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,

1. Select cell F14.
2. Press Tab key on your keyboard.
3. Selected cell is now B15 which is the first cell of the new record. This creates a new row in the Excel defined Table.
4. 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

1. Select any cell in your data set.
2. Press CTRL + T to open the Table dialog box.
   
3. 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:

1. Go to tab "Formulas" on the ribbon.
2. Press with left mouse button on "Name Manager" button to open the "Name Manager" dialog box.
3. Press with mouse on "New..." button to create a new named range.
4. Type a name based on the names displayed below.
5. Copy/Paste the corresponding formulas to the "Refers to:" field.
6. Press with left mouse button on OK button.
7. 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"; ... ; "December"}, OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))

and returns

{"January"; "February"; ... ; "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))

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.

The OFFSET function extracts the headers except the first one.

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

returns 0 (zero).

Create two drop down lists

1. Select cell C16
2. Go to tab "Data"
3. Press with left mouse button on "Data Validation" button
4. Select List
   
5. Type =Region in Source:
6. Press with left mouse button on OK
7. Select cell C17
8. Go to tab "Data"
9. Press with left mouse button on "Data Validation" button
10. Select List
11. Type =Month in source
12. Press with left mouse button on OK

Setting up the chart

1. Create a bar chart.
2. Press with right mouse button on on chart.
3. Press with left mouse button on "Select Data...".
4. Press with left mouse button on "Add" button.
   
5. Type =Sheet1!Series in Series name:
6. Type =Sheet1!Chart in Series values:
   
7. Press with left mouse button on Ok
8. Press with left mouse button on "Edit" button
   
9. Type =Sheet1!ChartCat in Axis label range:
   
10. Press with left mouse button on ok
11. Press with left mouse button on ok

Get the Excel file

Dynamic-chart-Display-any-row-or-columnv2.xlsx

Built-in Charts

Combo Charts

Chart elements

Chart basics

Custom charts

Sparklines

Highlight chart elements

Interactive charts

Animate

Advanced charts

Stock charts

Excel categories

Comments (4)