Use drop down lists and named ranges to filter chart values
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows you to select which values to show on the chart. If you own Excel 2010 or a later version I highly recommend using slicers instead.
The first drop down list lets you choose which column to show on the chart based on the selected column header, the second drop down list allows you to choose a row to show on the chart based on values from an Excel defined Table.
What you will learn in this article
- Use drop down lists to filter values shown on a chart.
- Extract specific columns or rows from an Excel defined Table using a formula.
- Create a named range containing a formula that returns specific columns or rows.
- Extract columns and rows from an Excel defined Table based on drop down lists.
- Show specific values on a chart using an Excel defined Table as a data source based on selected drop down values.
How to use this worksheet
The following animated image shows you a sheet where you can select column (Region) or a row (Month) and the chart updates correspondingly. I am only using named ranges and a table to create the functionality.
The great thing with this dynamic chart is that you can easily add more rows or columns to the Excel defined table, you don't need to update the formulas every time you add or remove records.
Example,
- Select cell F14.
- Press Tab key on your keyboard.
- Selected cell is now B15 which is the first cell of the new record. This creates a new row in the Excel defined Table.
- Type values in the empty cells.
You can also simply select cell B15 and type a value, the Excel defined Table gros as soon as you press Enter.
The table automatically expands and the drop down lists and chart are instantly refreshed with the new row or column values.
How I made this worksheet
This worksheet contains a few named ranges containing formulas, an Excel define Table that contains the source data, a chart and two drop down lists that let the user filter values on the chart.
How to convert data to an Excel defined Table
- Select any cell in your data set.
- Press CTRL + T to open the Table dialog box.
- Press with left mouse button on OK button.
Named ranges
I created five named ranges that allows me to use Excel defined Tables in drop down lists, you can also use the INDIRECT function to accomplish the same thing.
Here are the steps to add a named range:
- Go to tab "Formulas" on the ribbon.
- Press with left mouse button on "Name Manager" button to open the "Name Manager" dialog box.
- Press with mouse on "New..." button to create a new named range.
- Type a name based on the names displayed below.
- Copy/Paste the corresponding formulas to the "Refers to:" field.
- Press with left mouse button on OK button.
- Press with left mouse button on "Close" button.
Month - Formula:
Named range "Month" is used in drop down list in cell C17, instructions below on how to create drop down lists and edit chart settings.
Region - Formula:
Named range "Region" is used in drop down list in cell C16.
Chart - Formula:
Named range "Chart" is used as Series values in the chart.
ChartCat - Formula:
Named range "ChartCat" is used as category values in the chart.
Series - Formula:
Named range "Series" is used as series name in the chart.
Explaining "ChartCat " Formula
The ChartCat formula extracts the category values based on which drop down list is being used.
Step 1 - Return headers or column values?
The IF function checks if cell C16 is not empty, if TRUE then return column Month/Region values, if FALSE then return headers except the first one.
IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
becomes
IF("East"<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
and returns
IF(TRUE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
Step 2 - Return an array of values
The INDEX function allows you to get a value from a cell range, however, if you use a 0 (zero) as a row or column argument then you will get the entire row or column as an array. If you use 0's (zeros) in both row and column arguments you will get the entire cell range.
IF(TRUE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
becomes
IF(TRUE, {"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}, OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
and returns
{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}.
Step 3 - Return headers
If the logical expression returns FALSE the following will happen.
IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
becomes
IF(""<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
becomes
IF(FALSE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
The COUNTA function counts the number of headers in the Excel defined Table, we need a value that is 1 less than the number of headers.
IF(FALSE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
becomes
IF(FALSE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))
becomes
IF(FALSE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA({"Month/Region","North","East","South","West"})-1))
becomes
IF(FALSE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , 5-1))
becomes
IF(FALSE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , 4))
The OFFSET function extracts the headers except the first one.
IF(FALSE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , 4))
becomes
IF(FALSE, INDEX(Table1[Month/Region], 0, 0), {"North","East","South","West"})
and returns
{"North","East","South","West"}.
Step - Check if both cell C16 and C17 are empty
If both drop down lists are empty then return nothing.
IF((Sheet1!$C$16="")*(Sheet1!$C$17=""), 0, IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1)))
becomes
IF((""="")*(""=""), 0, IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1)))
becomes
IF(TRUE*TRUE, 0, IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1)))
becomes
IF(TRUE, 0, IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1)))
and returns 0 (zero).
Create two drop down lists
- Select cell C16
- Go to tab "Data"
- Press with left mouse button on "Data Validation" button
- Select List
- Type =Region in Source:
- Press with left mouse button on OK
- Select cell C17
- Go to tab "Data"
- Press with left mouse button on "Data Validation" button
- Select List
- Type =Month in source
- Press with left mouse button on OK
Setting up the chart
- Create a bar chart.
- Press with right mouse button on on chart.
- Press with left mouse button on "Select Data...".
- Press with left mouse button on "Add" button.
- Type =Sheet1!Series in Series name:
- Type =Sheet1!Chart in Series values:
- Press with left mouse button on Ok
- Press with left mouse button on "Edit" button
- Type =Sheet1!ChartCat in Axis label range:
- Press with left mouse button on ok
- Press with left mouse button on ok
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 […]