One race, Every medalist ever – Interactive Excel Visualization

Posted on August 14th, 2012 in Charts and Graphs , Cool Infographics & Data Visualizations , VBA Macros - 16 comments

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.

Usain Bolt vs. Rest of Olympic Sprinters - Interactive Excel Chart - Demo

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”

  1. Debraj Roy says:

    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

  2. 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! 

  3. Michael Pennington says:

    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!

  4. mustafa says:

    Congragulations Purna, 

    I really really like the show details function in that work. Nice to see that type  amazing work.

    Thank you for sharing. 

  5. 5antiago says:

    Pretty damn cool ūüôā

  6. [...] 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 [...]

  7. Matthieu says:

    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

  8. Bert van Zandbergen says:

    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!

  9. Yann says:

    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! 

  10. Yann says:

    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! 

  11. afilash says:

    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 ūüôā ]

    • Hui... says:

      @Afilash
      Can you post the macro code and explain what you do to run it ?

      • afilash says:

        @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

  12. […] One race, Every medalist ever ‚Äď Interactive Excel Visualization by Chandoo […]

Leave a Reply