Create Animated Sales Charts in Excel – Step‑by‑Step Guide

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

Code Breakdown

We declare the variables first. We set the Constant as SR representing Starting Row. In our case, it is 5. The LR and RN are also variables referring to the values of the Last Row and Row Number respectively.

LR = Range(“C” & SR).End(xlDown).Row→command removes all the values of the associated column (F: H).

Range(“F” & SR, “H” & LR).ClearContents→picked the range of cells, started showing the cell values row by row and filled the blank columns of F to H.

We have set the Time delay to 1 second, which will help us show the data at a 1-second delay, which gives a dynamic vibe to the chart.

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