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
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 highlight a line in a chart based on the selected item in a drop-down list. […]
Interactive 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 describes how to create an interactive chart, the user may press with left mouse button on a button […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
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 change chart series while hovering with mouse cursor over a series name. The image above […]
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
What if you want to show a selection of a data set on a chart and easily change that selection? […]
Functions in this article
More than 1300 Excel formulas
Excel categories
4 Responses to “Use drop down lists and named ranges to filter chart values”
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.
Hi,
I am trying to create a dynamic chart (a line chart), but a bit different to the one in this example. I have thousands of records on file and would like to be able to create a chart where I can use multiple filters. The below description is just an example of what I want to achieve.
At the moment I have: one row per customer, with information about customers' behaviour. I would like to show on my graph how many customers there are with x number of purchases (I have this info) - so it would be my x-axis, and be able to filter this information out by acquisition campaigns, and whether customer is still seen as active or lapsed. Now there is a little bit more tricky part. What I really want on my chart is % of all customers with 1 purchase, 2 purchases etc. So if someone is recruited by campaign A (I want to use filter here and simultaneously another one with customer's status) then from 100% of recruited customers I want to see how many made 1 purchase, how many 2 purchases etc, so someone with 3 purchases would be included in 3 groups (because they have done 1 and 2 purchases before doing 3rd one). I hope it makes sense. It is like survival graph.
Another tricky part is to include two lines representing two different campaigns so that they can be compared on one chart at the same time. It seems that maybe reapeating the same excercise on the same graph and defining two filters for campaigns could be a solution, but I wouldn't know for sure.
Is it something you will be able to help me with?
Many thanks,
Joanna
example of the data:
campaign status Purchases
A Lapsed 5
B Lapsed 9
C Active 12
D Lapsed 6
D Lapsed 1
D Lapsed 13
E Lapsed 1
E Lapsed 1
E Lapsed 1
C Active 11
D Lapsed 2
C Lapsed 10
D Active 12
A Lapsed 1
B Lapsed 4
B Lapsed 4
D Lapsed 11
A Lapsed 3
B Lapsed 3
B Lapsed 1
C Lapsed 14
C Lapsed 9
C Active 12
D Lapsed 2
E Lapsed 1
E Lapsed 16
D Lapsed 4
D Lapsed 1
A Active 4
B Lapsed 2
A Lapsed 3
B Active 7
B Lapsed 4
E Active 8
C Lapsed 11
E Lapsed 2
E Active 8
D Lapsed 2
B Lapsed 8
Can you share the actual data file so that I can think of what can be done?
Hi There,
How can i share my excel sheet with you? I have a work assignment similar to the tutorial above but i will need to display two related graphs (side by side) when I press/select one of the option box.
Please help me.
Thanks in advance,
JL
Jamie. L
Upload a file