Motion charts in Excel

Creating motion charts in Excel is a simple four-step process.

  1. Get the data in a tabular format with the columns [date, item, x, y, size]
  2. Make a “today” cell, and create a lookup table for “today”
  3. Make a bubble chart with that lookup table
  4. Add a scroll bar and a play button linked to the “today” cell

For the impatient, here’s a motion chart spreadsheet that you can tailor to your needs.
For the patient and the puzzled, here’s a quick introduction to bubble and motion charts.

What is a bubble chart?

A bubble chart is a way of capturing 3 dimensions. For example, the chart below could be the birth, literacy rate and population of countries (X-axis, Y-axis and size). Or the growth, margin and market cap of companies.

Example of a bubble chart

It lets you compare three dimensions at a glance. The size dimension is a different from the X and Y axes, though. It’s not easy to compare differences in size. And the eye tends to focus on the big objects. So usually, size is used highlight important things, and the X and Y axes used to measure the performance of these things.

If I were to summarise bubble charts in a sentence, it would be: bubble charts show the performance of important things (in two dimensions). (In contrast, Variwide charts show the same on one dimension.)

Say you’re a services firm. You want to track the productivity of your most expensive groups (“the important things”). Productivity is measured by 2 parameters: utilisation and margin. The bubble chart would then have the expense of each group as the size, and its utilisation and contribution as the X and Y axes.

What is a motion chart?

Motion charts are animated bubble charts. They track the performance of important things over time (in two dimensions). This is chart with 4 dimensions. But not all data with 4 dimensions can be plotted as a motion chart. One dimension has to be time, and another has to be linked to the importance of the item.

 

Motion charts were pioneered by Hans Rosling and his TED Talk shows you the true power of motion charts.

How do I create these charts?

Use the Motion Chart Gadget to display any of your data on a web page. Or use Google Spreadsheets if you need to see the chart on a spreadsheet: motion charts are built in.

If you or your viewer don’t have access to these, and you want to use Excel, here’s how.

1. Get the data in a tabular format

Get the data in the format below. You need the X, Y and size for each thing, for each date.

Date Thing X Y Size
08/02/2009 A 64% 11% 1
08/02/2009 B 14% 33% 2
08/02/2009 C 78% 55% 3
08/02/2009 D 57% 73% 4
08/02/2009 E 39% 32% 5
08/02/2009 F 40% 81% 6
09/02/2009 A 64% 12% 1
09/02/2009 B 14% 33% 2
09/02/2009 C 78% 56% 3
09/02/2009 D 57% 73% 4
09/02/2009 E 39% 32% 5
09/02/2009 F 40% 81% 6
..

To make life (and lookups) easier, add a column called “Key” which concatenates the date and the things. Typing “=A2&B2” will concatenate cells A2 and B2. (Red cells use formulas.)

Date Thing Key X Y Size
08/02/2009 A 39852A 64% 11% 1
08/02/2009 B 39852B 14% 33% 2
08/02/2009 C 39852C 78% 55% 3
08/02/2009 D 39852D 57% 73% 4

2. Make a “today” cell, and create a lookup table for “today”

Create a cell called “Offset” and type in 0 as its value. Add another cell called Today whose value is the start date (08/02/2009 in this case) plus the offset (0 in this case)

Offset 0 (Just type 0)
Today 08/02/2009 Use a formula: =STARTDATE + OFFSET

Now, if you change the offset from 0 to 1, “Today” changes to 09/02/2009. By changing just this one cell, we can create a table that holds the bubble chart details for that day, like below.

Thing X Y Size Formula
A 44% 19% 1

X =VLOOKUP(TODAY & THING, DATA, 2, 0)

Y =VLOOKUP(TODAY & THING, DATA, 3, 0)

Size =VLOOKUP(TODAY & THING, DATA, 4, 0)

B 6% 13% 2
C 90% 71% 3
D 41% 61% 4
E 59% 40% 5
F 16% 77% 6

Check out my motion chart spreadsheet to see how these are constructed.

3. Make a bubble chart with that lookup table

This is a simple Insert – Chart. Go through the chart types and select bubble. Play around with the data selection until you get the X, Y and Size columns right.

Example of a bubble chart

4. Add a scroll bar and a play button linked to the “today” cell

Now for the magic. Add a scroll bar below the chart.
Excel 2007 users: Go to Developer – Insert and add a scroll bar.
Excel 2003 users: Go to View – Toolbars – Control Toolbox and add a scroll bar

Right click on the scroll bar, go to Format Control… and link the scroll bar to the “Offset” cell. Now, as you move the scroll bar, the value in the offset cell will change to reflect it. So the “today” cell will change too. So will the lookup table. And so will the chart.

Next, create a button called “Play” and edit its code.
Excel 2007 users: Right click the button, go to Developer – View Code.
Excel 2003 users: Right click the button and select View Code.

Type in the following code for the button’s click event:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 
Sub Button1_Click()
    Dim i As Integer
    For i = 0 To 40:            ' Replace 40 with your range
        Range("J1").Value = i   ' Replace J1 with your offset cell
        Application.Calculate
        Sleep (100)
    Next
End Sub

Now clicking on the Play button will give you this glorious motion chart in Excel:

  1. Obed says:

    Thanks for sharing your wealth of knowledge with others. Great stuff!!!

  2. Michael says:

    In their infinite wisdom Google is discontinuing motion chart gadgets in 2013 so a big thank you for this alternative:-)

  3. sireesh says:

    how to transfer bubble chart to ppt?

  4. Max says:

    Thanks for the great write up. Any ideas as to how one could add a “tail” behind the moving bubble to track the past data?

  5. Mike says:

    Does the code work with 2010? Nothing happens when I copy and paste it in. When I run debug the sleep (100) highlights even after I replace the data as you indicated with my own. Granted, I’m new to developer stuff so I may be making a silly mistake.

  6. Diana says:

    I’m also asking the same question as Mike #5. I can’t get the code to work in 2010 and the sleep (100) highlights. As I’m not a programmer and really don’t have a clue, I’d love to get this handled. I’ve come this far and have great individual graphs!. Thanks….

  7. Diana says:

    Actually…..shows how hopeless I actually am….I am in Excel 2013, not 2010!

  8. Stefan Selby says:

    It is interesting to see how you did your motion charts. I have created an app that does more than just bubble charts. I have done the same for most excel charts and a speedometer chart. It is shared with the world have a look at:https://googledrive.com/host/0B0Ms4sM4a2RoSW9YYmkxbVlMc2M/vbamotioncharts.html

  9. Loveleen says:

    I can’t get the code to work in 2010 and the sleep (100) highlights. As I’m not a programmer to understand the coding. COuld you please how it works in 2010
    regards,
    Loveleen

  10. Nathalie says:

    It is very interesting. However, there is one little problem that stops me from using this kind of chart: the size of the bubbles does not follow the variation of size values in time. For instance, if bubble A remains the biggest bubble over the time, it will keep the same size in the chart, even if its value has increased by a factor 3 one year. I would have expected the size to be 3 times bigger than in the previous year. Is there a way to remedy this (without having to use Google motion charts)?

  11. Durga says:

    This was very useful for me. Thank you for describing the play button logic. On the web site when I click on the pause or the stop button the chart stops at that moment but I didn’t see it in the excel. Could you please tell me how to code the stop button or the pause button on excel.

  12. Atom says:

    How do i change the years to seconds?

  13. Stef says:

    I have created an app that creates these and other types of motion charts. You can find it at http://developingdata.azurewebsites.net/Excel/ExcelMotionCharts

    Knowing excel and reading this article would help (good article) but I have tried to make the app easy to use.

  14. Des Klass says:

    Hi I am having the same problem as Mike(#5) and just can’t get the motion working. Greatful for any suggestions(I am using my own data)

  15. Ye says:

    Thank you so much for this workaround (I usually did this with googledocs) in excel. I am trying to make this work – not based on increasing date, but on temperature. Imagine a chemistry-based excel sheet and I want to show how different components change volume, weight and surface opaqueness with rising temperature. Right now, I struggle to make the today and offset work for me since the example is (like google docs) based on a start date.

    Thanks for your reply in advance.

    Y