Wow, what a Summer Olympics. the 2012 medal count is so close. China and the USA are neck and neck in terms of total medals, but not in Gold medals.
So in the last post we were discussing building a medal count dashboard in Excel for the Summer Olympics.
We had found some data on Wikipedia and we were going to look at the data and see how we might design an Olympics Medal Count Dashboard.
How does yours look?
Here are some of the steps that I follow when building any Excel Dashboard.
1) Hide All Gridlines in the Excel Worksheet.
This is always a good idea. The Excel Gridlines are nice, but they create a lot of clutter and are not really needed when building your dashboard. Actually, I prefer to leave off all the gridlines and then use Custom Excel Borders to define different dashboard areas.
2) After hiding the gridlines, I then would like to determine what my dashboard will look like.
By that I mean:
Is it limited to one printed page?
Is it limited to one visible page on the computer screen?
Does the Excel Dashboard Charts need to be interactive?
These are the types of questions you should ask yourself before you begin developing the Excel Data Tables and Excel Charts and Graphs.
3) Look at the data and see what you would like to display. You don’t actually need to build the charts just yet, but get a concept of what you data and charts will look like in their final stage so that you can plan your dashboard layout.
4) Sketch out your dashboard design. I will typically use my blank Excel Worksheet to create the dashboard design by highlighting data/chart regions with Excel Custom Borders.
Here is what my excel 2008 to 2012 Olympic Medal Count dashboard layout will look like:
Lets take a look at each section of this layout and a few other questions.
1) I decided that the data set that I had wouldn’t require more than one screen shot of charts and tables.
2) It does not need to be interactive.
3) I also decided that the data for just 2008 would not be robust enough to create my desired Excel Dashboard. So I decided to add in the 2012 Summer Olympics to the layout as well for comparisons.
4) Some of my data will be displayed in Excel data tables and some with Excel bar charts
Dashboard Layout Breakdown
Title, Date and Data Source Dashboard Area
At the top I have a section for the title, date and data source references. I think it is always a good idea to block out space for your dashboard title and date as well as any other helpful information. This will allow you to save space for other information in chart titles and will also save you ink. Plus it will set your readers in the right mindset of what they are going to see in this dashboard.
Top 10 2008 Olympic Gold Medal Data Table and Top 10 2008 Olympic All Medals Won Data Table
On the left side of my Olympic Medal Dashboard, I will put a list of the Top 10 Countries that won Gold Medals in the 2008 Olympic games. Below that I will have a comparison of the Top 10 Countries that won All Medals (including Silver and Bronze) in the 2008 summer games.
Top 10 2012 Olympic Gold Medal Data Table and Top 10 2012 Olympic All Medals Won Data Table
On the right side of my Olympic Medal Dashboard, I will put a list of the Top 10 Countries that won Gold Medals in the 2012 Olympic games to contrast the left side of the dashboard. Below that I will have a comparison of the Top 10 Countries that won All Medals (including Silver and Bronze) in the 20012 summer games.
Stacked Bar Charts
Finally, in the center section of the Dashboard Layout, I will put 2 stacked bar charts of the 2008 Summer Olympic Games and next to it the comparable 2012 Summer Olympic Games of all medals one with each stacked chart showing Bronze, Silver and Gold medals won during that year.
What other charts, tables, lists or graphs would you include in your Excel Dashboard?
Let me know in the comments. I DON’T want the Olympics to end? Do you? It is so much fun to watch and amazing how much people can achieve. Don’t you agree?
I’ve been using Excel to chart the medal counts of the 2012 olympics.
Currently, I have a table that displays a list of all of the countries sorted by Gold, Silver, Bronze or Total. Additionally, there is a horizontal bar chart that extends to the right of the data showing how many of the medal that is being sorted has been won by the country. Lastly, there is an option to compare two countries and it shows 2 horizontal bar charts with gold, silver and bronze medals won.
I could upload it to give an example, but I don’t see any upload features on the website.
Check out Mitch’s incredible 2012 London Olympic Medal Dynamic Excel Dashboard in the most recent post. Thanks Mitch!