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:

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

Back to top

Formula

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

Back to top

Explaining formula in cell A16

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".

Back to top

Insert column chart

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

Back to top

Setting up the chart

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

Back to top

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

Back to top

Where to put the code?

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

Back to top

Get the Excel file

animated-chart.xlsm

Back to top

Recommended reading

Creating Animated Charts

Animation, Interaction and Dynamic Excel Charts

Animating Excel Charts