Tour de France Excel Dashboard

Since Phil is somewhat of a cycling fan I have been subjected to late night TV of le Tour.

Personally I don’t understand all the complexities of it, what with different jerseys (some of which I must say are a little feminine), how the team aspect works, why crazy fans are allowed to jump out in front of riders and the other shenanigans that go on.

However, being an Excel fan I saw an opportunity to crunch some data, so I thought it’d be fun to put together an interactive Excel dashboard.

Voila

Don’t worry, I’m not going all French on you 🙂

Click to enlarge the image.

Click here to learn how to build Excel Dashboard reports like this.

Techniques Used

I really enjoyed putting this dashboard together as it gave me an opportunity to use a lot of different techniques, namely:

• Picture links for the podium images and maps.
• Linked Text boxes for the stage statistics summary.
• Active X Combo Boxes for the stage selection list.
• Scroll Bar Form Controls for the Individual and Team position tables.
• Tables to house the source data, and Structured References to build formulas quickly. The other benefit of using Tables was as each stage finished I just pasted the new data under the existing data and it was automatically incorporated into my formulas. Voila, alright!
• Named Ranges to help with audit trails and ease of building formulas.
• Shapes for Jersey Wearer table.
• Custom Number formats to hide zeros and show a + symbol before the gap times.
• VBA for the image zoom
• Plus quite a few different Excel Formulas:
  • IF, IF and OR, IFERROR to hide acceptable errors.
  • SUMIFS in the Individual and Team position tables.
  • VLOOKUP to return information about the rider.
  • OFFSET to create a dynamic range that updates when the scroll bars are clicked.
  • INDEX & MATCH to return the Jersey Wearers data.
  • Array formulas

And since all of the data came from the letour.fr website it needed substantial tidying up, and for this I used Text to Columns and a few formulas to rearrange the times that came in as text:

• MID, LEFT and RIGHT to pull it apart, and
• TIME to put it back together.

Dashboard Formatting

The formatting I used here is a bit flashier than I’d recommend for a management information style of report but since it’s for a sporting event I thought I’d have some fun with it.

Notice I still stayed in keeping with the colours of the race though.

Challenges

One of the biggest challenges was deciding what information to display. Of course it helped that the official website gave me some clues as to what stats are important. Remember, I am not a cycling fan.

But I needed to fit everything on one page (one of the golden rules of a dashboard), and yet there was so much data.

That’s where the interactive elements allowed me to cheat a little, as they enable me to share a lot more information in one report than I could on one static page.

The primary purpose of this report was to showcase Excel’s Dashboard report capabilities as opposed to a useful report for Tour de France fans, but if I’ve achieved both then très bon, I’ve unexpectedly excelled myself 🙂

Improvements

There’s a list of things I think I could do to improve the report, but time was against me.

For example:

1. Have the photos change to show top 3 places overall as well as for the stage depending on the selections (Stage or Overall), made in the position tables.
2. Make information about the teams and riders available from the Position tables or top 3 for each race.
3. Include images of the country flags for each rider and team.
4. Include the key moments from each race.
5. Include some charts that depict the gaps in the riders and teams.

The above are just a handful that come to mind. The great thing about this dashboard is now that the bulk of the hard work is done that I can update and improve it year on year.

Do you have any suggestions? Please leave them in the comments and I can consider them for the 2014 Tour.

Download the Excel File

If you'd like an unprotected version of the file you can get one free with my Excel Dashboard course.

If you’d like to know how to build interactive Excel Dashboards like this, and more, please take a few moments to check out my course.

Credits

All of the data in the report came from letour.fr.

Special thanks to Jon Acampora for the image zoom idea.

If you know any Tour de France fans or Excel fans please use the buttons below to share this with them on Google+1, LinkedIn, Twitter or Facebook, or leave a comment below and tell me what you think.