How to animate an Excel chart

This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally between values which gives it a smoother appearance.

The drop-down list lets the Excel user select an item. An event macro checks if there is a new drop-down list value. It then compares old and new series values so it can calculate new values, in small steps.

Build a drop-down list

A drop-down list allows the Excel user to pick a value from a predefined list. It works like this, press with left mouse button on with the left mouse button on the arrow next to the cell, see the animated image above.

The drop-down list expands and shows items in a list, press with left mouse button on one of the values to select it. This allows you to control which values the Excel user can enter. An error message appears if an invalid value is entered.

The drop-down list is one of many tools you can use to validate data, however, there is a couple of things you need to know about the regular drop-down list.

It can easily be overwritten, simply copy another cell and paste it on a cell that contains a drop-down list and it is gone.
You can't easily spot regular drop-down lists, you need to select the cell that contains a drop-down list to see it. I recommend that you format the cell to make it easier to find the drop-down list.

Here are the steps to create a drop-down list:

Select cell C12.
Go to tab "Data" on the ribbon.
Press with mouse on "Data Validation button.
Select tab "Settings".
Select "List" in the drop down list "Allow:".
Type North, East, South, West in Source field.
Press with left mouse button on OK button.

Formula

An animated column chart formula 2

The formula in cell A16 verifies that the selected value in cell C12 exists in cell range A19:A22. The value in cell A16 is then used as the title in the column chart.

Formula in cell A16

=INDEX(A19:A22,MATCH(C12,A19:A22,0))

Explaining formula in cell A16

An animated column chart evaluate formula

I recommend using the "Evaluate Formula" tool to troubleshoot and understand Excel formulas. Press with left mouse button on the cell you want to debug.

Go to tab "Formulas" on the ribbon. Press with left mouse button on the "Evaluate Formula" button located on the ribbon. A dialog box appears, see image above.

The Evaluate Formula tool lets you see the formula calculations in smaller steps, simply press with left mouse button on the "Evaluate" button to move to the next step.

The underlined expression is what is to be evaluated and the italicized expression is the most recent evaluated result. Press with left mouse button on the Close button to dismiss the dialog box.

Step 1 - Find relative position

The MATCH function returns a number representing the position of a given value in a cell range.

MATCH(C12,A19:A22,0)

becomes

MATCH("South",A19:A22,0)

becomes

MATCH("South",{"North";"East";"South";"West"},0)

and returns 3. "Item "South" is the third value in cell range A19:A22.

Step 2 - Return value

The INDEX function returns a value from a cell range based on a row and column number, the column number is optional.

INDEX(A19:A22,MATCH(C12,A19:A22,0))

becomes

INDEX(A19:A22, 3)

becomes

INDEX({"North";"East";"South";"West"}, 3)

and returns "South".

Insert column chart

An animated column chart insert column chart

Select cell range A16:E16.
Go to tab "Insert" on the ribbon.
Press with mouse on the "Column charts" button.
A pop-up menu appears, press with left mouse button on the "Clustered column" button. See image above.
Create a column chart

Setting up the chart

Press with right mouse button on on column chart and press with left mouse button on "Select Data..."
Press with mouse on "Edit" button
Change series name to A16 and series values to B16:E16 (See picture below)
Press with left mouse button on the OK button.
Press with left mouse button on the OK button again.

Event Code

The event code calculates the steps needed to to make the chart animated, it returns new values in cell range B16:E16 until they match the selected item's values.

'Event code
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variables and declare data types
Dim i As Single, j As Single, x As Integer

'Check if changed cell is C12
If Target.Address = "$C$12" Then

    'Save cell values in cell range B16:E16 to variable cht
    cht = Range("B16:E16").Value

    'Enable error handling
    On Error Resume Next

        'Calculate relative position of value in cell C12 in cell range A19:A22 and save to variable x
        x = WorksheetFunction.Match(Range("C12"), Range("A19:A22"), 0) - 1

        'Ftop event code if an error has occurred 
        If Err > 0 Then Exit Sub

    'Disable error handling
    On Error GoTo 0

    'Save new values to variable Nval based on variable x
    Nval = Range(Cells(19 + x, "B"), Cells(19 + x, "E")).Value

    'Iterate four times line between for and next
    For i = 1 To 4

        'Calculate incremental value
        Nval(1, i) = (Nval(1, i) - cht(1, i)) / 10
    Next i

    'Iterate ten times lines between for and next
    For i = 1 To 10

        'Iterate four times line between for and next, this is a nested for next statement
        For j = 1 To 4

            'Add incremental value array variable cht
            cht(1, j) = cht(1, j) + Nval(1, j)
        Next j

        'Save values in array variable cht to cell range B16:E16
        Range("B16:E16") = cht

        'Update worksheet
        DoEvents

        'A second DoEvents is required if you are using Excel 365
        DoEvents
    Next i
End If
End Sub

Where to put the code?

An animated column chart WHERE TO PUT THE EVENT CODE

Press with right mouse button on on the worksheet tab, a pop-up menu appears.
Press with left mouse button on "View code", this opens the Visual Basic Editor and takes you to the worksheet module.
Copy VBA code above.
Press with left mouse button on in the code window.
Paste code to window, see the second image above.

Note, save your workbook with file extension *.xlsm to attach the code to the workbook. This will save the code as well when you the next time save the workbook.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form