Animated Business Chart #3

| 9 Comments | 0 TrackBacks

OK.  I need your help! 

I can't decide what is the best strategy to emulate a certain aspect of this visualization. But I'll get to that in a minute. This chart is a replication of General Electric's Our Aging World. For such a simple visualization, the project had a surprising number of challenges. Here is my work thus far.

As always, the Excel visualization is much better than this animated GIF.

This post is not about the merits of the original visualization, but rather how it was emulated using advanced Excel techniques.

I grabbed the data from the UN database. And of course it was only available in 5-year increments when broken down by gender and age bracket. So for this visualization I had to interpolate the interim years - and that is what accounts for most of the data on the data sheet. Next I had to turn each year's gender-age-bracket data into a percentage of the total for that year so that all countries and years would have a comparable relative scale.

To speed up the animation I needed to replace these tens of thousands of formulas with hard data. The charts are based on four very small ranges of formulas in columns BA and BB of the data sheet. These use the INDEX function to grab the appropriate data for the selected countries and year.

The animation is the very same technique used in the first two animated business charts.

An interesting aspect of this visualization is the creation of the legend controls to select countries and the clickbar at the bottom to select years. These are built from shapes and images and are powered by VBA.

There are some very interesting VBA and named formula twists going on here, so have a look.

Each vertical bar in the chart represents an age group for that country. For example the first bar represents the relative number of people in a country (in the selected year) who are aged 0 through 4. The second bar represents people 5 though 9. This continues for each 5-year age bracket up until the last vertical bar, which represents 100+.

In GE's original, they use the technique of mousing over the bars to show a pop-up that explains what each bar means.

I can do this, but it's clunky for all of the reasons described in my Stremgraph post. So what should I do? One idea is to allow clicking on the bars which would update a static legend box - not great but workable. I can't think of a stellar idea just now. If you have ideas, let me know, and if I really like one, I will update the chart here and give you credit for the idea.

Here is the workbook.

PS. Big announcement tomorrow. Stay tuned!

If you like my blog, please be sure to sign-up for the new Excel Hero Newsletter. Subscribers receive all of my articles AND extra Excel Hero tips.

Here's a list of other animated charts on Excel Hero:

 - Excel, A Presentation Platform (Number Spiral)
 - Lilac Chaser (Optical Illusion)
 - Stereokinetic (Optical Illusion)
 - Illusory Contours (Optical Illusion)
 - Breathing Square (Optical Illusion)
 - Enigma (Optical Illusion)
 - Two Sinusoids (Optical Illusion)
 - Perpetual Collisions (Optical Illusion)
 - Freezing Rotation (Optical Illusion)
 - Reverse Spoke Illusion (Optical Illusion)
 - Stepping Feet Radial Illusion (Optical Illusion)
 - Swimming Fish (Optical Illusion)
 - Mutually Interfering Shapes (Optical Illusion)
If you liked this article, please share it!

Your Ad Here

No TrackBacks

TrackBack URL:


More Beautiful Work
I don't know when you get the time to do actual paid work...


When working with shapes I use the below mentioned technique to make the same macro work irrespective of the shape clicked.

For Countries

Sub SelectCountry()
ChangeCountry Right(Application.Caller, 1), Mid(Application.Caller, 1, Len(Application.Caller) - 2)
End Sub

For Year

Sub SelectYear()
ChangeToYear Right(Application.Caller, 4) * 1

End Sub

Simply amazing. I really like the animated business charts, Daniel. I love all of your work, but these in particular are my favorites.

Very cool.

When I click the links my cursor goes wild flickering, but in your .gif file the cursor appears not to flicker at all. Is there some setting I'm missing to avoid that?

@d -

I just sent you a revised version of the chart. Please let me know if it cures your cursor problem.


Daniel Ferry

Daniel, that worked - changing the cursor during the animation to the NorthwestArrow makes the cursor rock steady.


you can create (pretty hacky-looking) tooltips by the simple expedient of giving your tables headers in the form of

"Males 34-39

(that's linebreak-doublequote-Males-.... use alt+enter to add the line break. the quote is to visually balance the quote at the end of the point: field)

result will look like e.g.
Series: "male_b" point: "
"Males 34-39"
value: 0.345345

What about permanently labelling the bars ? To me, it wasn't immediately obvious that the bars actually represented different age groups.
You wouldn't need to label every bar - labelling alternate bars (or even fewer) would work. The label could be really simple - just one number, for example 35+ (it doesn't need to include gender).

Hi Daniel,

could you help me understand what is easing and why is it used along with animation? i did not find any difference in Business chart 1 & 2 where you have checkboxes for both animate & easing. if i just keep the animate checked, easing unchecked, i see no difference in the transition. i have done this using large data too.

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on September 2, 2010 11:16 AM.

Excel Optical Illusions Week #28 was the previous entry in this blog.

Excel Optical Illusions Week #29 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.