Building a Premier League Results Matrix in Excel

Maybe you’re a football fan — maybe you’re not. Besides, the new Premier League season started last weekend. Amidst the empty stadiums and general doom and gloom, you’d be forgiven for not feeling as enthusiastic about it compared to previous seasons. However, I’ve got something for you that will whet your appetite.

I thought I’d show you a really cool results matrix I built in Excel, which allows you to view all match results for a given season. In addition, you can calculate totals and highlight matches based on a number of different criteria.

Prerequisites

⚠️ Microsoft 365 Required

An Excel desktop copy that’s part of a Microsoft 365 (formerly Office 365) subscription is required. Excel 2019 or anything older will not work unfortunately. New functions and dynamic arrays have been utilised and these are only available in Microsoft 365.

This article does assume you have a good foundation of Excel knowledge, as some of the techniques used in the workbook are fairly advanced. At the end, I’ve included a list of all the functions and VBA used, so if there are any you’re unfamiliar with, it’s worth learning them.

Download the workbook: Results-Matrix.xlsb.

Results Matrix Workbook

There are three worksheets in the workbook: Data, Settings and Results Matrix.

Data contains the tabular dataset the results matrix references. It consists of Premier League match data ranging from the 1995/96 to 2019/20 season.

A calculated field has been added to the original data called match_ref. This is a concatenated string that amalgamates the team_home, team_away and season fields — used as a reference for finding match records.

Settings houses season and club data that are used by the season dropdown and the results matrix itself.

As the user can select from multiple seasons, the clubs that played in each season need to be defined, so the correct sides and results are outputted. In the Settings_Clubs table, if a team has played in a specific season, it is denoted by a 1, and a 0 if they haven’t.

The club emblems are also stored here, alongside a unique reference in the Crest Name column that ensures the correct badges show up in Results Matrix.

Results Matrix features the main grid, along with peripheral formulas that assist with the calculations and dynamic highlighting feature.

Home teams are displayed vertically and away sides horizontally. Simply hover over the intersection and you will see the chosen fixture at the top-left outside the matrix. The dynamic hovering functionality has been achieved using VBA.

By clicking on Highlight Choices, you can also highlight matches based on different criteria and see a numerical summary.

Results Matrix Worksheet

There are several aspects to the results matrix:

Club Names

For the home club names, these are being referenced from the Clubs table that starts in cell AB15. The teams featured in this table change based on the season selected in the dropdown. The formula =IFERROR(AC41#,"") contains a spilled range that will adapt to the number of teams present. If you select the blank option in the Season dropdown and look at the formula in B11, you’ll see that only one cell is being referenced. This is one of the clever things about Excel’s dynamic array capabilities.

You’ll notice for the away club names, the use of the TRANSPOSE function was necessary because the range being referenced is vertical, but in the matrix it needs to be outputted horizontally.

Main Grid

Let’s now break down cell D11, which is one of 400 formulas in the main part of the grid that is responsible for outputting a result, fixture date (if match is unplayed) or blank value (for invalid fixture).

=IFERROR(
IFERROR(HYPERLINK(@Highlighter(ROW(),COLUMN()),""),

IF(INDEX(DataTable[[played]:[played]],MATCH($B11&D$10&$AC$35,DataTable[[match_ref]:[match_ref]],0))=0,
TEXT(INDEX(DataTable[[date_time]:[date_time]],MATCH($B11&D$10&$AC$35,DataTable[[match_ref]:[match_ref]],0)),"dd/mm/yy"),

TEXTJOIN("–",1,XLOOKUP($B11&D$10&$AC$35,DataTable[[match_ref]:[match_ref]],DataTable[[home_goal]:[home_goal]]),
XLOOKUP($B11&D$10&$AC$35,DataTable[[match_ref]:[match_ref]],DataTable[[away_goal]:[away_goal]])))),
"")

To enable the dynamic hover effect, the opening part of the formula is a requirement: =IFERROR(HYPERLINK(@Highlighter(ROW(),COLUMN())). For the link_location argument of the HYPERLINK statement, the VBA function Highlighter (see VBA section) is placed here. By wrapping ROW() and COLUMN() inside it, this returns the row and column number based on where the mouse cursor is.

What’s confusing about this is if you click on the link_location argument and press F9, you’ll see that the result of this part of the formula is a #VALUE error. However, when you hover over the cell, the hand cursor indicates it is still seen as a hyperlink. A pop-up window appears when you click on it warning you it’s an invalid link, but it’s only needed for the hover effect.

As this part is nested in the IFERROR function, the error in the value argument means it will jump to value_if_error. Contained here is an IF statement responsible for displaying the result, or fixture date if the match hasn’t been played.

The first part of the IF statement uses an INDEX and MATCH formula, as when I tried XLOOKUP, the hover effect stopped working. This appears to be an unresolved glitch in Excel.

IF(INDEX(DataTable[[played]:[played]],MATCH($B11&C$10&$AC$33,DataTable[[match_ref]:[match_ref]],0))=0,

The INDEX array argument references the Played column in DataTable, as this is the value that is being logically tested for 0 (unplayed matches). In the subsequent argument row_num, this contains a MATCH formula nested within it. It comprises a concatenated cell reference that joins the values of the home team, away team and season, and then this string (‘ArsenalAstonVilla2019/2020’) is searched for in the match_ref column of DataTable, as part of the lookup_array argument. The false value 0 is given as the match_type, because an exact match is being looked for.

Now the value to appear if the above condition is true must specified (value_if_true).

TEXT(INDEX(DataTable[[date_time]:[date_time]],MATCH($B29&D$10&$AC$33,DataTable[[match_ref]:[match_ref]],0)),"dd/mm/yy"),

The fixture date needs to appear, but it must be outputted in a certain format. The use of the TEXT function allows it to be converted into a text string and explicitly chosen how the date should be displayed. If the unformatted date was returned, it would just appear as one long number.

TEXT takes on two arguments: value and format_text. The former houses another INDEX/MATCH statement that returns the correct date/time from the date_time column in DataTable. The latter states the format of the returned date: "dd/mm/yy".

Finally, the value_if_false argument is stated. This part of the formula will be executed when a match has been played.

TEXTJOIN("–",1,XLOOKUP($B11&C$10&$AC$33,DataTable[[match_ref]:[match_ref]],DataTable[[home_goal]:[home_goal]]),
XLOOKUP($B11&C$10&$AC$33,DataTable[[match_ref]:[match_ref]],DataTable[[away_goal]:[away_goal]])))),
"")

The TEXTJOIN function allows multiples values to be strung together whilst separated by a delimiter. An en-dash (–) has been chosen, providing separation between the home and away goals. Empty values need to be ignored, so a true value of 1 is placed in ignore_empty.

In text1, an XLOOKUP formula is used and it works very similarly to the INDEX/MATCH formula I showed you. The lookup_value is the concatenated string containing the home team, away team and season. This value is then searched for in the match_ref column within DataTable, which is defined inlookup_array. The return_array is the number of home goals to be returned, so home_goal is stated.

This process is then repeated for text2, except away goals need to be returned, so away_goal becomes the return_array.

Total/Played/%

When you choose a highlight category, Total (cells highlighted), Played (total matches played) and % (Total ÷ Played) appear after the club names to provide you with a useful summary of each row and column. All three of these reference the values displayed in the Highlight Choices table, which I will go through shortly.

Result/Fixture Bar

When you hover over the matrix, the active result or fixture will appear above.

This is concatenating values together from other cells to display in a single cell. An OR condition has been included to prevent anything from being shown when the drop down is on blank. Or, when you are hovering over an intersection where the home club mirrors the away.

=IF(OR(AC12=1,AF14=AF15),"",
AF14&" "&IF(AF18=1,
AF16,"v")&" "&AF15&" ("&AF17&")")

Highlight Selection Bar

When you select an option in Highlight Choices, the bar on the right above the matrix shows you what has been selected, as well as the total, matches played and percentage of highlights.

=AL12&IF(ResultsMatrix_Highlight=0,"",
" — "&BJ31&" out of "&BK32&" matches played"&
" ("&BL33&")")

Complementary Tables

In the middle section, there is a group of tables that assist the results matrix.

Season: linked to the dropdown and contains the reference number and season selected.

Clubs: the teams displayed are dependent on the season selected. As previously mentioned, the clubs and their details are all stored in Settings.

Let’s delve inside of cell AC17 to look at the formula.

=IFERROR(SORT(FILTER(Settings_Clubs[Team],(Settings_Clubs[Team]<>"")*XLOOKUP(Settings_ResultsMatrixSeason,Settings_Clubs[[#Headers],[2019/20]:[1992/93]],Settings_Clubs[[2019/20]:[1992/93]]),"")),"")

I have used just the array argument for the SORT function, with the knowledge that whatever is wrapped inside of it will get sorted in ascending order by default.

The FILTER function also has an array argument, and the Team column in Settings_Clubs has been specified here, as this is the range needed to be filtered.

The include argument contains formulaic instructions to truncate the Team range so only the clubs that participated in the season selected. The first condition, (Settings_Clubs[Team]<>""), is there to ignore the sole blank value that exists in the Team column. An asterisk (*)—which represents AND logic—is placed after, allowing another condition to be made.

The second condition is an XLOOKUP formula that finds the selected season (lookup_value) in the headings of the Settings_Clubs table (lookup_array), and then returns the clubs that are marked 1 (return_array).

As the first condition is being multiplied by the second, both need to result in a TRUE value (1) to be counted successfully. If either equates to FALSE (0), FALSE (0) is the outcome.

Crests: contains named references that correspond to the badges that dynamically display above the results matrix. Each of these is listed in Name Manager and point to the appropriate cell in the Settings worksheet.

Dynamic Hover Statistics: provides statistics about the hover selection. The Row and Column numbers are generated via VBA, whilst the other rows use XLOOKUP formulas to turn these figures into useful data that is used to inform you about your selection.

Scorelines and Result Margin: both display the number of goals selected in these tabs within the Highlight Choices window.

Highlight Selection: contains the reference number and the name of the highlight category you have chosen in Highlight Choices.

Highlight Category: The 22 different options in Highlight Choices are shown below. Each one has been given a reference number to distinguish between them. The first row has been assigned 0, as this represents no selection.

Match Result Outcomes

The four basic outcome categories a match falls into.

Under/Over

The under/over goals is a common betting market and broadly categorises matches based on total goals. I’ve included five of each.

Scorelines

Specific scorelines can be highlighted by using the spinners to set the home and away goals. The Activate button must be pressed after choosing the scoreline for the matrix to update.

Result Margins

In a similar manner, you can highlight results based on their margin. For example, if you were to look for overall or home games with a one-goal margin, a 1–0 win will be counted the same as a 4–3 victory.

Other

Clean sheets are matches where one or both teams did not concede. You can opt to highlight clean sheets in all matches, or just home or away ones. There’s also an option for no clean sheets.

Highlight Selection Table

The Highlight Selection table contains a grid of pretty intimidating formulas, but don’t worry, they are not actually that hard to understand. As there are 22 different metrics to choose from in the Highlight Choices menu, it is necessary to include conditional statements for each of them. If a game matches the criteria of the chosen condition, the cell will display 1 — and 0 if not.

Below, I have chosen to view the results of the 2019/20 season and selected Home Wins in the Highlight Choices window. The Highlight Selection grid has updated to include a 1 in each cell where a home win occurred.

In simple terms, a match is classified as a home win if the home team scored more goals than the away side. Using cell AQ11 as an example, you can see in this particular case it is, because Arsenal beat Aston Villa 3–2 at home.

Finding each category is easy if you click just inside the CHOOSE statement. You will be presented with a long wide function argument box, which breaks down each section of the statement.

CHOOSE(index_num, value1, [value2], [value3]...[value22]

The index_num argument refers to a named range called ResultsMatrix_Highlight in AK12. This is the reference number of the option you chose in the Highlight Choices window. Conveniently, each value argument matches the figure shown in the Highlight Category table, so you only have to click on the one you want to go to for Excel to automatically take you to it.

Let’s dissect value1:

IF(XLOOKUP($AO11&AQ$10&$AC$13,DataTable[[team_home]:[team_home]]&DataTable[[team_away]:[team_away]]&DataTable[[season]:[season]],DataTable[[home_goal]:[home_goal]])
>
XLOOKUP($AO11&AQ$10&$AC$13,DataTable[[team_home]:[team_home]]&DataTable[[team_away]:[team_away]]&DataTable[[season]:[season]],DataTable[[away_goal]:[away_goal]])=TRUE,1,0)

I’ve opened the statement with the IF function and then used XLOOKUP to search for a concatenated string comprising three cell references that point to the home team, away team and season. This means that $AO11&AQ$10&$AC$13 in the lookup_value argument equates to ‘ArsenalAston Villa2019/20’.

Next, lookup_array represents the concatenated columns in the Data worksheet that need to be searched in for the aforementioned lookup_value. These are the team_home, team_away and season fields. There’s a good reason why I’ve made the table column references absolute: so those exact columns are maintained when dragging the fill handle horizontally to populate the rest of the table. Unfortunately, it isn’t a simple case of pressing F4 like with cell references—you have to manually set them.

The return_array contains the column that the value is to be returned from. Seeing as the number of home goals is required, the home_goal field is specified to conclude the first XLOOKUP statement.

To find out if the number of home goals is more than the away goals, the greater than conditional operator (>) is used to check if this is the case. The XLOOKUP formula just written only has to be copied, planted after the > sign andreturn_array value changed to away_goal.

The calculation of this formula converts to 3 > 2. Is 3 greater than 2? Of course it is, so it’s equal to TRUE.

With the logical_test complete, the value_if_true and value_if_falsefigures needing to be returned are 1 and 0 respectively.

The formula I just showed you is the template for most of the other CHOOSE value arguments, which include match outcomes and goals. These are variants based around an IF formula with two comparative XLOOKUP parts: a home and away statement — with a conditional operator sandwiched in between.

That isn’t the case for Scorelines in value15 though, which uses a nested AND formula wrapped inside an IF to determine if the number of home and away goals are equal to the values picked in the Highlight Choices window.

Elsewhere, the ABS function — which returns the absolute value of a number by omitting its sign — has been used for value16, the overall goal margin category. Because home and away margins are included for this, the calculation needs to consider that the user can only choose 0 and positive numbers in the Result Margin tab.

In the case of Arsenal 3–2 Aston Villa, 3 minus 2 is 1, so this is all right. But what about Arsenal 1–2 Chelsea, for instance. 1 minus 2 is –1, but this would result in a discrepancy between the figure chosen by the user and the result of this calculation. Ultimately, this would mean away wins going undetected. However, by wrapping the value inside the ABS function it becomes 1 — therefore being read correctly.

An alternative way is to exclude ABS and use two OR statements that consider zero and positive numbers for home matches, and negative figures for away games. However, this results in an unnecessarily verbose formula.

Conditional Formatting

Conditional formatting has been set up in Results Matrix, and you can see the different rules by going into Conditional Formatting on the Home tab. Where a match meets the criteria chosen in the Highlight Choices menu, the cells will be highlighted in yellow. The result in the cell that’s being hovered over will turn bold to serve as a visual aid for the user. And where an impossible fixture exists (home team is identical to away), these cells will turn grey.

Using the ampersand symbol (&) for a shorthand AND formula (=ROW()&COLUMN()=$AF$12&$AF$13) means that the ROW and COLUMN numbers must match the figures in cells AF12 and AF13 for the hovered cell to turn bold.

For the home and away club names, I’ve used =OR(ROW()=$AC$24,COLUMN()=$AC$25) to highlight the appropriate row and column based on where the cursor is. The ROW() function outputs the row number and must match ResultsMatrix_Row for this condition to be true. Likewise, COLUMN() requires a matching column number in ResultsMatrix_Column. As this is an OR condition, only one of the conditions needs to be true.

To make it easy to see the maximum and minimum figures for the Total, Played and %, these are highlighted in green and red respectively.

Additional rules to make these add-on rows and columns appear with borders—as well as selectively bold—have also been applied.

Dynamic Crests

When you hover over the results matrix, the club crests change depending on the fixture. Let’s have a look at how they’ve been achieved.

The home crest is called ResultsMatrix_CrestA and the away one is named ResultsMatrix_CrestB. The club references appear in the Crests table.

Using the home crest reference as an example, the XLOOKUP formula searches for the stated home team in Dynamic Hover Statistics (lookup_value), finds it from the Team column in the Settings_Clubs table (lookup_array), and then returns the corresponding crest reference from the Crest Name column (return_array).

=XLOOKUP(AF14,Settings_Clubs[Team],Settings_Clubs[Crest Name])

If you go to Formulas >>> Name Manager, you can see a list of defined names that correspond to different cells. Every club featured has a crest reference stored in here, and they each point to their cell containing the SVG vector in the Settings worksheet.

The INDIRECT function returns the value of a cell reference or named range that’s enclosed within a text string. This method is used when you have a value that varies. In this case, the club crests change, so their named ranges also need to.

The formula shows that ResultsMatrix_CrestA refers to the home crest reference in the Crests table, via the INDIRECT function. This means that, indirectly, the cell being referred to is Crest_Arsenal in Settings.

VBA (Visual Basic for Applications)

VBA played a prominent role in the results matrix hover feature and the club crests, both of which dynamically update. I prefer to avoid VBA if possible, given its archaic nature and how it is slowly becoming obsolete. However, I couldn’t build the dynamic functionality without it.

Press Alt + F11 to open the VBA window.

There are two VBA modules: DynamicHover and OpenWindows.

The code in OpenWindows is used by the Highlight Choices button simply to open its window.

The procedure written in DynamicHover is a function that outputs the row and column number in cells AF12 (ResultsMatrix_Row) and AF13 (ResultsMatrix_Column) respectively.

' Results Matrix Highlighting

Public Function Highlighter(iRow As Integer, iColumn As Integer)
Application.ScreenUpdating = False

If ActiveSheet.Name = "Results Matrix" Then

Sheet3.Range("ResultsMatrix_Row") = iRow
Sheet3.Range("ResultsMatrix_Column") = iColumn

Application.ScreenUpdating = True

End If
End Function

Double-clicking on the HighlightChoices form presents you with the design view of the same form you’ve already seen. If you right-click on the form and go to View Code, you will see all the inner workings.

The code has been structured and commented in a way that makes it easy to follow. Many of the procedures are repetitive, but they all have a role to play.

The variables have been declared at the top, followed by an initialisation procedure. Before the UserForm has finished loading, the ListBoxes in Scorelines and Result Margin default to the named ranges shown in the worksheet. These are the values that the user last set — they will not reset to 0 if you close and reopen the window. The UserForm_initialize() event is used to do this.

' Display the same values in ListBoxes as are shown in worksheet

Private Sub UserForm_initialize()

Scorelines_HomeGoals.AddItem Sheet3.Range("ResultsMatrix_HomeGoals")
Scorelines_AwayGoals.AddItem Sheet3.Range("ResultsMatrix_AwayGoals")
ResultMargin_Overall.AddItem Sheet3.Range("ResultsMatrix_OverallResultMargin")
ResultMargin_Home.AddItem Sheet3.Range("ResultsMatrix_HomeResultMargin")
ResultMargin_Away.AddItem Sheet3.Range("ResultsMatrix_AwayResultMargin")

End Sub

All 22 buttons that represent each category have had procedures written for them. When the button is clicked on, the reference number in cell AK12 (ResultsMatrix_Highlight) changes to the number specified in the code.

' Match Result Outcomes

Private Sub HomeWinsButton_Click()
Sheet3.Range("ResultsMatrix_Highlight") = 1
End Sub

When you want to reset the result matrix and strip it of any highlighting, you have to press the Clear button. Behind this action, the code is instructing the ResultsMatrix_Highlight to display 0. There are five of these Clear buttons — one for each tab.

' Clear buttons

Private Sub ClearButton1_Click()
Sheet3.Range("ResultsMatrix_Highlight") = 0
End Sub

Private Sub ClearButton2_Click()
Sheet3.Range("ResultsMatrix_Highlight") = 0
End Sub

Private Sub ClearButton3_Click()
Sheet3.Range("ResultsMatrix_Highlight") = 0
End Sub

Private Sub ClearButton4_Click()
Sheet3.Range("ResultsMatrix_Highlight") = 0
End Sub

Private Sub ClearButton5_Click()
Sheet3.Range("ResultsMatrix_Highlight") = 0
End Sub

The rest of the procedures all pertain to the SpinButtons that are present in Scorelines and Result Margin. Although a spinner is a single item, it consists of an up and a down button, so there needs to be code for each action.

Let’s look at the home spinner in Scorelines as an example.

' Scorelines
' Home spin up

Private Sub Scorelines_HomeSpinButton_SpinUp()

Application.ScreenUpdating = False

ResultsMatrix_HomeGoals = Sheet3.Range("ResultsMatrix_HomeGoals").Value
    ResultsMatrix_HomeGoals = ResultsMatrix_HomeGoals + 1
    Scorelines_HomeGoals.Clear
    If ResultsMatrix_HomeGoals > 10 Then ResultsMatrix_HomeGoals = 10
    Sheet3.Range("ResultsMatrix_HomeGoals").Value = ResultsMatrix_HomeGoals
    Scorelines_HomeGoals.AddItem ResultsMatrix_HomeGoals

    Application.ScreenUpdating = True

End Sub

When the up spinner is clicked, the value of ResultsMatrix_HomeGoals is fed into the variable ResultsMatrix_HomeGoals and the +1 ensures the value will increase in increments of one.

The purpose of Scorelines_HomeGoals.Clear is to prevent a build-up of numbers in the list with each increment. If this line is excluded, the ListBox ends up having a vertical scroll bar that contains multiple numbers.

By including If ResultsMatrix_HomeGoals > 10 Then ResultsMatrix_HomeGoals = 10, it sets a maximum limit of 10 goals.

The ResultsMatrix_HomeGoals variable value is then copied over to ResultsMatrix_HomeGoals (cell AC15) by using Sheet3.Range(“ResultsMatrix_HomeGoals”).Value = ResultsMatrix_HomeGoals.

Finally, using the AddItem method, the ListBox takes the value of the ResultsMatrix_HomeGoals variable and displays it. It should be noted that the ListBox is separate to the variable value displayed in it. That’s why it’s vital to include the Scorelines_HomeGoals.AddItem ResultsMatrix_HomeGoals line of code, otherwise the goal number will not be visible, even though the spinner is functionally working.

The down spinner is almost identical to the up one, except instead of increasing, it decreases by — 1.

The other SpinButtons also use the same code template, and only differ by the variables and named ranges specified.

Sheet3 (ResultsMatrix)

There is an annoying bug in Excel, whereby if you fiddle about with the dataset in the Data worksheet — and then go back into Results Matrix — the grid no longer shows the results. You then have to select another season for the grid to show its values again. It’s not clear what causes this, but thankfully there is a workaround.

A procedure fronted by the Worksheet_Activate event ensures the Season dropdown resets each time you go to the Result Matrix worksheet.

The way it works is: the value of the dropdown is captured by the SeasonSelected variable, and then the dropdown is given a value of 0 to nullify it. Finally, the value of SeasonSelected is fed back into the dropdown object and this finalises the reboot process.

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False

'Season dropdown

SeasonSelected = Sheet3.Shapes("ResultsMatrix_SeasonDropdown").ControlFormat.Value

Sheet3.Shapes("ResultsMatrix_SeasonDropdown").ControlFormat.Value = 0

Sheet3.Shapes("ResultsMatrix_SeasonDropdown").ControlFormat.Value = SeasonSelected

Application.ScreenUpdating = True

End Sub

Final Words

Whether you’re not much more than a novice or an experienced pro, hopefully you will have gained something from this article and workbook. The best way to understand it is to explore and learn how to use the functions individually first. Then you’ll gain a better understanding of how you can nest these functions together to do amazing things. Feel free to adapt it in any way you wish; I’m sure it has uses beyond football.

---

Functions Used

This is a list of every single function used in the workbook. Each link will take you to the ExcelJet webpage for that specific function. I’ve found this to be the best place out there for learning.

ABS
AND
CHOOSE
COLUMN
CONCAT
COUNTA
COUNTIFS
FILTER
IFERROR
HYPERLINK
IF
INDEX
INDIRECT
LEN
MATCH
ROW
SEQUENCE
SORT
SUM
TEXT
TEXTJOIN
TRANSPOSE
XLOOKUP

VBA Used

Here is a list of sources taken from Microsoft’s website relating to various aspects of VBA used in the workbook.

Objects

Application
ControlFormat
Name
Range
UserForm

Methods

AddItem
Clear
Show

Properties

ActiveSheet
ScreenUpdating
Shapes
Value

Events

Click
Initialize
SpinDown/SpinUp

Statements

Dim
End
Function
If…Then
Option Explicit
Sub

Controls

ListBox

Reference

Comparison operators
Data Types