Do you ever wanted to show development of some process over time?
Well I did, but unfortunately there is no built in functionality in Excel that would enable us to do so in elegant way… However what cannot be achieved by a little bit of VBA code? Today I am gonna present the basics behind two simple ways to animate a chart in Excel.
First one combine formulas and VBA. The second rely on VBA alone. In both I assume that chart was already created and the maximum, minimum and major units were set to constant for all axes. In other words options in ‘Format Axis’ menu were set as shown below.
These conditions guarantee that the chart will not start looking funny when animated (after you test the presented cases and you want to experiment set maximum scale values to ‘Automatic’ and see what happens).
The chart I use to present both solutions is a simple scatter plot depicting values of logarithm function from 1 to 100. This is pretty didactic example, but the methods can be used with other chart types as well.
Formulas and VBA Solution
Animated chart in Excel can be created using formulas thanks to simple, yet powerful, chart options set known as ‘Hidden and Empty Cell Settings’. This menu can be accessed when selecting a data source and allows to not render data points that are either empty or not applicable. All you need to to is to select ‘Show empty cells as: Gaps’.
Next you need to create new data series which equals to:
- NA() when your time axis is greater than ‘counter’
- the original when your time axis is less than ‘counter’
The exact formula for that is shown on the screenshot below. In this example the ‘counter’ represents current time and is used to determine which data points should be visible.
The last thing to do is to insert a very simple VBA snippet in the workbook that contain the chart. The purpose behind these four lines of code is to automate changing the counter and force the Excel Application to fire rendering. This actually animate the chart.
Sub Animate1() For i = 1 To 100 Cells(3, 2).Value = i DoEvents Next i End Sub
Please note that ‘DoEvents’ line is very important. Without it Excel won’t repaint the chart, so you will not see the effect until the procedure finish. In other words the result won’t be an animation but an appearance of the chart.
Cons: rely on workbook structure
When the cons overweight the pros it’s time to move to a little bit more advanced methods.
Below you can see another short VBA subroutine that achieves the same results as previously described solution. However the method is a little bit different. This time we rely on a loop that assign one more value to chart serie with each iteration.
Sub Animate2() Dim rng As Range Dim ser As Series Set ser = ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(1) For i = 1 To 100 Set rng = ActiveSheet.Range(Cells(2, 3), Cells(2, 3 + i)) ser.Values = rng DoEvents Next i End Sub
In this way there is no counter changing on the worksheet and the user is not bothered with visible recalculation of data series used in the chart.
Pros: does not rely on workbook structure (so much)
Cons: somewhat more complicated
Scene. Lights. Camera. Action!
Well that’s more or less everything for today. You can see for yourself effects of both methods in Excel Animated Chart Template or watch the video below to get a quick grasp how it works.
Next time I am going to discuss the promised method for installation and update management of Excel Add Ins. Unitl then stay tuned!