How to Animate Excel Charts Using NA(), IF() and a Counter Cell

Here's a cool trick to get your charts animated in Excel.

The magic comes to life through a combination of NA() function, the IF() function, and a counter cell. The counter cell contains the total number of series in your chart, and the series in your chart data will be numbered from 1 to that total number.

Your raw data will not be the source data for this chart. This chart will get its source from a calculated range that brings in data based on a condition - that the numbering of the series data point is greater than the value in the counter cell.

The formula in the calculated range that brings in the source data checks if the current series numbering is greater than the counter cell number - if it is, it brings in the source data, if it is not, it returns an N/A error

The formula in the calculated range is below - this formula is filled across the data points that feed into the chart, both year and value (all cells in grey fill).

=IF($A4>$B$1,NA(),D4)

^{_{(Note the formula above uses an absolute reference for the counter cell (B1), so that it can be dragged to fill the range without moving its position, and uses a mixed reference - locked column, free row - for the series numbering cell and source data cell, so that it moves to reference the details in each row it is dragged to)}}

When you create a chart with this calculated range, you can notice that the data points disappear for the rows that have an N/A error.

From here, we are going to need the magic of VBA. We want VBA to automate the task of incrementing the value in the counter cell, step by step, from 1 to the total range, till the final number is reached. This will give the impression of a chart animation.

[video of doing the animation manually - incrementing the counter by one point till we get to the total - this is the task we want to automate]

The VBA code that gets this done is below:

Sub AnimateChart()

Dim i as Long

For i = 1 to 400 ' 400 represents the square of the total number of series (20^2) in the data - update as required

Range("B1").value = i/20 'B1 is the cell address of the counter, update as required

DoEvents

Next i

End Sub

The For-Next code loops from 1 to 400, dividing each number by 20 and returns the answer for each step into the counter cell, essentially automating the increment task. The last answer (400/20) will return the total number of series in the counter cell, finalizing the animation.

You can alter the code to make the animation faster or slower by increasing the end value for i in the For loop (400 in my example). But whatever value you select, ensure you update the divisor in the equation in line 4 (20 in my example) so that the quotient for this division ends at 20.

Example code for a slower animation:

Sub AnimateChart()

Dim i as Long

For i = 1 to 2000

Range("B1").value = i/100

DoEvents

Next i

End Sub

Example code for a faster animation:

Sub AnimateChart()

Dim i as Long

For i = 1 to 100

Range("B1").value = i/5

DoEvents

Next i

End Sub