How to Animate Charts in Excel with Timed Macros

 

Chart animation in Excel brings data to life by showing changes over time, making presentations more engaging, and helping viewers understand trends and patterns. Animated charts capture attention, tell compelling data stories, and make trends easy to see. Excel doesn’t have a built-in chart animation feature; you can achieve it by using timed macros with simple, timed delays.

In this tutorial, we will show how to animate charts in Excel with timed macros.

What is a Timed Macro?

A timed macro uses a built-in delay or pauses between its actions. This macro adds one data point to your chart, pauses for a fraction of a second, and then adds the next, repeating until the chart is fully revealed. This sequence gives the appearance of animation!

Animated charts are great for:

• Presentations: Reveal information step by step, keeping your audience engaged.
• Reports: Highlight data growth, trends, or comparisons in a memorable way.
• Dashboards: Add dynamic effects that update as data changes.

Step 1: Prepare Your Data and Chart

Let’s consider a monthly sales dataset to show the animation of the chart.

• Select the cell range (e.g., A1:B7).
• Go to the Insert tab >> from Charts >> select Column Chart (or your preferred chart type).

• Place the chart anywhere on your sheet and notice the Chart number.

Step 2: Open the VBA Editor

• Go to the Developer tab >> select Visual Basic (or press ALT + F11).
• Click the Insert menu >> select Module.

Note: If you don’t see the Developer tab in Excel, enable it. Go to the File >> Options >> Customize Ribbon >> tick Developer >> OK.

Step 3: Add the Animation Macro (Using a Timed Macro)

Let’s add a timed macro that runs actions with a short pause between each step, creating a smooth animation effect.

• Insert the following VBA code into the newly created module.

VBA Code:

Sub AnimateChart()
    Dim i As Integer
    Dim ChartData As Range
    Dim ChartSeries As Series
    
    Set ChartData = Sheets("Dataset").Range("B2:B7")
    Set ChartSeries = Sheets("Dataset").ChartObjects(1).Chart.SeriesCollection(1)
    
    ' Clear initial data
    ChartSeries.Values = ""
    
    For i = 1 To ChartData.Rows.Count
        ChartSeries.Values = Sheets("Sheet1").Range("B2").Resize(i, 1)
        
        ' Wait using DoEvents for smoother update
        Pause 0.5 ' Pause half second
        
        DoEvents ' Keep Excel responsive
    Next i
End Sub

'Custom Pause function using Timer
Sub Pause(seconds As Single)
    Dim start As Single
    start = Timer
    Do While Timer < start + seconds
        DoEvents
    Loop
End Sub

Explanation:

• This code clears the chart data first.
• Adds one more data point to the chart with each loop.
• This code uses a “timed macro” technique: after each update, it pauses briefly using the Pause function before continuing to the next data point.
• This delay is what animates the chart, letting you see the data build up step by step, rather than appearing instantly.
• It uses DoEvents to keep Excel responsive and update the display.

Step 4: Run the Animation

• Save and close the VBA editor to return to Excel.
• Go to the Developer tab >> select Macros.
• In the Macros dialog box;
  • Select AnimateChart.
  • Click Run.

Add a Button to Run the Animation:

• Go to the Developer tab >> select Insert >> select Button (Form Control).
• Draw the button on your sheet.
• Assign the AnimateChart macro to it for easy replays!
• Click OK.

• Change the button name to Animate Chart.

• Click the Animate Chart button.
• Watch your chart build, point by point, animated right in Excel.

Step 5: Customization Tips

Adjust Animation Speed:

• Change Pause 0.5 to a smaller or larger value:
  • Pause 0.2 for fast animation.
  • Pause 1 for a slower effect.

Reference the Correct Chart:

• If you have multiple charts, find your chart number:
  • Click your chart and look at the Name Box (left of the formula bar).
    If it says, “Chart 2,” then use ChartObjects(2) in the code.
  • Or, use the chart’s name:

Set ChartSeries = Sheets("Sheet1").ChartObjects("Chart 2").Chart.SeriesCollection(1)

Troubleshooting Tips

• Macros not running: Enable macros:
  • File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable all macros.
• Chart not updating: Make sure you’re referring to the correct chart object and sheet name.
• Excel hangs or freezes: Always use the Pause function with DoEvents, not Application.Wait.

Conclusion

By following the above steps, you can animate charts in Excel with timed macros. Animated charts in Excel can transform static data into compelling visual stories. Experiment with timing, chart types, and datasets to create the perfect animation for you. With practice, you’ll be creating sophisticated animated dashboards that bring your data to life.