How to Create Animated Charts in Excel (with Easy Steps)

We have taken a dataset of monthly sales for 3 consecutive years: 2020, 2021, and 2022. We will generate a chart that changes automatically.

Step 1 – Setting up a Chart with Helper Columns

• Insert three helper “Sales” columns.

Note: You can highlight the helper columns for better visualization.

• Move to a blank cell.
• Navigate to the Insert tab.
• Under the Charts section, choose Insert Line or Area Chart and pick Stacked Line.

• Right-click on the chart and click on Select Data.

• The Select Data Source dialog box pops out.
• Choose Add from the Legend Entries (Series).

• You will see the Edit Series window.
• In the Series Name box, select Sales (2020), and in the Series values box, select the data range $F$5:$F$16.
• Press OK.

• Repeat to select all the “Sales” columns and the respective helper columns.

• Moreover, in the Horizontal (Category) Axis Labels, move to Edit.

• Subsequently, the Axis Labels dialog box pop out. Select the data range from $B$5:$B$16. Click OK.

Finally, hit OK in the Select Data Source window.

Read More: How to Create Animated Bar Chart Race in Excel

Step 2 – Formatting the Chart

• Create the Chart Title, the Axis Titles, and the Legend at the bottom. These are available from Chart Elements.

Your chart will look something similar to the image below.

Step 3 – Using VBA Code

• Go to the Developer tab and choose Visual Basic.

• Choose the Insert tab and select Module.

• In Module 1, you will see the General box.
• Put the following VBA code there and save it.

Sub AnimationChart()
    Const SR As Long = 5
    Dim LR As Long
    Dim RN As Long
    LR = Range("C" & SR).End(xlDown).Row
    Range("F" & SR, "H" & LR).ClearContents
    DoEvents
    Application.Wait (Now + TimeValue("00:00:1"))
    For RN = SR To LR
        DoEvents
        Range("F" & RN, "H" & RN).Value = Range("C" & RN, "E" & RN).Value
        Application.Wait (Now + TimeValue("00:00:1"))
        DoEvents
    Next RN
End Sub

Step 4 – Entering a Button to Generate the Animation

• Add a button using Form Controls in your worksheet.
• Rename it to Animation.
• Right-click on the Animation button and choose Assign Macro from the Context Menu.

• The Assign Macro dialog box appears.
• Select the Macro name as AnimationChart.
• Press OK.

• The animated chart is ready. Click on the Animation button.

Practice Section

We have provided a practice section on each sheet on the right side so you can practice the steps.

Download the Practice Workbook

Related Articles

• How to Animate Text in Excel
• VBA Animation in UserForm