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:
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?
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 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!
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:
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.
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):
- 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.
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.
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.
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.
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.
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 to Roberto for helping me make my Match History formula more elegant.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.