• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Football Dashboard Extreme Makeover

You are here: Home / Excel Dashboard / Excel Football Dashboard Extreme Makeover
sports league tables excel dashboard
October 23, 2013 by Mynda Treacy

Phil plays 6 a-side football (soccer) in a Supa-Oldie league. They’re all over 35 and that’s considered supa-old in football! Personally I think it’s an excuse to have a beer with the lads, anyway…

Darryl, the fellow who updates the scores, explained to Phil that every week it was taking him 2 hours to prepare the league tables as he had to re-calculate all the stats in his Excel spreadsheet. TWO HOURS!

Here is the ‘Before’ photo:

Football league table before photo

Every number in the league table above is hard keyed except for the 'Totals' rows! EVERY NUMBER.

Not only that, each week he had to manually rearrange the order of the teams as rankings changed, and update the colour coding for the teams that moved up or down.

No wonder it took 2 hours to update every week.

All that manual work is an Excel crime!

When we heard about this we thought it was a prime candidate for an ‘Excel Dashboard Extreme Makeover’.

So I put on my Dr Dashboard mask and nipped and tucked his league tables into shape.

And here is the ‘After’ photo:

Note: The after photo shows the current season’s teams and divisions which are slightly different to the before photo from last season, but I think you get the gist. It’s much better, right?

Football league table after photo

In complete contrast to the original league table every number is now the result of formulas that automatically recalculate. EVERY NUMBER.

Not only that, the teams are automatically sorted based on their new rankings and the colours and symbols also automatically change with the help of Conditional Formatting.

It now takes Darryl 10 minutes to update. In reality it could take 2 but he uses one finger to type.

It’s Excel heaven! Actually, it’s the way Excel was intended to be used.

Anatomy of the Dashboard

Hopefully you’re not squeamish because I’m going to take you into the gizzards (as my kids say) of this new league table dashboard so you can see how it works.

Note: Even if you're not interested in football, there are some great lessons here on different tools you can use in Excel and how to tie them together to make a dynamic and informative report.

So back away from the mouse and keep reading 🙂

Download

Download the workbook and follow along. Use it for your own league tables, reverse engineer it and see how it works, or print it off and make a paper plane, whatever tickles your fancy!

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Click here to Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Source Data

Like I said, previously all of the data was hard keyed except for the ‘Totals’ row which was only there for checking that he’d keyed everything in correctly. It was good to see that he'd built in cross checks.

With the new league table he simply enters the fixtures and scores for the week into a source data sheet for each division. The Premier division looks like this:

dashboard source data

You might be thinking, phew that’s a lot to key in, but in reality he already has almost all of this data for the fixtures draw. Formulas in the Result Home and Result Away columns return the W, L or D (win, loss, draw), so nothing to enter there.

And in actual fact he can set everything up in advance then once a week he completes the Home and Away scores, and if there’s a forfeit or no ref he enters them. 10 minutes later it’s all done!

By the way, the W, L or D feeds the Match History conditional formatting in the league table. More on that in a moment.

Workings

The league tables are fed by two sets of workings in columns to the right; one for the current week (columns AG:AR) and one for the previous week (columns AT:BD partially outside of image):

dashboard workings

In columns AH to AM and AR I’ve used SUMPRODUCT formulas, but you could also use SUMIFS and COUNTIF(S) to return the results.

  • P – Played SUMPRODUCT or COUNTIFS
  • W – Won SUMPRODUCT or COUNTIFS
  • D – Draw SUMPRODUCT or COUNTIFS
  • L – Lost SUMPRODUCT or COUNTIFS
  • F – Goals For SUMPRODUCT or SUMIFS
  • A – Goals Against SUMPRODUCT or SUMIFS
  • No Ref – SUMPRODUCT or COUNTIF

GD – Goal Difference is simply column AL – AM, and the PTS (Points) are referencing the values in cells AP2:AR2 located above the workings tables.

I’ve used the RANK function (column AQ) to rank the teams in the League Tables from top to bottom.

Note: because there could be ties I’ve first calculated a ‘Unique PTS’ in column AP which is weighted to avoid a tie on Points (PTS). It does this by also using the Goal Difference (GD) and an alphabetical ranking in the calculation.

The League Tables

The Rank result is then used in a VLOOKUP formula with CHOOSE to return a sorted list of teams in column A of the league table.

Tip: we use CHOOSE to get around the limitation of VLOOKUP not being able to lookup to the left. You could also use INDEX & MATCH.

VLOOKUP Left

Fonts and Conditional Formatting

Now, you might have wondered earlier why we need workings for the current week and previous week…well that’s because we need to show which teams have moved up, down or stayed the same from one week to the next.

I’ve done this with some wingding fonts in column B, and Conditional formatting is used to highlight the team’s row red if they moved down, blue if they moved up and black if they stayed the same.

Match History

The match history is also conditional formatting.

An INDEX and MATCH formula looks up the ‘Result’ columns on each division’s source data sheet and brings in the W (win), L (loss) or D (draw) for each week. Conditional formatting colours the cell green (W), red (L) or grey (D).

The challenge with this formula is that because the order of the teams shuffles each week the Match History also needs to shuffle to stay aligned to the team's new position in the league table.

Zebra Stripes

Did you notice the Conditional Formatting Zebra Stripes on the source data sheets?

The blue and white alternating lines allow you to easily see the records for each week's fixtures grouped together in banded blue/white lines.

You can learn how to do Zebra strips in varying numbers of rows here.

Group Buttons

I have used Group Buttons to hide the spare rows for each division. This allows the dashboard to be re-used from one season to the next and allow for changes in the number of teams.

I prefer to use Group Buttons to hide/unhide rows and columns as I find it quicker to toggle between them being hidden or unhidden.

excel group and outline

More on how to group and outline data here.

Want More?

Do your reports take hours to update like Darryl's did?

If you'd like to learn how to create dynamic reports like this that take just a few minutes to update, then check out my Excel Dashboard course where I teach these techniques and more.

Thanks

Thanks to Roberto for helping me make my Match History formula more elegant.

sports league tables excel dashboard

More Excel Dashboard Posts

ai dashboard design

AI Dashboard Design

AI dashboard design can give you some great ideas for design and color combinations, but it’s not smart enough to create useful reports…yet!
dark theme dashboards

Dark Themed Excel Dashboards

Dark Themed Excel Dashboards enable you to use formatting techniques that make your data pop and get your audience's attention.
excel dashboards custom backgrounds

Custom Excel Dashboard Backgrounds

Custom Excel Dashboard Backgrounds bring a level of polish and professionalism to reports and they’re super easy to create using PowerPoint

Embedding Excel Dashboards in a Web Page

Embedding Excel Dashboards in a Web Page from files on SharePoint or OneDrive for Business or OneDrive Personal.
excel student performance dashboard

Excel Student Performance Dashboard

How to build an Excel student performance dashboard from scratch. Download the completed Excel file for reference.
5 Pro excel dashboard design tips

5 Pro Excel Dashboard Design Tips

Excel Dashboard Design Tips you can use in both Excel and Power BI to ensure your reports and quick and easy to read.
excel interactive personal finance dashboard

Interactive Personal Finance Dashboard

Excel Interactive Personal Finance Dashboard to help you visualise and keep track of and manage where you’re spending your money.

Non-financial Excel Dashboards

Step by step how to build Excel Dashboards with non-financial data. Uses Power Query and Power Pivot to visualise NASA data.

Secrets to Building Excel Dashboards in Under 15 Minutes

Learn how to build interactive, clean and focussed dashboards that communicate the important information you want readers to know.
personal share portfolio dashboard

Personal Stock Portfolio Dashboard

Monitor your stocks with this personal share portfolio dashboard built with Excel using the refreshable STOCKHISTORY function and data types.




Category: Excel Dashboard
Previous Post:30 Chants for Better Excel Charts30 Chants for Better Excel Charts
Next Post:Excel Tabular Data FormatExcel Tabular Data Format

Reader Interactions

Comments

  1. Muchwa Kunihira

    March 15, 2018 at 8:06 pm

    I have watched your content on YouTube and you’re really amazing. Your dashboards are super neat, organized, and very clean. I admire that a lot.

    Quick question though; how do you draw those little shapes in excel (the red signaling a decline in performance, and a green showing an improvement in performance)?

    Reply
    • Mynda Treacy

      March 15, 2018 at 8:40 pm

      Hi Muchwa,

      Thanks for your kind words 🙂

      The red/green markers are done with Conditional Formatting (download the file and inspect how it’s done). You can also use Sparklines.

      Mynda

      Reply
  2. John

    December 11, 2017 at 10:38 pm

    Is there any way to update the match history to more than 18 rounds?

    Reply
    • Mynda Treacy

      December 12, 2017 at 5:19 pm

      Sure, you just need to add more columns and copy the formulas across accordingly. You’ll also want to add to the data validation list in cell AI1.

      Mynda

      Reply
  3. Sherman

    November 23, 2017 at 3:41 am

    This is. Soo helpfull
    Pity I won’t return as match secretary of our social league next year.. I will pass on the knowledge picked up here though!
    Thank u

    Reply
    • Mynda Treacy

      November 23, 2017 at 9:48 am

      Glad you liked it, Sherman.

      Reply
  4. Brendon

    June 2, 2015 at 5:01 am

    Mynda,

    Thank you for this excellent dashboard. I’m in an old man softball league and have modified your sheet to help the league manager present the results each week. I am having a bit of difficulty because we rank differently.

    The current sheet sorts based on wins(pts), then sorts on run differential. This would work in most leagues but, our second ranking is based on head to head results, then run differential.

    I am at a loss of how to create a formula that evaluates any team with the same win/loss record based on head to head results.

    Any ideas? Perhaps a manual sort would be the easiest in our situation. It has been a fun challenge for my excel skills however!

    Thanks again.

    Reply
    • Mynda Treacy

      June 2, 2015 at 9:01 am

      Hi Brendon,

      I’m not sure what you mean by ‘head to head results’ but if you can send me your workbook via the Help Desk with an explanation of what you want and how it should appear when sorted then I can take a look.

      Kind regards,

      Mynda

      Reply
  5. Kevin McIlroy

    February 16, 2015 at 1:15 am

    Hi Mynda,

    Have played around with this to no avail…

    Our League consists of 10/12 per division, however our top division is split into 2; Premier/Section One and both sets of teams play each other as if it was only one division but their results are displayed as separate divisions as shown below.

    This is how the league table is split

    Premier Section League Table 15/02/2015
    Pos Team Pld W L D For Agn Pts
    1st Abingdon Allstars 15 14 1 0 155 85 155
    2nd Rileys Red Devils 14 9 5 0 114 18 114
    3rd Oracle A 15 9 6 0 119 13 119
    4th West Oxford Democrats 14 12 2 0 141 72 141

    Section One League Table
    Pos Team Pld W L D For Agn Pts
    1st Rileys A 14 5 9 0 100 10 100
    2nd Donnington Social Club ‘B’ 15 7 8 0 106 13 106
    3rd Marlborough Club A 15 6 9 0 105 15 105
    4th Rileys Team Merola 14 5 9 0 104 2 104
    5th Midget ‘A’ 15 0 15 0 60 105 60
    6th Abingdon RBL 14 8 6 0 45 2 45
    7th Railway Inn 15 5 10 0 30 45 30
    8th BYE 0 0 0 0 0 0 0

    and this is how the fixture appear for the opening 3 weeks

    wk
    1 BYE Oracle A
    1 Railway Inn 4 11 Abingdon Allstars
    1 Abingdon RBL 4 11 Rileys Red Devils
    1 Rileys Team Merola 5 10 West Oxford Democrats
    1 Marlborough Club A 10 5 Rileys A
    1 Midget ‘A’ 5 10 Donnington Social Club ‘B’
    2 BYE Donnington Social Club ‘B’
    2 Rileys A 12 3 Midget ‘A’
    2 West Oxford Democrats 8 7 Marlborough Club A
    2 Rileys Red Devils 8 7 Rileys Team Merola
    2 Abingdon Allstars 10 5 Abingdon RBL
    2 Oracle A 11 4 Railway Inn
    3 Railway Inn BYE
    3 Abingdon RBL 5 10 Oracle A
    3 Rileys Team Merola 6 9 Abingdon Allstars
    3 Marlborough Club A 10 5 Rileys Red Devils
    3 Midget ‘A’ 1 14 West Oxford Democrats
    3 Donnington SocClub ‘B’ 7 8 Rileys A

    Reply
    • Mynda Treacy

      February 16, 2015 at 1:34 pm

      Hi Kevin,

      Can you not just enter them as though they are two separate divisions since that’s how you want the league table results displayed?

      The fact that both teams may appear in both fixtures doesn’t matter.

      Mynda

      Reply
  6. Kevin McIlroy

    January 27, 2015 at 6:53 pm

    Hi,

    You’ve allowed the tables for up to 12 teams but if the play each other HOME and AWAY it will take 22 weeks to complete, but you’ve only allowed for 18 weeks. How do I increase the number of weeks.

    Looks great..

    Reply
    • Mynda Treacy

      January 27, 2015 at 10:06 pm

      Hi Kevin,

      In column BG add more weeks then increase the Data Validation source for cell AH1 to pick up the extra weeks. You’ll also need to insert new rows for each division and copy down the formulas.

      Be careful when you insert the rows that you don’t mess up the data validation list source in column BG.

      Hope that helps.

      Mynda

      Reply
  7. Dennis Chung

    January 27, 2015 at 3:39 pm

    Hello,
    Can you please
    1) Cell B4.How did you get these two symbols side by side (pq)
    2)Cell AH5.Can you pls explain the formula as I am getting difficulty interpreting/understanding this formula
    3)Cell AH18,AH33,AH48.What are their utilities?

    Thank you

    Reply
    • Catalin Bombea

      January 27, 2015 at 5:06 pm

      Hi Dennis,
      1. IN B4, the font is Wingdings, that’s why pq is seen as symbols, instead of text, try changing the font, you’ll see what i mean.
      2. The SUMPRODUCT formula has multiple arguments, it will return the count of rows where all arguments are TRUE :
      (ISNUMBER(premier[Home Score])
      (premier[Week]<=$AH$1) (premier[Team Home]=Results!AG5) ISNUMBER(premier[Away Score]) (premier[Week]<=$AH$1) (premier[Team Away]=Results!AG5) It is similar to COUNTIFS formula, where you can add multiple ranges and criterias to count: for example, the last 2 conditions will have the following syntax in COUNTIFS formula: =COUNTIFS(premier[Week],$AH$1,premier[Team Away],Results!AG5) In SUMPRODUCT, the conditions are wrapped in paranthesis and multiplied, this is the only difference. 3. In Super Champions sheet, there are only 6 teams, that's why in cells you mentioned the value is 6. You may replace that static value with a dynamic formula: =COUNTA(super[Team Home]), that will autoadjust when you add teams to that table. As the Instructions says, if you read them, you will find this mention: "The Match History formulas allow for up to 18 rounds. There is enough space for 12 teams in each division. To hide/unhide the spare rows use the Group buttons to the left of the row numbers " Regards, Catalin

      Reply
  8. Kevin McIlroy

    June 19, 2014 at 2:40 am

    How do you populate the Fixtures, do you do this manually ?

    Reply
    • Mynda Treacy

      June 19, 2014 at 9:54 am

      Hi Kevin,

      Yes, you have to type in the fixtures yourself.

      Mynda

      Reply
      • Kevin McIlroy

        February 14, 2015 at 9:06 pm

        Hi Mynda,

        No problem with fixtures as I’ve been able to add a TEAMS sheet and use LOOKUP on the divisional sheets to populate the fixtures automatically by adding another 2 columns into which I input the 1 vs 2, 3 vs 4 etc….

        Only problem I have now is that the Group buttons to the left of the row numbers only switch between 8 and 12 (I have league of 10) even after changing the “Number of Teams” box in column AH on the Results sheet

        Kevin

        Reply
        • Mynda Treacy

          February 15, 2015 at 8:56 pm

          Hi Kevin,

          The group buttons need to be changed using the Group tool on the Data tab of the ribbon. First remove the groups that are there and then reapply the. Here is a tutorial on Grouping rows and columns:

          https://www.myonlinetraininghub.com/excel-2007-group-and-outline-data

          Kind regards,

          Mynda

          Reply
          • Kevin McIlroy

            February 15, 2015 at 9:09 pm

            Thanks for the reply Mynda, managed to work it out for myself for a change

            Kevin

          • Mynda Treacy

            February 15, 2015 at 9:10 pm

            🙂 Well done!

  9. Dan Sheets

    February 4, 2014 at 7:51 am

    I am American like some of the other submissions, but this is something that I definitely will ponder and dissect. Thank you for these morsels.

    Dan

    Reply
    • Mynda Treacy

      February 4, 2014 at 7:58 am

      You’re welcome, Dan 🙂

      Reply
      • Bruce Elvin

        February 10, 2014 at 5:48 pm

        I am glad to your post, really the dashboard is working Excellent.Basically the formula you put here is making a proper structure what actually I was need keep it up!

        Reply
        • Philip Treacy

          February 10, 2014 at 7:36 pm

          Thanks Bruce, glad you found it useful.

          Reply
  10. Juan

    October 28, 2013 at 10:24 pm

    Thank you very much for sharing this wonderful spreadsheet with us, Mynda, it’s a piece of art, I am amazed of what you can do to squeeze the power of Excel to the next level! As Bryan said, it’s difficult for me to understand what the numbers mean because I am not a big fan of sports, although soccer is very popular in Paraguay, but I must recognize that you did an awesome job rearranging all the data, it looks so different and interactive now!

    Reply
    • Mynda Treacy

      October 28, 2013 at 10:27 pm

      Thanks, Juan. I’m glad you like it. Please download the file (if you haven’t already) and play around with it to see how the formulas work.

      Kind regards,

      Mynda.

      Reply
  11. Bryan

    October 24, 2013 at 9:05 pm

    Wow, so many clever things going on here. I don’t fully appreciate what all the numbers mean (I’m not into sports, and I’m American, so even if I were I wouldn’t be into soccer!), but Excel is Excel so the formulas themselves (mostly) make sense. I’m going to have to hunker down and play with a few of them to figure out exactly how they work. I think my favorite part is the ability to re-rank every week. That is definitely a trick I’m going to have to put in my book.

    Reply
    • Mynda Treacy

      October 24, 2013 at 9:10 pm

      Thanks, Bryan 🙂 Glad you liked my treasure trove of Excel tricks.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.