Recently I’ve shared several Excel animated charts that have been well received. These have been largely entertainment focused applications. Today I will show you how to apply animation to normal business charts.
This animated GIF shows the business chart in question which is available for download at the end of this post. Last week one of Excel Hero’s readers, Tom Quist, sent me a spreadsheet where he had made an outstanding first effort at the animation of a typical column chart. He wanted to animate between several records – let’s call them divisions.
His technique worked, but was a little slow. So I showed him how I would do it and I believe he was enthusiastic with the results.
There are some points to note. If you add a shadow to the chart or (God forbid) make the series 3D, the animation will slow down significantly. Ditto for a background image to either the Plot Area or the Chart Area. The animation speed is heavily dependent on the number of data points in the chart. After a few dozen points, animation becomes dicey in Excel 2007. Making the series partially transparent will also slow it down, but only slightly (maybe not even perceptibly given the number of points in the chart). In some circumstances the transparency effect may be worth the tiny penalty.
The technique that I shared with Tom that day was to do a straight linear interpolation between the point values of one division to the next. This works well. I shared with him that it was also possible to implement EASING so the animation has a soft start and end. I did not have time to give him an example, but I hinted at how to do it. The animated business chart that I’m sharing with you today includes the option for easing, and you can see it demonstrated in the GIF loop above.
The example also moves the calculations for the animation off of the sheet and into named formulas. To me this makes sense as there is no reason to clutter the sheet with the calculations required to morph one division into another.
The chart allows you to switch between four divisions, change chart type, enable animation, and enable easing. You can also set the number of discreet steps to include in the morphing transition between divisions, which will be useful if you run the workbook on older versions of Excel. The older charting engine is so much quicker than Excel 2007’s that you will want to increase the number of animation steps just to see it!
I’ve heard that Excel 2010 is supposed to improve charting speed. I’d love to hear how this performs on that platform.
This workbook demonstrates interactive dynamic charting, animated transitions including easing, and charting from named formulas. VBA is used only to loop through the number of animation steps that you specify, and to change the chart type.
An animated transition can add a certain amount of flare to your dashboard, but be careful not to overdo it. If used sparingly it can add a Flash-like level of interest, but remember that in and of itself, it does not add to the information the dashboard conveys.
I recommend studying the named formulas (CTRL-F3) and the VBA (ALT-F11) in the workbook. Nothing is protected and there are no hidden sheets. The knowledge gained can be used for much more than animation.
Here’s the animated business chart.
And here’s a list of other animated charts on Excel Hero: