Author: Oscar Cronquist Article last updated on September 20, 2019

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. Click 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. Click "Name Manager" button to open the "Name Manager" dialog box.
  3. Click 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. Click OK button.
  7. Click "Close" button.

Month - Formula:

=INDEX(Table1[Month/Region],0,0)

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:

=INDEX(Table1[#Headers],0,0)

Named range "Region" is used in drop down list in cell C16.

Chart - Formula:

=IF((Sheet1!$C$16="")*(Sheet1!$C$17=""), 0, IF(Sheet1!$C$16="", OFFSET(Table1, MATCH(Sheet1!$C$17, Table1[Month/Region], 0)-1, 1, 1, COUNTA(Table1[#Headers])-1), INDEX(Table1, 0, MATCH(Sheet1!$C$16, Table1[#Headers], 0))))

Named range "Chart" is used as Series values in the chart.

ChartCat - Formula:

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

Named range "ChartCat" is used as category values in the chart.

Series - Formula:

=IF((C16="")*(C17=""),"",IF(C16="",C17,C16))

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

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

Setting up the chart

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

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!