Animated charts in Excel
Watch Hans Rosling’s TED Talks on debunking third world myths and new insights on poverty and ask yourself: could I do this with my own data?
Yes. Google has a gadget called MotionChart that lets you do this.
Now, you could put this up on your web page, but that’s not quite useful when presenting to a client. (It is shocking, but there are many practical problems getting an Internet connection at a client site. The room doesn’t have a connection. The cable isn’t long enough. You can’t access the LAN. Their proxy requires authentication. The connection is too slow. Whatever.)
So you need this in Excel. Let me explain a variant of the technique I described earlier.
Let’s start by creating a simple bubble chart.
For each item in a bubble chart, you need 3 pieces of data: the X-axis, Y-axis and size. This graph shows three items A, B and C in one year: 2001. To animate this, you need data for more years, so let’s create that.
The first 3 rows contain the same data as before, except that I’ve added a "Year" column and a "Key" column (which is just a concatenation of the Year and the Item). The data now goes on for many more years.
Now we need to create a scroll bar that can be used to change the year. So add a scroll bar below the bubble chart…
… and right click the scroll bar and go to Format Control. Now, select the cell link to some cell ($H$1 in this case). Now, if you move the scroll bar, the cell value will change.
All you need to do is to now change the source data for the chart based on the year. From the table on the left, VLOOKUP the year + item, and put this into the table on the right. When the year in the cell H1 changes, the data updates itself. So now, as you move the scroll bar, cell H1 changes, then so does the data and hence the graph.
This is what the animation looks like.
And here’s the Excel file.
cool tip. I will use it.
Does this method work only in MS Excel 2007? I am using MS Excel 2003 and I was not able to get the “Control” tab in the properties of the scroll bar.
@Sundar: It works on Excel 2003 as well, except I’m not sure what the menu shortcuts are.
After some googling I found out the problem: There are scroll bar object in both “Control toolbox” and “Forms” tool bars. The scroll bar in the “Control toolbox” does not have the “Control” tab in its properties. The scroll bar in the “Forms” does have the “Control” tab in its properties. I was adding the scroll bar object from the Control toolbox toolbar. That’s the reason I was not able to get it working earlier.
I don’t understand what the “size” could be in the data.. can u help?
@Sridhar: The size is the radius of the circles. You’ll notice that this changes in the animation as well. Initially, the circles are of the same size, but over time, they become bigger or smaller.
size can be the annual sales value of a product
x can be year
y growth
Hi. Very useful way of doing this – thanks!
Is there any way to automate the scroll bar so that I can click just one button and the graph updates itself automatically?
Thanks
very nice and simple
Hi Anand,
This is very nice, I landed on this page googling for something I wanted for in xl charting.
Can you help ? I am trying to model a “System A”. I have 4 identified dimensions D1, D2, D3, D4 for it, each on a scale of say, 1 to 400.
I wanted to represent them in one composite chart to show, this is how the “System A” looks like. Is there anyway out ?
Thanks
I think it would depend on the dimensions, SG. The best way of modelling up
to 4 dimensions I’ve seen are using Hans Rosling’s charts at gapminder.org.
But you’d need the 4 dimensions to be mappable to X, Y, size and time.
Thanks Anand for the immediate response. A bit more to explain myself.
The things that I would like to depict are these.
To model the system A, I have 4 dimensions each with varying weights. The response score for each dimension is expressed as %. In bubble chart terms- Say, one of the dimension is represented as a bubble, then the outer circle diameter denote the Relative weight of the dimension and a circle inside denote the actual score.
Roughly, I visualize to represent the Model as a stack for 4 bubbles (dimmensions)one on top of other. something like a 3D color model
In the example, that you referred.. Size and time are something which is common to X and Y..
Thanks
Ganesh S
For this particular example, Ganesh, you’re probably better of not
using the scheme I described. It’s probably better to reduce the data
to fewer dimensions (e.g. in pairs: dimensions (A,B), (A,C), (B,C),
etc.). Sorry, without a sense of what the data is about, I’m not sure
I can think of a good way of doing this.
Hi Anand
As I told you earlier, I came up with something like the above image. 5 processes each measured on 4 Dimensions.
I was trying to get each of these respective xl charts one on top of the other on Z-axis which could make the representation in 3D.. But I could not.
Regards
- S Ganesh
Hi there,
I’ve created a little Excel add-in that does a similar thing, but without the need for adding controls to your Excel worksheet. The user simply creates a table of data, creates a chart from the top row of the table and then uses the add-in’s menu to run the animation.
It is free – for now at least
– and can be downloaded from my website – http://www.animatexl.com.
I’d be very interested to know what anyone thinks of it.
Regards
Phil.