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
Animation, Interaction and Dynamic Excel Charts
Built-in Charts
Combo Charts
Combined stacked area and a clustered column chartCombined chart – Column and Line on secondary axis
Combined Column and Line chart
Chart elements
Chart basics
How to create a dynamic chartRearrange data source in order to create a dynamic chart
Use slicers to quickly filter chart data
Four ways to resize a chart
How to align chart with cell grid
Group chart categories
How to add lines between stacked columns/bars [Excel charts]
Custom charts
How to build an arrow chartHow to graph a Normal Distribution
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template
Sparklines
Win/Loss Column LineHighlight chart elements
Highlight a column in a stacked column chartHighlight a group of chart bars
Highlight a data series in a line chart
Highlight a column in a stacked column chart
Highlight a bar in a chart
Interactive charts
How to filter chart dataHover with mouse cursor to change stock in a candlestick chart
How to build an interactive map in Excel
Highlight group of values in an x y scatter chart programmatically
Use drop down lists and named ranges to filter chart values
How to use mouse hover on a worksheet [VBA]
How to create an interactive Excel chart [VBA]
Change chart series by clicking on data [VBA]
Change chart data range using a Drop Down List [VBA]
How to create a dynamic chart
Animate
Line chart Excel Bar Chart Excel chartAdvanced charts
Custom data labels in a chartImprove your X Y Scatter Chart with custom data labels
Label line chart series
How to position month and year between chart tick marks
How to add horizontal line to chart
Add pictures to a chart axis
How to color chart bars based on their values
Excel chart problem: Hard to read series values
Build a stock chart with two series
Change chart axis range programmatically
Change column/bar color in charts
Hide specific columns programmatically
Dynamic stock chart
How to replace columns with pictures in a column chart
Color chart columns based on cell color
Heat map using pictures
Dynamic Gantt charts
Stock charts
Build a stock chart with two seriesDynamic stock chart
Change chart axis range programmatically
How to create a stock chart
Excel categories
One Response to “How to create a stock chart”
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.
[…] Learn how to create a stock chart in excel […]