How to handle the group stage of the Champions League with excel without any macro


After explaining how to handle a soccer Serie A league championship type (go to that article), here is another example on how to handle with excel a UEFA Champions League Group stage.
Again the complexity is always given by the tie breaker variable and unlike a normal Championship, with 8 Groups we have to build 8 different and separate standings.

Let's first share the file (without any macro) and refresh a couple of concepts.

Rules of the Tournament

As always, the starting point is to read the rules and understand the logic. At this link you can download the official UEFA regulations document valid for 2015-2018.
In case the link would not work anymore, here is the document again.

What is relevant for our exercise is Article 16 (Match system - group stage) and Article 17 (equality of points - group stage).

To replicate this in our excel, we have to build 8 different tables that can foresee 4 different cases:

  1. 1 standing for:
    • 4 teams with same points (almost impossible, but still possible if all matches end with a draw)
    • 3 teams with same points (I believe all with 12 points and the last with 0)
    • 1 couple with same points (this has higher probability)
  2. 2 standings if 2 couples have same points (say 2 teams with 9 and 2 teams with 3)

What to change to adapt it to your needs

I will not go through the structure of the file here and this logic applies really for the UEFA Champions League only.
Still, if you would like to use it for a "personal" Champions League tournament, here are the things you should be changing (rest is automated):

As always, file comes free of charge and without protections. I encourage you to use it, test it and I hope it will be useful to some of you. I don't care being mentioned or cited as the developer of this file should you like to share it elsewhere.