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.
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: