Build a Dynamic FIFA World Cup 2026 Dashboard in Excel
- MirVel
- 12 minutes ago
- 10 min read
Spain beat Argentina 1-0 after extra time on 19 July 2026 to win a World Cup that broke almost every record it could: 48 teams, 104 matches, 308 goals, and more than 6.8 million people through the turnstiles at 99.7% stadium occupancy.
That is a lovely dataset. Big enough to be interesting, small enough to hold in your head. In this walkthrough I turn it into a dark, pitch-green dashboard that reacts to two dropdowns, and I build it with the modern Excel function set rather than the 2003 toolkit.
The finished workbook is attached at the bottom. Follow along and build your own, or open mine and reverse-engineer it.
What you'll build
A single Dashboard sheet with:
five KPI cards across the top
four charts that restyle themselves against a dark background
a selection roll-up that recalculates from two dropdowns
a data-completeness indicator, so the dashboard is honest about what it doesn't know
Everything sits on three data sheets and one calculation sheet. No macros. No Power Query. Just formulas.
Before you start
You need Microsoft 365 or Excel 2021 for the dynamic-array functions. GROUPBY and PIVOTBY need Microsoft 365 specifically. Where they come up, I've noted the Excel 2021 alternative.
Check quickly: type =SEQUENCE(3) in a blank cell. If 1, 2, 3 spill down three cells, you're good. If you get #NAME?, you're on an older build.
Step 1 - Get the data into a proper Table
Create a sheet called Teams and enter the 48 qualified nations with these columns: Team, Confederation, FIFA Rank, Host, Debut, Stage Reached.
Select any cell in the range and press Ctrl + T. Tick "My table has headers". Then on the Table Design ribbon, rename it to Teams.
This is the step people skip, and it's the one that makes everything afterwards work. A Table gives you:
Structured references. Teams[Confederation] instead of $B$2:$B$49.
Automatic growth. Add a 49th row and every formula, chart and PivotTable downstream picks it up. No range editing, ever.
Readable formulas. You can tell what SUM(Teams[Prize $m]) does at a glance. You cannot tell what SUM(G2:G49) does.
Do the same for two more small tables on a Tournament sheet: Bands (the eight finishing bands with team counts and prize money) and Editions (goals and matches for each World Cup from 1998 to 2026).
A note on honesty: I could only confirm the exit round for nine of the 48 teams from cited sources. Rather than invent the other 39, I left those cells blank and built a completeness indicator into the dashboard. A dashboard that admits a gap is worth more than one that quietly fills it with guesses - and this becomes a real feature in Step 9.
Step 2 - Modern lookups with XLOOKUP
Add a Prize $m column to the Teams table. We want each team's prize money, pulled from the finishing band it reached.
The old way meant VLOOKUP with a hardcoded column number, or INDEX/MATCH with two ranges to keep in sync. Neither survives someone inserting a column.
=XLOOKUP([@[Stage Reached]], Bands[Band], Bands[Prize $m], "")Three things to notice:
[@[Stage Reached]] is the current row's value. Excel writes this for you when you click the cell.
No column index. You point at the lookup column and the return column directly, so inserting a column between them changes nothing.
The fourth argument is the not-found value. No more wrapping the whole thing in IFERROR, which used to swallow genuine errors along with the expected ones.
Because the Table auto-fills, one entry populates all 48 rows.
Step 3 - Build the filter layer
Go to a new Dashboard sheet. Pick two cells for your controls - I used D8 and M8.
Select D8, then Data > Data Validation > List, and in Source type:
All,AFC,CAF,CONCACAF,CONMEBOL,OFC,UEFADo the same on M8 with the eight finishing bands plus All.
Now name them, because Conf reads better than Dashboard!$D$8 in every formula that follows. Select D8, click the Name Box to the left of the formula bar, type Conf, press Enter. Repeat for M8 as Stage.
The "All" pattern
This is the trick that makes the whole dashboard work, and it's worth understanding rather than copying.
FILTER takes an array of TRUE/FALSE values. We want "match this confederation, OR show everything if the user picked All". In array logic, addition is OR:
=FILTER(Teams, (Teams[Confederation]=Conf) + (Conf="All"), "No teams match")When Conf is "UEFA", the second term is FALSE (= 0) on every row, so the filter reduces to the confederation test. When Conf is "All", the second term is TRUE (= 1) on every row, so every row passes regardless.
Multiplication is AND. So both dropdowns together:
=FILTER(Teams, ((Teams[Confederation]=Conf) + (Conf="All")) * ((Teams[Stage Reached]=Stage) + (Stage="All")), "No teams match")Read it out loud: (confederation matches OR All) AND (stage matches OR All). That's the entire filter engine, in one formula.
Step 4 - KPI cards without eight helper cells
Each card needs a number. The naive approach writes FILTER again in every card, which means Excel evaluates the same filter five times.
LET fixes that. It names an intermediate result once and reuses it:
=LET( keep, ((Teams[Confederation]=Conf) + (Conf="All")) * ((Teams[Stage Reached]=Stage) + (Stage="All")), sel, FILTER(Teams[Team], keep, ""), n, ROWS(sel), n)LET takes name/value pairs and a final expression. Everything before the last argument is a definition; the last argument is what the cell returns. Excel computes each name once, so this is genuinely faster, not just tidier.
For the prize-money card, swap the last two lines:
=LET( keep, ((Teams[Confederation]=Conf) + (Conf="All")) * ((Teams[Stage Reached]=Stage) + (Stage="All")), SUM(FILTER(Teams[Prize $m], keep, 0)))Format that cell as $#,##0 and give it a football-gold font. Card done.
The static cards
Matches, goals and attendance don't move with the filter - they're tournament facts. Point them at the Tournament sheet and format them: 104 matches, 308 goals, =Goals/Matches for 2.96, and attendance as =6810960/1000000 with the custom format 0.00"m" so it renders as 6.81m.
That number format is worth stealing. The "m" in quotes is literal text inside a number format, so the cell still holds a real number you can chart, but displays as 6.81m.
Step 5 - GROUPBY: a PivotTable in one cell
Here's where modern Excel genuinely changes how you work. Prize money by confederation, sorted, with a total row:
=GROUPBY(Teams[Confederation], Teams[Prize $m], SUM, 3, 1)The arguments are: rows, values, function, header behaviour, total behaviour. That's it. It spills a formatted, sorted, totalled summary - and unlike a PivotTable it refreshes the instant the source changes. No Refresh All, no cache, no "PivotTable report is invalid".
Want a cross-tab? PIVOTBY adds a column dimension:
=PIVOTBY(Teams[Confederation], Teams[Stage Reached], Teams[Team], COUNTA, 3, 1)Confederations down the side, finishing rounds across the top, team counts in the middle.
On Excel 2021? Use SUMIF against a UNIQUE list instead:
=UNIQUE(Teams[Confederation])=SUMIF(Teams[Confederation], H2#, Teams[Prize $m])Note the # on H2# - that's the spill operator, and it's the subject of the next step.
Step 6 - Charts that resize themselves
This is the single most useful trick in the whole build.
When a formula spills, the top-left cell carries a spill reference: H2# means "the whole spilled range starting at H2", however big it currently is. Point a chart at that, and the chart grows and shrinks with the data.
Excel won't let you type H2# directly into a chart's data range, so you go through a name:
Formulas > Name Manager > New
Name: ChartConfeds
Refers to: =Engine!$H$2#
Repeat for the values: ChartPrize refers to =Engine!$I$2#
Then insert a bar chart, right-click > Select Data, and enter the names with the sheet name prefix - this catches everyone the first time:
Series values: ='My Workbook.xlsx'!ChartPrizeCategory labels: ='My Workbook.xlsx'!ChartConfedsNow filter to UEFA and the chart redraws with the right categories. Add a 49th team and it appears. You never touch the chart again.
Step 7 - Reusable logic with LAMBDA
You'll calculate goals-per-match in several places. Rather than repeat the division and the divide-by-zero guard, define it once.
Formulas > Name Manager > New, name it GoalsPerMatch, and in "Refers to":
=LAMBDA(goals, matches, IF(matches=0, "", goals/matches))Now it behaves like a built-in function anywhere in the workbook. =GoalsPerMatch(308, 104) returns 2.96.
And to run it down a whole column at once, hand it to MAP:
=MAP(Editions[Goals], Editions[Matches], GoalsPerMatch)One formula, one cell, the entire column calculated. No fill-down, and therefore no possibility of row 47 quietly holding a different formula from row 46 - which is the most common silent error in spreadsheets anywhere.
Two more worth knowing:
=SCAN(0, Editions[Goals], LAMBDA(acc,v, acc+v))=TEXTJOIN(", ", TRUE, FILTER(Teams[Team], Teams[Debut]="Yes"))That last one gives you "Cabo Verde, Curacao, Jordan, Uzbekistan" as a single string - perfect for a dynamic chart subtitle.
Step 8 - Make it look like football
Formulas are half the job. Here's the styling that turns a grid into a dashboard.
The palette
Dark backgrounds make numbers glow, and a pitch at floodlit night is a gift of a colour scheme:
Apply the canvas colour to the whole used range first, then lay the lighter cards on top. Working dark-first is much faster than colouring cards one by one.
Kill the gridlines
View > uncheck Gridlines. Non-negotiable. Gridlines are the difference between "a spreadsheet" and "a dashboard".
Build a layout grid
Select columns B through AN and set the width to 2.6. You now have a fine modular grid, and a "card" is just a merged block of it. This is how designers work, and it's why proper dashboards have alignment that column-by-column sizing can never achieve.
Icons
Press Win + . (or Ctrl + Cmd + Space on Mac) and drop emoji straight into cells. They render at any size and cost nothing. For something sharper, Insert > Icons gives you Microsoft's SVG library, which recolours cleanly to your palette.
In-cell bars
For a compact ranking, skip the chart entirely. Select your goals column and use Home > Conditional Formatting > Data Bars > More Rules, set a solid gold fill, and tick "Show Bar Only" if you want the bar without the number.
For a sparkline effect that scales with the cell, REPT still can't be beaten:
=REPT("|", [@Goals])Step 9 - The completeness indicator
Remember those 39 unconfirmed teams. Rather than hide the gap, surface it:
=LET( keep, ((Teams[Confederation]=Conf) + (Conf="All")), sel, FILTER(Teams[Stage Reached], keep, ""), known, SUM(--(sel<>"")), total, ROWS(sel), known/total)Format as 0.0% and label it "Data completeness". Add a caption underneath:
=TEXT(known,"0") & " of " & TEXT(total,"0") & " selected teams have a confirmed finishing band"The -- in SUM(--(sel<>"")) is a double negative: it coerces TRUE/FALSE into 1/0 so SUM can add them. You'll see it constantly in array formulas and it's worth recognising on sight.
Anyone reading your dashboard now knows exactly how much of it is solid. That is a professional habit, and it costs one formula.
Step 10 - Add slicers for a click-driven version
Dropdowns are compact, but slicers look better and allow multi-select.
Click any cell in the Teams Table > Table Design > Insert Slicer > tick Confederation. A slicer panel appears; it filters the Table directly.
To read the slicer's state in a formula, use SUBTOTAL with function number 3 (COUNTA), which ignores rows hidden by a filter:
=SUBTOTAL(103, Teams[Team])That returns the count of visible rows. Style the slicer to match with Slicer > Slicer Styles > New Slicer Style, setting the header and item fills to your card and pitch greens.
Reconciling with the official figures
One last professional touch. Your dashboard now computes from your own table - but the official tournament totals are published. Put both side by side:
Official | In my data | Delta | |
Teams | 48 | =ROWS(Teams) | =B2-C2 |
Prize pool | $671m | =SUM(Teams[Prize $m]) | =B3-C3 |
If the delta is anything but zero, you have a data problem - and you'll find it now, not in front of a client. Every dashboard you build for someone else should have a reconciliation block like this somewhere, even if it lives on a hidden sheet.
The verified numbers behind this build
Every figure in the workbook is sourced. For the record:
104 matches, 308 goals (2.96 per match) - the most of any World Cup; the previous record was 172 goals in Qatar 2022
48 teams in 12 groups of 4, with a new Round of 32
6.8m+ total attendance, 65,490 average, 99.7% occupancy - beating the 1994 record of 3,587,538, broken with 44 matches still to play
Final: Spain 1-0 Argentina after extra time at New York New Jersey Stadium, Ferran Torres 106'; Spain outshot Argentina 20-3
Third place: England 6-4 France
Golden Ball: Rodri (Spain). Golden Boot: Kylian Mbappe (France, 10 goals). Golden Glove: Unai Simon (Spain, 7 clean sheets, 1 goal conceded). Young Player: Pau Cubarsi (Spain)
Prize pool: $871m total, of which $671m is performance-based; $50m to the winners
Cheat sheet
Task | Modern | Legacy |
Look up a value | XLOOKUP | VLOOKUP / INDEX+MATCH |
Filter rows | FILTER | AutoFilter, IF arrays |
Unique list | UNIQUE | Remove Duplicates |
Sort | SORT / SORTBY | Data > Sort |
Group and total | GROUPBY | PivotTable, SUMIF |
Cross-tab | PIVOTBY | PivotTable |
Name an intermediate | LET | helper cells |
Reusable function | LAMBDA | VBA / copy-paste |
Apply down a column | MAP / BYROW | fill down |
Running total | SCAN | SUM($B$2:B2) |
Top N | TAKE(SORT(...)) | sorted copy |
Combine ranges | VSTACK / HSTACK | copy-paste |
Pick columns | CHOOSECOLS | rearrange source |
Download the workbook
The finished file is attached. It contains a Start Here sheet (legend, sources and the known data gaps stated plainly), the Dashboard itself, the Teams and Tournament source tables, an Engine sheet with the calculation layer fully exposed so you can trace any number, and an Excel 365 sheet giving every calculation written both ways.
One caveat worth flagging: the shipped file uses SUMPRODUCT, INDEX/MATCH and COUNTIF rather than FILTER and GROUPBY. That's deliberate. A file generated outside Excel can't write the spill metadata that dynamic arrays need, so the compatibility versions guarantee it opens correctly in Excel, LibreOffice and Google Sheets alike. The Excel 365 sheet gives you the modern equivalent of every one, ready to paste in. Same numbers, about a third of the formula text.
Your turn
Fill in the 39 blank Stage Reached cells from the FIFA final standings and watch the completeness indicator climb to 100%. Then try:
a Group stage sheet with all 12 groups and a SORTBY league table
a knockout bracket built from HSTACK and conditional formatting
an xG column, if you can find the data, and a scatter against actual goals
Post what you build - I'd like to see it.
Questions about any step? Reply below, or book a session and we'll build one against your own data.
Comments