Football Statistics Dashboard

Excel Football Statistics Dashboard allows to visualize and interpret historical football (soccer) results from previous seasons. It is a great instrument to support your betting strategies and help you make better predictions. In this article we will walk through the dashboard, explain how to use it, and how to build each of the functions, tables, pivots, and charts, so that you can adapt it to your needs.

Dashboard

The Dashboard tab contains slicers to filter raw data stored in the Table worksheet by season, home team (Team A), and away team (Team B). In this example, that table contains scores for games over the last 20 years for the Spanish Primera Division league (La Liga). Excel spreadsheets with historical results for other leagues can be found here: Football Scores in Excel

The total number of seasons in the dataset (Table1) is taken from the formula:

=SUMPRODUCT(1/COUNTIF(Table1[Season],Table1[Season]))

Similarly, the total number of teams is taken from:

=SUMPRODUCT(1/COUNTIF(Table1[Team A],Table1[Team A]))

Next, the following basic Football stats are calculated using the Excel Subtotal function. That allows to update figures upon table filtering, in contrast to basic Sum and Count functions.

Games played (P) =SUBTOTAL(2,Table!H:H)

Win outcome (W) = SUBTOTAL(9,Table!L:L)

Draw outcome (D) = SUBTOTAL(9,Table!M:M)

Loss outcome (L) = SUBTOTAL(9,Table!N:N)

Note that additional columns were added to the table to capture win, draw and loss events.

The percentage for each outcome and the corresponding betting odd is calculated based on those figures as follows:.

%win = total games won / total games played (same for draw and loss)

Odd = 1 / %outcome

Let’s have a look at Barcelona’s basic stats playing home for the last 20 seasons.

Barcelona played 359 games at home and won 278. They have an average of 77.4% probability to win at home based on those figures.

The total and average number of goals scored (Home) and goals allowed (Away) is calculated with the Subtotal function as earlier explained for outcome. The goals distribution chart below shows the cumulative number of games for each goal outcome.

The following formula calculates the cumulative number of games (from column H in Table1) for the home team scoring 0 goals:

=SUMPRODUCT(--(Table!$H:$H=0),SUBTOTAL(2,OFFSET(Table!$H$1,ROW(Table!$H:$H)-ROW(Table!$H$1),0)))

Similarly, we get the cumulative number of games where the away team scored 1 goal:

=SUMPRODUCT(--(Table!$I:$I=1),SUBTOTAL(2,OFFSET(Table!$I$1,ROW(Table!$I:$I)-ROW(Table!$I$1),0)))

Here’s how it looks for Barcelona playing home over the last 20 years.

Totals

The Totals tab consists of 2 pivot tables, for Home and Away results respectively, with a common Slicer to filter by season. The pivots show basic stats for each team in the league, when playing home and away.

Average

Similarly to Totals, the Average tab consists of 2 pivot tables (for Home and Away results) with a common Slicer to filter by season. The pivots show calculated fields for average figures of basic stats for each team playing home and away. Average values can be used to make approximate predictions, see this other article: Football Predictions Overview

Goals

This tab consists of 4 pivot charts and 3 slicers. It shows the distribution of goals scored and allowed for the selected seasons and teams, either home or away. As explained earlier, the goals distribution chart displays the cumulative number of games for each goal outcome.

As we will see later in other article, that graphical representation for goals follows the statistical Poisson distribution, which can be used to calculate probabilities for most Football betting events (Football Predictions with Poisson Distribution).

Get access to Football (Soccer) historical scores in Excel for numerous leagues here: Football Scores in Excel