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

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.

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. Vinoth says:

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 says:

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.

3. David says:

Is there a way to slow it down?

4. S Anand says:

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`
5. David says:

Thanks! That did the trick! Great Blog!

6. Zoheb says:

Hi I had a question, if I wanted the counter by 3, how would i do that?

Thanks
-Zoheb

7. Zoheb says:

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 says:

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 says:

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

10. Venkat says:

Thanks Anand. Very useful to represent often boring data like time series analysis.

11. Firman says:

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

12. Reginald Vaz says:

Extremely useful…thanks a lot for sharing

everything redy and beautiful…

can we add drill down to it

Thank you Anand

14. Mike says:

Great Tool. I am also looking for a way to pause the animation. Any ideas?

15. Pete says:

Is there a way to embed the animation in excel?

16. Sabnish says:

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

17. Sunny says:

Is there any way I can export these motion charts into microsoft powerpoint?

18. S Anand says:

Don’t think we can export these motion charts into Powerpoint, I’m afraid.

19. beth says:

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 says:

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?

21. Alex says:

Having problems creating it in Excel for mac. Any ideas?

22. Jeff Koenig says:

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 says:

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 ?

24. Drew says:

Just found your post, and I can’t wait to try it out. Thanks for the tips!

25. derek says:

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.

26. Mahdi Meskin says:

Great post!
thanks.

27. NIall Tallon says:

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 says:

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

29. Kamesh.M says:

Great Post!
This can be done without Macros also…..
http://www.webanalyticsindia.com/2009-11-20/motion-chart-in-excel/

Best Regards
Kamesh

30. Scott says:

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 says:

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 says:

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 says:

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 says:

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!

35. Atisha Banjare says:

Hi!! Great stuff and really useful. Thanks

36. kalaisam says:

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 ?

37. Nal says:

hi, this is fantastic! Is it possible to put this into my powerpoint presentation?
thanks!

38. Chris says:

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 says:

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 says:

Thank you for this useful tutorial.

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

41. Kirk says:

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 says:

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?

43. Seymon says:

it is awesome, thanks

44. Gary says:

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 says:

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 says:

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 says:

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 says:

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. 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 says:

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!