How to Create an Animated Chart in Excel with VBA

Step-by-step

A column chart that builds itself month by month when you click a play button. The trick: a formula reveals the data one row at a time based on a control cell, and a short macro counts that cell up.

1. Build the dynamic data with a control cell

Next to your source data (monthly values for three years), add a control cell (E3 in the example) and dynamic columns (I:K) that copy the data only up to that row. In I3:

=IF($E$3>=ROW(),A3,NA())

ROW() returns the formula’s own row number. If the control value has reached this row, the formula shows the value; otherwise it returns #N/A, which charts don’t plot. Copy the formula across and down. Tip: select part of a formula in the formula bar and press F9 to see its result.

2. Lay the years out for a continuous look

Give each year its own column, and repeat the first month of each new year at the end of the previous year’s column. That joins the years without a gap.

3. Insert and format the chart

Set the control cell to the last row (38) so everything shows, select G2:K38 and insert a Clustered Column chart. In Format Data Series, set Series Overlap to 100% and Gap Width to 50%, so the three years look like one continuous chart. Delete the legend, gridlines and title.

4. Write the macro

Go to Developer > Macros, type the name Animate, make sure it’s stored in this workbook and click Create. Paste the code from the free workbook. In outline it:

• declares the Windows Sleep function, which pauses in milliseconds (more precise than VBA’s Wait, which only does whole seconds);
• loops a counter from the first row to the last (3 to 38);
• writes the counter into the control cell on Sheet1 each time;
• sleeps briefly between steps. The smaller the delay, the faster the animation.

Use Option Explicit at the top so every variable must be declared. Save the file as an Excel Macro-Enabled Workbook (*.xlsm).

5. Add a play button

Insert a picture of a play button (Insert > Pictures > Online Pictures), right-click it and choose Assign Macro > Animate. Click it and the chart animates. If you rename the macro later, reassign it to the button.

Go further

My Excel Macros & VBA Programming for Beginners course teaches the VBA behind tricks like this from the ground up.