Author: Oscar Cronquist Article last updated on August 31, 2018

Today I will show you how to build a basic animated line chart. The chart has a small trailing shadow effect.

animating a line chart with shadow1

How I made this chart

The chart data is in cell range B3:J8. A small vba macro calculates the difference between the current series and the new series and then divides it with 20. The result is shown in cell range C10:J10.

animated line chart

The chart data source is cell range C13:J17. The chart uses 4 lines to create the trailing shadow effect. Series 1 is the main line, the others are shadows.

Here you can see the calculations in action.

animating a line chart with shadow - calculations1

Event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$6" Then
    On Error Resume Next
        x = WorksheetFunction.Match(Range("L6"), Range("B4:B8"), 0) - 1
        If Err > 0 Then Exit Sub
    On Error GoTo 0
    For i = 1 To 8
        Range("C10:J10").Cells(i) = (Range(Cells(4 + x, "C"), Cells(4 + x, "J")).Cells(i) - _
        Range("C13:J13").Cells(i)) / 20
    Next i
    For i = 1 To 20
        Range("C14:J17") = Range("C13:J16").Value
        For j = 1 To 8
            Range("C13:J13").Cells(j) = Range("C13:J13").Cells(j) + Range("C10:J10").Cells(j)
        Next j
    Next i
    For j = 1 To 4
        Range("C14:J17") = Range("C13:J16").Value
        Range("C14:J14") = ""
    Next j
End If
End Sub

Download excel *.xlsm file

Animating a line chart.xlsm

See my other posts about chart animations:

See other interesting posts about charts: