Motion charts in Excel

March 23, 2009 / Excel tips, Tools / 69 Comments

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:

Related Posts

69 thoughts on “Motion charts in Excel”

  1. Vinoth

    Hi Anand.Very useful article.
    I can use similar motion chart to showcase how each of my team member’s cumulative performance is contributing at the overall level.

  2. Nach

    Thank you…this is something that I have been trying to do for a long time now…but didnt know how to write the looped macro in Excel.

  4. S Anand

    Sure. Use the sleep function. Add this to the module:

    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Then add a Sleep function after Application.Calculate

     Sleep 200
  7. Zoheb

    also could you rewrite the whole code,on how to slow it down.. i tried inserting it.but i don’t think i put it in the right place.

  8. Al-Hamour

    Great post! Thanks for sharing. Time delay code is very useful if you can upload it.. right now it goes too fast to see.

    Also, is there a way to make the graph trace each of the moving circles?

  9. S Anand

    I’ve changed the code and also revised it in the Excel file. Take a look.

    To increment the counter by 3, change the properties of the scroll bar to make the increment 3 instead of 1. (Right click on the scroll bar and select “Properties”)

  11. Firman

    Thank you so much… i hope in the near future you can give us tutorial / article about how to make it more interactive… say… after we click the bubble we can divide it into smaller / more detailed parts.

    Thank you

  13. Pradeep Jindal

    Fabulous! I just starting to do it and find this article…
    everything redy and beautiful…

    can we add drill down to it

    Thank you Anand 🙂

  16. Sabnish

    Hi…great help…thanks a lot….but for my presentation i will need to see the evolution by years…in this example it is the date which gets incremented…how to increase the year instead…thanks…hope you can help me….

  19. beth

    do you know how you can embed this into a powerpoint? when i try to embed, you can’t do the motion.

  20. Michael Brault

    I have the motion chart embed into a Dashboard with other charts and tables. The macro increments all tables. Any ida on how to limit the macro to a single table of data?

  22. Jeff Koenig

    Great bubble chart solution. I’ve been looking for a 3D bubble chart so I can move bubbles in 3D space, allowing me to track an additional dimension. Any ideas?

  23. Dirk Cornette

    Great solution Anand.
    I am trying to customize it, showing 1 customer per buble, size = revenue, Y-axes = number of our staff working for that customer, X axes showing margin %.

    Solving two issues would make it more gapminder-like.
    1. How to add a data label to the buble, showing the customer name ?
    2. How to show the year ?

    Any ideas ?

  25. derek

    Another kind of motion chart can be a line or bar chart showing the evolution of multiple line shapes or histograms with time.

    You can also add a time display to show when the events are occurring. Rosling’s application cleverly displays the year as a giant watermark behind the bubbles, so the eye doesn’t have to stray to a corner to see the time. I’ve often thought a clock face symbol might be a neat alternative to a simple text display.

  27. NIall Tallon

    HI Anand,

    This is great – a while since I updated and wrote macros – I am running a 64bit version of windows 7 and excel 2010 – and the macro won’t run there – any ideas?

    Thanks,

    Niall

  28. Niall Tallon

    HI Again,

    Actually just have to add “PTRsafe” in the macro near the declare statement.

    Pretty easy…….Email me if you want a copy with the changed macro.

    Best regfards and with thanks,

    NT

  30. Scott

    Hi,

    Is it possible to make this so the individual bubbles have different colors or at least unique labels? I can only seem to have all the bubbles be one color and one label ‘Thing’….

    Thanks,
    Scott

  31. Sreekar

    Hi Anand – I tried using your macro and it gives a compilation error “Sub or Function not defined”; would you know how I could fix it? I am using 2010 Excel

  32. James Arnott

    Hi – I’ve come late to this but am finding it very useful. Thanks for sharing your work with us as it would have taken me ages to work it out myself.

  33. Alan

    Can you please post me the updated version for Excel 2010.

    I dont know how to update the declare statement to include “PTR Safe”

    Thank you
    Alan

  34. Anna

    Does anyone know if it is possible to include this chart in a Share Point site? I know you can upload dashboards with the REST API but I don’t know if it is possible to add motion charts. Can you shed some light on this topic. Thanks!

  36. kalaisam

    Hai,
    I read Excel file from my Google account using Zend. I call Motion chart using Publish gadget script. It automatically assign the default X-axis and Y-axis .I wish to assign the X- axis and Y-axis . I choose Advanced settings option to change the Axis and then call that script string in Php code. But Default axis only displayed ! How can i fix that ?

  38. Chris

    Wonderful! Thanks for all this work. I use Excel 2010 and it shouldn’t be too hard to adapt it (I won’t bother you about it).

    It’s too bad it can’t be embedded in PPT. Microsoft definitely needs to add this kind of thing to PowerPoint.

  39. Bill

    Is it possible to have bubble diameters also show thier diameter change over time? Various smoothing algorithms could be offered to choose from.

  40. CK

    Thank you for this useful tutorial.

    How did you embed the motion chart moving on your web page please?

  41. Kirk

    I tried this macro but got an error saying the macros in this project are disabled. Anyone have an idea on what I am doing wrong?

    Sub Button20_Click()
    Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)

    Sub Button1_Click()
    Dim i As Integer
    For i = 0 To 40:
    Range(“J1”).Value = i
    Application.Calculate
    Sleep (100)
    Next
    End Sub

  42. Pvl

    Hi, Anand,

    Thanks for good tutorial. But I’m very interested in creating such motion chart in Flash, like You posted as an example. Any suggestions how to do that?

  44. Gary

    First let me say thank you. The code you posted did not work for me. I am using MS Excel 2010 and I keep getting the same error.
    Microsoft Visual Basic for Applications
    Compile error:
    Sub or Function not defined.

    Can you help me fix this error. This is what I have typed.
    Sub Button1_Click()
    Dim i As Integer
    For i = 1 to 39:
    Range (“C1”).Value = 1
    Application.Calculate
    Sleep (100)
    Next
    End Sub

  45. Gary

    Oh, sorry to mention that it stops at the Sleep (100). I even tried to add the Declare but for some odd reason it keep advancing above where I type it. I appreciate any help.

    Thank you

  46. Mukesh

    Hi Anand,
    I need to show the data for only 4 years.
    Could you please tell me how to slow down the motion further ?
    I tried increasing the value of the number in Sleep (), but its not making a difference

  47. Merry Turnip

    THANK YOU SO MUCH, FOR YOUR TUTORIAL, I HAVE TRIED IT AND IT WORKS.
    THANK YOU SO MUCH.
    By the way, when I change Years to months, however, it doesn’t work. Could you please teach me how to change year to months?

    THANK AGAIN
    REGARDS

  48. maanu

    hi anand, i want to build a bubble chart for my applications globally with four variables, application investment strategy meaning invest, maintain or disinvest, cost, location, and functional match. i want to handle location with color as there are only 8 locations, and cost with size. i want app investment strategy as numbers (1,2 ,3) on y axis and functional match with five categories (0-20 21-40,41-60,61-80,81-100) on x axis. can we use your technique to achieve this ?

  49. Ganeshan Nadarajan

    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.

  50. isspenguin

    Hello – How do I create “trails” that trace the path of individual bubbles in Excel, like they do in Google motion charts? Please feel free to email me your response at rdivya09@gmail.com. Thanks!

  52. Michael

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

  54. Max

    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?

  55. Mike

    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.

  56. Diana

    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….

  59. Loveleen

    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

  60. Nathalie

    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)?

  61. Durga

    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.

  64. Des Klass

    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)

  65. Ye

    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

  67. Niall

    HI There – really love the motion chart work here – I’ve been using it for a while – but I recently went to O365 and the graphs I made don’t work anymore – any ideas?

    Many thanks,

    Niall

  69. Rochny

    I can’t get the code to work in 2016 and the sleep (100) highlights.
    Can get help, as i am not a programmer.
    regards
    Rochny

Leave a Comment

Your email address will not be published. Required fields are marked *