During London 2012 Olympics, Usain Bolt reached the 100mts finish line faster than anyone in just 9.63 seconds. Most of us would be still reading this paragraph before Mr. Bolt finished the race.
To put this in perspective, NY Times created a highly entertaining interactive visualization. Go ahead and check it out. I am sure you will love it.
So I wanted to create something similar in Excel. And here is what I came up with.
Demo of Usain Bolt vs. Rest Visualization
Here is a quick demo of this interactive chart. Click on it to enlarge.
How is this made?
One thing is sure, It took me more than 9.63 seconds to create this 😉
The basic ingredients of this chart are Interactive Hyperlinks, Conditional formatting, Form Controls, INDEX + MATCH formulas, Picture links and Tables.
Since explaining all this is going to take forever, I made a short video showing you behind curtains of this. Watch it below [or click here to watch it on YouTube]
Download this Interactive Excel Workbook
Click here to download the workbook and play with it.
Note: This works in Excel 2010 or above only.
Do you like this visualization
I loved re-constructing this NY Times chart. It was a nice challenge. Although so many hyperlinks made Excel a bit sluggish, it was worth it.
What about you? Do you like this? Go ahead and break apart the download file and see what more you can do.
Special thanks to NYTimes for the inspiration.
16 Responses to “One race, Every medalist ever – Interactive Excel Visualization”
Chandoo..
Nice Visualization.. I wish there was a Olympics should also have a event for EXCEL..
Thanks a ton bro..
PS:
In Data Sheet, when I pressed to view Formula (Ctrl + `) It shows "Excel Cannot Complete this task with...... " But after error its working fine..
Any Idea Why?
Regards,
Deb
I LOVE THIS~! The interactive hyperlink rollover method lives!
Alright, so I didn't watch the entire video, so I don't know if you address it in the video. I was wondering why you chose to use an IF statement in your hyperlink rollover formula, but then it occurred to me that you probably ran into the same problem as I have previously. Specifically, the stuff in the friendly name and iferror results of the Hyperlink formula don't always show up, correct?
The way around this is to create a parallel intermediate table that already has all the values in it. So for this spreadsheet, you would just make an intermediate table exactly like one display on the view tab but with 1s,2s,3s,0s calculated to reflect gold, silver, bronze, and blank. Then in the view table, you would reference these values from the intermediate table rather than calculating them in the rollover formula - that's essentially how I got around the problem for my Rollover Gantt Chart and Interactive Periodic Table. I think removing the IFs will seriously speed up that sluggishness - I don't know IF is considered volatile, but it certainly has the speed of a volatile function.
Other ideas (like if you really are going for speed gold!) might be to:
Replace the conditional formatting of medal colors to a custom format like [Color44][>2]"?";[Color25][>1]"?";[Color6]"?";"" (you would still keep the conditional formatting for cell highlighting).
Use Sheet1.Range("valSelRunner").value2 instead of [valSelRunner] because - thanks to the work of Daniel Ferry at Excel Hero - the former has been shown to be faster.
Replace lookup functions like Match with Sumproduct - although this would have made it terrible to read and show to other folks - I prefer readable formulas when presenting to other people
I don't really know if passing a range of itself can make things slower, but it might become a possible circular reference error...? You might consider just passing Row() and Column() into the showDetails instead a cell range.
When writing the row and column values to the sheet, since they are stacked in a contiguous area, you could write to both of them at the same time with something like sheet1.Range("valRowCol").value2 = vRowCol where vRowCol is a variant area holding the Row and Col values.
Looking at this list of ideas, I think only 1 & 2 will show immediate improvement. But you never know until you try - so I guess I'll have to try these later today 🙂
Thanks for posting this great chart!
Hi Jordan,
Many thanks for sharing about this technique with all of us and making this happen. I like your suggestions, let me try a few of them and see how they speed up the workbook. I will share my findings later in the week.
Any success? I'm really interested in what you find.
Wow, that is awesome! When I saw this a few days ago, I wondered how closely Excel could replicate it. Pleasantly surprised to find this post this morning answering my question. Thanks for the great post and the video. I can't wait to really dig into the spreadsheet and hopefully pick up a few new techniques. Keep up the great work, Chandoo!
Congragulations Purna,
I really really like the show details function in that work. Nice to see that type amazing work.
Thank you for sharing.
Pretty damn cool 🙂
[...] often recreate visualisation already done using other softwares. Here is one such example from Chandoo.org where he recreates the highly interactive NYT post on Olympic medals. In this video below, he [...]
Another way to do this, without any hyperlink or calculation, would have been to add "Conditional Formatting/Icon Sets/Rating" to a simple pivot table, to display stars of different colors for Gold / Silver / Bronze medals, then selecting "Show sheet right-to-left" as "Display options for this worksheet", to move the pivot field column corresponding to the year+venues to the far right, like on your graph.
Then when, for any value of the pivot table, you call the pivot "Show Details" function (i.e. you double-click a star on the display), and copy the details to a box shape overlaying the pivot table,
you get a similar onmouseover event effect.
It runs quite well and fast. I can send you the spreadsheet if you give me an email address.
The only bit of VBA code used in the worksheet was:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim name As String
Application.ScreenUpdating = False
Sheets("Details").Activate
ActiveSheet.Cells.Select
Selection.ClearContents
Sheets("Pivot").Activate
Target.ShowDetail = True
name = ActiveSheet.name
Selection.Copy
Sheets("Details").Activate
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.DisplayAlerts = False
Sheets(name).Activate
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("Pivot").Select
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True
End Sub
Jordan Goldmeier wrote:
"The way around this is to create a parallel intermediate table that already has all the values in it. So for this spreadsheet, you would just make an intermediate table exactly like one display on the view tab but with 1s,2s,3s,0s calculated to reflect gold, silver, bronze, and blank. Then in the view table, you would reference these values from the intermediate table rather than calculating them in the rollover formula".
In a square I'm also using intermadiate tables. An intermidiate table with rows and columns is easier, gives flexibility and makes Excel faster!
Hi Guys
@Chandoo, Jordan and Hui: congrats for your breakthroughs on rollovers! This is a really neat way to improve the style of output materials...
However, there is one thing I did not manage to replicate in my own excel: my dynamic hyperlinks do not activate on rollover when the sheet is protected, whereas it works perfectly fines on the 100mts-records excel file here...
Any suggestions?
Thanks in advance!
in the meanwhile and FYI I have also found some interesting script here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=664
This totally solves the problem (ie: the dashboard is perfectly locked and cells cannot be selected); however I am still curious to know how Chandoo did to realise the same thing on its excel files without VBA script!
Hi Chandoo,
If you run a macro using hyperlink , it will only execute the first line and error out? why is that ? any idea to solve this problem [ other than using excel2010 🙂 ]
@Afilash
Can you post the macro code and explain what you do to run it ?
@Hui thank you for your reply
The problem is that i was trying to recreate the above effect in excel 2007
But when I use the same script from excel file provided by Chandoo, The program is not running (means the shape object do not snap to the positions ) and the cell we name to tap the row, col value didn’t even display anything.
So I changed the program little bit , Now the snapping works but the renamed cells (VALCURRROW and VALCURRCOL) is not showing any value !
Excel file is here : http://www.sendspace.com/file/b0t6z4
Function showDetailsDUMMY(runnerNum As Integer, caller As Range)
Application.EnableEvents = False
If ([VALSELSTUDENT] <> runnerNum) Then
[VALSELSTUDENT] = runnerNum
[VALCURRROW] = caller.Row
[VALCURRCOL] = caller.Column
With ActiveSheet.Shapes("grpDetail")
.Left = caller.Left + 16
.Top = caller.Top + 16
End With
End If
Application.EnableEvents = False
End Function
Function showDetails(runnerNum As Integer, xtr As Range)
Application.EnableEvents = False
If (Range("VALSELSTUDENT").Value <> runnerNum) Then
Range("VALSELSTUDENT") = runnerNum
Range("VALCURRROW").Value = xtr.Row
Range("VALCURRCOL").Value = xtr.Column
End If
With ActiveSheet.Shapes("grpDetail")
.Left = xtr.Left + 16
.Top = xtr.Top + 16
End With
Application.EnableEvents = True
End Function
[…] One race, Every medalist ever – Interactive Excel Visualization by Chandoo […]