Today I had a couple of hours to kill and stumbled upon an interesting flash chart at flowingdata.com
It's an animated chart that allows you to explore Average Consumer Spending in the USA by category from 1984 through 2008. I thought it would be interesting to emulate the animation in Excel and here's what I came up with:
Now mind you, I'm not sure I like the value axis changing scale on a chart, but to emulate this properly that was a requirement. It actually turned out pretty well. If you click on any of the categories, the whole chart morphs... it's pretty cool. Clicking again brings it back to normal. There's a check box at the top to turn off the animation, and I suggest you do so if you plan to edit the chart.
This is most definitely a VBA project, but interestingly most of the work is done by worksheet formulas and the VBA glues it together and animates it. One thing I learned crafting this was that adding a shadow to the Chart Area dramatically crippled the speed at which the animation takes place - so I removed the shadow!
The project uses a modified version of Jon Peltier's chart event class. You can re-color the chart categories by changing a couple of RGB values in the SetColor sub-procedure. The code there does a straight linear interpolation in RGB color space between the two provided colors for the 14 spending categories.
I think it would be time well spent to investigate how this chart works.
Here's the workbook:
If you liked this article, please share it!