A few months ago in Gapminder for Excel I made a simplistic mockup of Hans Rosling’s Gapminder presentation Debunking third-world myths with the best stats you’ve ever seen in Excel. It was fairly simple, with a single bubble marching across a chart, interpolating between yearly data points as a simple timer-based VBA procedure incremented.
An astute reader named Eric thought that the chart would be more effective if the moving bubble left a trail of breadcrumbs, as it were, the annual mileposts along the travels of the moving bubble. I took up the challenge, and it took less time to work up than it’s taking to write this post.
I modified the data somewhat, since it was made up anyway, so that the path traced by the moving bubble was more continuous. I also defined some names in the worksheet which contain the whole series of data from the first point up to the point where the moving bubble has reached.
Here is a summary of the names, if you’re playing along at home. The first three names were present in the first version of this chart, and were used by the VBA program in its interpolation routine. The next three names are the X and Y values and the Diameters for the bubbles. Finally, I’ve included the chart series formula for the new series.
Name Refers To FirstColumn =Data!$B$12 LastColumn =Data!$B$13 ControlColumn =Data!$B$14 AllYearsX =OFFSET(Data!$A$3,0,1,1,GapminderExcel.xls!ControlColumn-1) AllYearsY =OFFSET(AllYearsX,1,0) AllYearsD =OFFSET(AllYearsX,2,0) Chart Series =SERIES("All Years",GapminderExcel.xls!AllYearsX, GapminderExcel.xls!AllYearsY,1,GapminderExcel.xls!AllYearsD)
The workbook can be found in GapminderII.zip.
Matt says
Thanks. I saw that Google has a motion chart widget that does something like this and though it was cool. I’d prefer doing it in Excel. Now I need to find a good data set to use it on. (Not just use it because it’s cool and end up confusing the point of the data)
Anton says
Do you know how to get all of this into a Powerpoint so I can present that animation? Thanks
Jon Peltier says
Anton –
You could keep the Excel workbook as a companion file to the presentation. Or you could embed the Excel chart (actually the entire workbook) into a slide. The embedded chart will be frozen during the slideshow, but in design mode, activating the embedded chart will allow you to run the animation.
With some VBA programming, it is possible to run the animation within the slideshow using controls you’ve embedded in the slide, similar to the controls on the worksheet. I haven’t done this, so I can’t say how it’s done.
Phil says
Hi there,
I have created a small Excel add-in that seems very similar to what you have done here. My aim is to make it as straightforward as possible for the user to animate their charts – all that is required is to create a data table, create a chart from the top row of that data table and then use the add-in’s menu to run the animation.
It is freely available from my website – – and I would greatly appreciate any comments from any potential users.
Regards
Phil.
Alan Spence says
Hi there,
I was playing around with the file and wanted to introduce a second set of data so I can have two moving bubbles at once. When I put the new data series into the chart, the graphs no longer seem to clear the “breadcrumbs” and the yearly posts remain. Is there something I need to change about the code if I want to have two data sets moving at once?
Thanks for any help.
Alan
Jon Peltier says
Alan –
You have to construct the second series in the same way as the first. It has to be based on dynamic named ranges, so it shows only the years before the highlighted point.
I’ve worked up an example with two series in GapminderIIa.zip.
Alan Spence says
Jon,
Thanks a lot, that was exactly what I was trying to accomplish.
Alan
Ada says
I downloaded your gapminder for Excel. When I clicked on VB code, It shows Solver but asks for password. Can you please provide the password for solver code?
Thanks,
-Alok
Jon Peltier says
Ada –
My file does not make use of Solver.
Prompts for VB project passwords are often the cause of third party COM or .NET add-ins which do not properly release system resources. These ad-ins may not even be related to Excel, and they are definitely not related to the VB project whose password is being requested. These are a difficult thing to track down and fix.
wynand says
Jon,
Is there a way to edit the content of the chart tip for the bubbles in the sample s/sheet ?
I know the chart tip is part of tool tips, which you can edit (font and background color) under windows display propoerties, but I woulD like to edit this hovering tip to display my own value or just one of the values e.g. operating profit.
thanks
Jon Peltier says
Wynand –
You can’t modify the built-in chart tooltips to any great extent. You can turn them off, and pop up your own shapes with the desired information, using chart events to drive VBA code that handles these custom messages. Tushar Mehta has done something along these lines, but I don’t know if it’s a tutorial or just a program that handles it. It would make a great blog topic here, though, so I’ve put it onto my list. I’ll get to it a week from whenever.
wynand says
Thanks for thinking of me in the hopefully not to distant future!
Tushar’s add-in works great, however when applied to your gapminder 2, it messes up the data labels, so it seems his utility is meant for “hovering” data labels only and not for chart tips editing.
Jon Peltier says
Wynand –
Sorry I wasn’t clear. When I said “You can’t modify the built-in chart tooltips” I meant they can’t be modified at all. Tushar’s add-in does not modify the tooltips, it only turns the real ones off, and simulates custom ones. To do this it pops up a label that contains the text you want to display.
Jaoo Jon says
Hi, it’s very well done. But how did you make the Play button ? any link ?
Thanks
Jon Peltier says
Jaoo –
I used the technique in How To: Assign a Macro to a Button or Shape. This article was written for Excel 2003.
For Excel 2007-2013, the steps to insert a button are different. First you need to display the Developer tab. In 2007, click the big round Office button, then select Excel Options, and on the General tab (I think), check the box for Show Developer tab. In 2010-2013, click the File tab, select Options, select Customize Ribbon, and check the box in front of Developer.
Then on the Developer tab, click on Insert, and select the button, which is the first icon under Form Controls, then draw the button. Right click on the button and assign the appropriate macro.
Jaoo Jon says
Hi again, thanks for earlier response. I’m not sure I understand the last part of the article
“Name Refers To …”
Controlcolumn for example : when I click on B17, I see ControlColumn in red and B14 in red too. How do we set that value ? Could we also have a ControlRow ?
Jon Peltier says
Jaoo –
To define a Name, go to the Formula tab and click on Define Name; these are what I’ve listed in the text. You must enter a Name for this defined name, and a formula that it refers to. You can also indicate whether its scope (where it is relevant) is the workbook or a worksheet, and you can enter a comment.