How to make animated graphs in Excel
You can create the illusion of animation in Excel by using a series of static charts and then utilizing macros or VBA (Visual Basic for Applications) to cycle through these charts at a fast rate, creating a basic animation effect. Here’s a simplified step-by-step guide on how to create such animated graphs in Excel:
Set up a helper column for each series of data that you want to animate. For example, if you have monthly sales data for three years, you can create three helper columns next to your original data and copy the headers. These columns will store the values that will be gradually plotted on the chart by a VBA macro.
Create an empty line chart with markers using the helper columns as the source data. To do this, select any empty cell, go to the Insert tab, click on Insert Line or Area Chart, and choose Line with Markers.
Add a VBA code to your workbook that will copy the values from the original data columns to the helper columns one by one, creating the animation effect. To do this, press Alt + F11 to open the Visual Basic Editor, insert a new module, and paste the following code:
Sub AnimateChart()
Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim ws As Worksheet
Dim cht As ChartObject
Set ws = ActiveSheet
Set cht = ws.ChartObjects("Chart 1") 'change the chart name as needed
LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 'change the column letter as needed
LastCol = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column 'change the row number as needed
Application.ScreenUpdating = False
For i = 5 To LastRow 'change the starting row as needed
For j = 6 To LastCol 'change the starting column as needed
ws.Cells(i, j).Value = ws.Cells(i, j - 4).Value 'change the offset as needed
cht.Chart.Refresh
Application.Wait (Now + TimeValue("0:00:01")) 'change the time interval as needed
Next j
Next i
Application.ScreenUpdating = True
End Sub
Add a button to your worksheet that will run the macro and start the animation. To do this, go to the Developer tab, click on Insert, and choose a Form Control button. Draw the button on your worksheet and assign the AnimateChart macro to it. You can also change the button text as you like.
Now you have an animated graph in Excel that you can play by clicking on the button. You can also format the chart and adjust the code as you need.
