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