Building an Excel Football League Table — Traditional vs Dynamic Array Methods

Introduction

I am going to show you the different ways you can build a football league table in Excel. Some of the methods are old school, but others utilise Excel’s new capabilities.

In case you weren’t already aware, Excel has undergone a big change to its calculation engine fairly recently. The concept of dynamic arrays was first introduced back in September 2018, however, for many Microsoft 365 users the first batch of new functions took an awfully long time to appear. Unless you have been an Office Insider, you will not have been able to use them. Even though the update was rolled out to my copy towards the start of the year, there were still swathes of users who were kept waiting.

Since dynamic arrays were introduced in Excel, array formulas no long require you to press Ctrl + Shift + Return every time you edit a cell. This was an annoying practice that made many users, including myself, reluctant to use arrays. They just didn’t feel like a native and integrated part of Excel. Now you can use an array formula like any other — without that additional step.

If you want to find out more about the new functions, I recommend you visit Microsoft’s help page for each one: XLOOKUP, FILTER, UNIQUE, SORTBY, SORT, SEQUENCE, RANDARRAY.

Before We Start

Download the workbook: League-Table-Examples.xlsx.

Quick caveat: if you have an older version of Excel, you will find some of the examples do not work because of compatibility issues. This is unavoidable unless you purchase a Microsoft 365 subscription. Personally, I would recommend you do so.

Steps Taken

Firstly, a dataset is required containing a list of all the matches played and their respective results. I have used English Premier League data from the 2019/20 season for this example. To conserve space elsewhere, the matches are stored in a separate worksheet called Data — with the table itself named DataTable.

You’ll notice there’s a calculated column on the end called Result. This formula looks at the home_goal and away_goal fields for each match played and determines whether the outcome was a home win (H), draw (D) or away win (A).

There are three sections: Part A, Part B and Part C. Each contains multiple league tables that output identical values, but the method used differs.

Any kind of system that involves ranking data is typically going to require an unordered and ordered table. The former houses the mathematical calculations and determines the ranking of each row, whilst the latter references it to output the data in the correct order. Part A and Part B are based off this principle. Part C, however, contains two variants that are not dependent on an additional table.

The tables in the workbook use these headers:

• POS (position)
• TEAM (team name)
• P (matches played)
• W (matches won)
• D (matches drawn)
• L (matches lost)
• F (goals for)
• A (goals against)
• GD (goal difference)
• PTS (points)
• RANK* (table position when points are sorted in descending order)

*Table A2 only

Part A

The approaches here are all based on official Excel tables. The way to tell if what looks like a table is indeed a table— is to check if it has a small blue triangle in the bottom-right, or to click on it and the Table Design tab will appear in the ribbon.

We start off by creating Table A1, which is unordered and forms the base for Table A2, Table A3 and Table A4 to work off. The P, W, D, L columns use COUNTIFS formulas to count the number of matches a team has played, won, drawn and lost respectively. It’s important to note that a single COUNTIFS formula only allows for AND conditions. That means all criteria must be met for a successful count. As we have home and away matches to consider, we need to use two COUNTIFS statements in the same cell to add the counts together. The same concept applies to the SUMIFS function, which has been used for the columns that involve addition: F and A.

The goal difference (GD) column is as simple as F minus A. Custom formatting has also been applied so positive numbers are preceded by a ‘+’ symbol and negatives with a ‘-’.

The PTS column uses a rather convoluted formula to produce a very long number, but is necessary so the ordered table sorts correctly. In the case of tiebreakers, where two or more teams are level on points, we need to ensure a unique ranking for each team. The priority order that determines rank is as follows:

1. Points (PTS)

2. Goal difference (GD)

3. Goals scored (F)

4. First letter of team name (TEAM)

5. Position number (POS)

To prove the points column works, we set the values of the P/W/D/L/F/A/GD/PTS columns to pure zeros. With all things being level now, the numbers in RANK directly mirror the alphabetical order of the teams. If you look at Arsenal and Aston Villa, for example, both of them begin with the letter ‘A’, so the only thing separating their points are the position numbers.

Table A2 uses VLOOKUP to extract the relevant data from Table A1 and display it in the correctly sorted order. VLOOKUP is a popular function, but is less flexible than INDEX/MATCH and the new XLOOKUP. For this reason, many people will stop using it once they’ve discovered one of the latter two.

One of the biggest downsides of VLOOKUP is having to reorder your data so that the lookup range is the furthest-left column. In this case, the RANK column has had to be moved in Table A1 so it comes before all the columns that we want to return data from.

This is not the case with INDEX/MATCH and XLOOKUP. It doesn’t matter about the position of the RANK column. With both Table A3 and Table A4, we have simply referenced only the ranges we need from Table A1.

Part B

Unlike Part A, all three of the ‘tables’ you see are not actually tables—in Excel terms anyway—although we’ll still use that term. Table B1 uses only 11 formulas—one for the headings and the other 10 for the calculations. To surprise you even more: Table B2 and Table B3 use just three. This is what dynamic arrays are all about. They grow and shrink depending on the output of the formula. This concept goes completely against traditional Excel, where one cell has one formula. The big advantage of this is you are less likely to make errors in your worksheet and will reduce inconsistencies.

For the column headings of each table, we have used an array constant to hold the column names. These are fixed values and do not change. Unfortunately, only numbers and text strings can be used in constants — not formulas.

={"POS","TEAM","P","W","D","L","F","A","GD","PTS"}

You will notice in the formulas that a cell reference followed by a hashtag is a common occurrence. This is a spilled range, meaning it is dynamic, so its size will adapt according to the data.

We have again used COUNTIFS and SUMIFS for the P/W/D/L/F/A columns in the same manner as we did in Table A1, except spilled ranges have replaced regular ones.

Some of the new Excel functions have been utilised and allow us to do a lot from a single cell.

For the POS column, the SEQUENCE function returns a set of numbers. By wrapping it around the COUNTA function, it will always return an incremental number in accordance to how many teams are displayed adjacent to it.

POS: =SEQUENCE(COUNTA(D14#))

The individual team names have been extracted from the team_home column in DataTable using UNIQUE, and then sorted in ascending order using SORT.

TEAM: =SORT(UNIQUE(DataTable[team_home]))

Notice how the PTS column is full of integers and there is no RANK column. This is because we no longer need that awfully complex calculation you saw in Table A1. What replaces it then? The trusty SORT function again.

You’ll see in Table B2 and Table B3 that the formula for the table calculations starts at the very top of TEAM. That’s one formula controlling 180 values!

CHOOSE and SWITCH are great for this purpose. Multiple column values can be stored in one formula and spill vertically and horizontally.

Let’s look at the main formula in Table B2:

=SORT(CHOOSE({1,2,3,4,5,6,7,8,9},
D14#,
E14#,
F14#,
G14#,
H14#,
I14#,
J14#,
K14#,
L14#
),{9,8,6,1},-1,-1,-1,1)

The numbers at the top between the parentheses are placed in the index_num argument. They are the index numbers that refer to each spilled range below it.

The {9,8,6,1} in the sort_index argument refers to the sorting order and the {-1,-1,-1,1} in sort_order determines whether each sort_index number should be sorted ascending (1) or descending (-1).

Just like in Table A1, we have a priority list: PTS, GD, F and TEAM. But it’s so much easier to implement it this new way though.

The SWITCH method shown in Table C3 is very similar to the CHOOSE one, but the advantage is you are not limited to index numbers, so you can name values what you wish. This is ideal if you have a long formula and you want to optimise readability.

=SORT(SWITCH({"Team","P","W","D","L","F","A","GD","PTS"},
"Team",
D14#,
"P",
E14#,
"W",
F14#,
"D",
G14#,
"L",
H14#,
"F",
I14#,
"A",
J14#,
"GD",
K14#,
"PTS",
L14#
),{9,8,6,1},{-1,-1,-1,1})

Part C

The first thing you’ll probably notice in this section is that there is no unordered table. The two table variants you see are both self-contained, and only depend on the dataset in the Data worksheet. I have simply squeezed in all the formulas you saw in Table B1 and placed them in a CHOOSE (Table C1) and SWITCH (Table C2) statement.

These really are the true definition of a ‘mega formula’! They are rather overwhelming to look at, but using line breaks appropriately (Alt + Return) to spread out the formula can solve most of the readability issues. I also recommend using the SWITCH option as named values can allow you to immediately see where each section starts and ends.

One of the limitations of Excel formulas is the inability to reuse parts of a formula. For example, with the goal difference column, rather than having to repeat the formulas for columns F and A, it’d be nice if it was possible to access those values and reuse them for a different part of the formula. Given the LET function is closer upon us, this may well solve that problem for the most part, as we’ll be able to declare named values and use them multiple times within the same formula.

Final Words

Hopefully the workbook examples have given you food for thought with how you can go about creating formulas that make use of Excel’s dynamic array powers. The centralised, one formula = many cells approach will gradually become the de facto standard, so get ahead of the curve whilst you can.

Functions to Learn

There are many resources on the web that enable you to learn in-depth about the functions used in the workbook. I’ve found Exceljet to be a particularly helpful source.

RANK.EQ
COUNTIFS
SUMIFS
VLOOKUP
INDEX
MATCH
COUNTA
CHOOSE
SWITCH
COLUMN
CODE
UNIQUE
SORT
XLOOKUP
SEQUENCE