Author: Oscar Cronquist Article last updated on September 15, 2020

An animated column 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

animated chart2

A drop-down list allows the Excel user to pick a value from a predefined list. It works like this, click 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, click 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. Click 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.
    animated chart - drop down list
  7. Click OK button.

Back to top

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))

Back to top

Explaining formula in cell A16

An animated column chart evaluate formula

I recommend using the "Evaluate Formula" tool to troubleshoot and understand Excel formulas. Click the cell you want to debug.

Go to tab "Formulas" on the ribbon. Click 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 click 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. Click 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

An animated column chart insert column chart

  1. Select cell range A16:E16.
  2. Go to tab "Insert" on the ribbon.
  3. Click on the "Column charts" button.
    An animated column chart insert column chart1
  4. A pop-up menu appears, click on the "Clustered column" button. See image above.
  5. Create a column chart

Back to top

Setting up the chart

  1. Right click on column chart and click on "Select Data..."
    animated chart - select data source
  2. Click on "Edit" button
  3. Change series name to A16 and series values to B16:E16 (See picture below)
    animated chart - add series
  4. Click the OK button.
  5. Click 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?

An animated column chart WHERE TO PUT THE EVENT CODE

  1. Right-click on the worksheet tab, a pop-up menu appears.
    An animated column chart worksheet module
  2. Click "View code", this opens the Visual Basic Editor and takes you to the worksheet module.
  3. Copy VBA code above.
  4. Click in the code window.
  5. 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.

Back to top


Download Excel file


animated-chart.xlsm

Back to top

Recommended reading

Creating Animated Charts

Animation, Interaction and Dynamic Excel Charts

Animating Excel Charts