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.
What's on this page
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
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
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".
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?
- 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.
Recommended reading
Animate category
This article demonstrates macros that animate bars in a chart bar. The image above shows a bar chart that animates […]
This article demonstrates how to create an animation using a line chart in Excel. The user selects a series in […]
Charts category
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
Column chart category
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
I found an interesting chart on CNN's website: Rise of the supersize rugby player It shows the average height of athletes […]
The clustered column chart allows you to graph data in vertical bars, this layout makes it easy to compare values […]
Functions in this article
More than 1300 Excel formulas
Excel categories
One Response to “How to animate an Excel chart”
Leave a Reply
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.
[…] clears all values. The "Animate" button starts the animation. Check out my other animated chart:An animated excel chart If you think this animation is too slow, see the animation […]