Gapminder For Excel II
by Jon Peltier
Tuesday, December 9th, 2008
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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.
Related Posts:
- Gapminder for Excel
- Ballistics Animation
- Moving Averages
- Interactive Multiple Line Chart
- Gas Prices – Interactive Time Series
- VBA to Filter Chart Data Range
Posted: Tuesday, December 9th, 2008 under Dynamic Charts.
Comments: 17
Comments
Comment from Matt
Time: Tuesday, December 9, 2008, 3:29 pm
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)
Comment from Anton
Time: Thursday, August 6, 2009, 5:11 pm
Do you know how to get all of this into a Powerpoint so I can present that animation? Thanks
Comment from Jon Peltier
Time: Thursday, August 6, 2009, 6:06 pm
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.
Pingback from Animeted Charts, Interaction and Dynamic Excel Charts
Time: Sunday, October 4, 2009, 4:46 pm
[...] emerging, you would need to add a complex interpolation routine to make it look better (read Jon’s post to see how a simple interpolation can be [...]
Comment from Phil
Time: Tuesday, October 13, 2009, 11:52 am
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 – http://www.animatexl.com – and I would greatly appreciate any comments from any potential users.
Regards
Phil.
Pingback from Data Visualization News» Blog Archive » Animation, Interaction and Dynamic Excel Charts
Time: Friday, November 20, 2009, 12:19 pm
[...] emerging, you would need to add a complex interpolation routine to make it look better (read Jon’s post to see how a simple interpolation can be [...]
Pingback from Hydrus 1D数据的Excel简单模拟 » 『高丁』Excel
Time: Sunday, February 7, 2010, 10:26 am
[...] Gapminder For Excel II @ PTS Bolg [...]
Pingback from Dynamic Excel Charts, Animated Charts and User Interaction
Time: Monday, February 15, 2010, 12:20 pm
[...] emerging, you would need to add a complex interpolation routine to make it look better (read Jon’s post to see how a simple interpolation can be [...]
Comment from Alan Spence
Time: Monday, July 5, 2010, 3:15 pm
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
Comment from Jon Peltier
Time: Monday, July 5, 2010, 5:34 pm
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.
Comment from Alan Spence
Time: Monday, July 5, 2010, 6:03 pm
Jon,
Thanks a lot, that was exactly what I was trying to accomplish.
Alan
Comment from Ada
Time: Monday, August 22, 2011, 2:36 am
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
Comment from Jon Peltier
Time: Tuesday, August 23, 2011, 11:52 am
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.
Comment from wynand
Time: Wednesday, April 4, 2012, 2:15 am
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
Comment from Jon Peltier
Time: Wednesday, April 4, 2012, 6:35 am
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.
Comment from wynand
Time: Wednesday, April 4, 2012, 6:48 am
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.
Comment from Jon Peltier
Time: Wednesday, April 4, 2012, 11:46 am
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.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.