How to Create Animated Charts in Excel

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 23, 2023

This tutorial will demonstrate how to create animated charts in all versions of Excel: 2007, 2010, 2013, 2016, and 2019.

Excel Animated Charts – Free Template Download

Download our free Animated Chart Template for Excel.

Download Now

An animated Excel chart that draws itself onscreen in front of the very eyes of your audience is a powerful, attention-grabbing way to put your data in motion.

In contrast to static graphs, animated charts provide additional context to your data and helps identify emerging patterns. As an example, take a look at the animated line chart with markers shown below that demonstrates the GDP of four countries (the USA, China, Brazil, and Turkey) over the past few decades.

While the static counterpart would look like nothing special or new, the animated effect makes it possible for the chart to tell the story for you, bringing life to the motionless GDP numbers.

Animated charts in Excel free template

In this step-by-step, newbie-friendly tutorial, you will learn how to do the same thing with your data—even if you are just making your first steps in Excel.

Getting Started

Consider the following data table containing the GDP figures for each country:

Animated charts original data

To animate the chart, we are going to use a simple VBA Macro that will smoothly plot the values on the graph.

Note: When adding VBA code to your workbook, make sure to save your workbook in .xlsm format (Microsoft Excel Macro-Enabled Worksheet) to enable macros.

Now, let’s roll up our sleeves and get down to work.

Short on time? Download our free Animated Chart Template for Excel.

Download Now

Step #1: Set up the helper columns.

To start off with, expand the data table with additional helper columns where the actual values will be gradually copied into, creating the animation effect.

Copy the headers of the columns containing the GDP numbers (B2:E2) into the corresponding cells next to the data table (F2:I2).

The cell range highlighted in light red (F3:I13) defines the place where we will store the VBA macro output.

Additionally, add decimal separators for the highlighted cell range (Home > Number > Comma Style).

Set up the helper columns for the macro output

Step #2: Plot an empty chart.

Once you have allocated some space for the helper columns, build an empty 2-D chart using the columns (F2:I13) as its source data:

  1. Highlight any empty cell.
  2. Switch to the Insert tab.
  3. Click “Insert Line or Area Chart.
  4. Choose “Line with Markers.

Create an empty column chart

Having done that, we now must link the empty chart to the cells in the helper columns (F:I). Right-click on the empty plot and click “Select Data.

Open the Select Data dialog box

In the Select Data Source dialog box, under “Legend Entries (Series),” hit the “Add” button.

Hit the "Add" button

When the Edit Series dialog box springs up, create four new data series based on the helper columns (F:I):

  1. For “Series name,” specify the header row cell of column USA (F2).
  2. For “Series values,” select the corresponding empty cell range (F3:F13).

Repeat the same process for the remaining three columns.

Set up new data series

Once you have your data series in place, import the horizontal axis labels to the chart.

To do that, under “Horizontal (Category) Axis Labels,” click the “Edit” button.

Hit the "Edit" button

In the Axis Labels dialog box, under “Axis label range,” highlight the axis values (A3:A13).

Highlight the axis values

Here’s a pro tip: If you regularly add or remove items from the data table, set up dynamic chart ranges to avoid the hassle of having to tweak the source code every time that happens.

To top it off, make the future line graph even more informative by adding the chart title and legend:

  1. Click the chart area.
  2. Go to the Design tab.
  3. Select “Add Chart Elements.
  4. Add the chart title (Chart Title > Above Chart).
  5. Add the chart legend (Legend > Bottom).

Add the chart title and chart legend

Step #3: Program the VBA to create the animated effect.

Once the chart’s source data has been set up the right way, next comes the hard part—writing the VBA macro that will do all the dirty work for you in just one click.

Before we dive into the nitty-gritty, make sure you have the Developer tab displayed in the Ribbon. If it is disabled, right-click on any empty space in the Ribbon and pick “Customize the Ribbon” from the menu that appears.

Click "Customize the Ribbon"

In the Excel Options dialog box, check the “Developer” box and click “OK.

Access the Developer tab

Having done that, it’s time to release the Kraken of Excel, the feature that pushes the limits of what’s possible in the world of spreadsheets. It’s time to unleash the power of the VBA.

First, open the VBA editor:

  1. Navigate to the Developer tab.
  2. Click the “Visual Basic” button.

Open the VBA editor

In the editor, select the Insert tab and choose “Module.

Add a new module to the workbook

Once there, copy the following macro into the code window:

Sub Animated_Chart()

    'Declare Variables
    Const StartRow As Long = 2
    Dim LastRow As Long
    Dim RowNumber As Long

    'Get Last Row of Data
    LastRow = Range("A" & StartRow).End(xlDown).Row

    'Clear Chart & Display Blank Chart
    Range("F" & StartRow, "I" & LastRow).ClearContents
    DoEvents
    Application.Wait (Now + TimeValue("00:00:1"))

    'Step Through Each Chart Period
    For RowNumber = StartRow To LastRow
        DoEvents
        Range("F" & RowNumber, "I" & RowNumber).Value = Range("B" & RowNumber, "E" & RowNumber).Value
        Application.Wait (Now + TimeValue("00:00:1"))
        DoEvents
    Next RowNumber

End Sub

At first glance, the code may appear daunting for VBA newbies, but in reality, it takes just a few simple steps to adapt the code for your needs.

Basically, the code can be broken down into four sections as shown on the screenshot below. The green rectangles represent the segments of the code that must be tailored to your data—while the rest should remain unchanged.

Animated charts VBA code

Let’s zoom in on the parts you need to fine-tune. For your convenience, take another look at the data table and follow my footsteps:

The underlying chart data that drives the animated chart

Declare Variables: This section introduces new variables for the VBA to work with. In our case, the constant labeled as “StartRow” helps the VBA figure out where the data table begins (row 2). Therefore, the constant value should correspond to the row where your data starts.

Const StartRow As Long = 2

Get Last Row of Data: This line of code tells the VBA to analyze the data table and define where the data table ends (row 13) so that it can later zoom in only on the values within the specified cell range while leaving out the rest of the worksheet.

To pull it off, specify the first column (“A”) where the data table starts for the VBA to find the last row in that column that contains a non-empty cell (column A).

LastRow = Range("A" & StartRow).End(xlDown).Row

Clear Chart & Display Blank Chart: This section is responsible for erasing the values in the helper columns (F:I) each time you run the macro.

That way, you can repeat the same animated effect over and over again without having to clean up the worksheet cells on your own. To adjust, specify the first and last helper columns in your data table (“F” and “I”).

Range("F" & StartRow, "I" & LastRow).ClearContents

Step Through Each Chart Period: This is where all the magic happens. Having picked the cell range, the VBA goes row by row and fills the helper columns with the corresponding actual values at one-second intervals, effectively creating the animated effect.

To pull it off, you only need to change this line of code for the VBA to copy the values into the helper columns:

Range("F" & RowNumber, "I" & RowNumber).Value = Range("B" & RowNumber, "E" & RowNumber).Value

The first part of the code (Range(“F” & RowNumber, “I” & RowNumber).Value) grabs all the helper columns in the data table (F:I) while the second part of the equation (Range(“B” & RowNumber, “E” & RowNumber).Value) is responsible for importing the actual values into them.

With all that in mind, the “F” and “I” values characterize the first and last helper columns (columns F and I). By the same token, “B” and “E” stand for the first and last columns that contain the actual GDP numbers (columns B and E).

Once you made it through all of that, click the floppy disk icon to save the VBA code and close the editor.

Save the VBA macro

Step #4: Create the macro button.

To tie together the worksheet data and the newly-created macro, set up a button for executing the VBA code.

  1. Go to the Developer tab.
  2. Click the “Insert” button.
  3. Under “Form Controls,” select “Button (Form Control).

Create the macro button

Click where you would like to place the button (preferably near the chart title). At that point, the Assign Macro dialog box will appear. Select the VBA macro you just created (Animated_Chart) and click “OK.

Assign the macro to the button

As a final adjustment, change the button text (double-click button text and rename). If necessary, move the button into position where you want it.

Now, click the button and watch how the VBA smoothly fills the empty plot with the actual values—and the beauty of this method is that you can change the underlying chart type in just a few clicks without having to jump through all the hoops again!

How to create anymated charts in Excel

So that’s how it’s done. Animating your Excel charts may be a great way to give a unique perspective on your data that you could have otherwise overlooked.

Obviously, it may take some time to really understand the logic behind the VBA code. But since the same code can be repeatedly reused for different types of data and charts, it’s well worth the effort to give it a try. Once you make it past the short learning curve, the world is your oyster.

Download Excel Animated Chart Template

Download our free Animated Chart Template for Excel.

Download Now

AI Formula Generator

Try for Free