Gapminder For Excel II
by Jon Peltier
Tuesday, December 9th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
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
- Clustered Bars as an Alternative to Stacked Bars or Bubbles
- Interactive Multiple Line Chart
- Dynamic Chart using Pivot Table and Range Names
- Stack Columns In Order Of Size With VBA
- Rock Around The Clock
- Reforming a Health Care Reform Chart
- SPC Approach to Browser Stats
Posted: Tuesday, December 9th, 2008 under Dynamic Charts.
Comments: 11
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
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.