What if you want to show a selection of a data set on a chart and easily change that selection? Later Excel versions have tools that make this easy, I will in this article describe the options you have and how to configure them.

The image above shows a chart and data, the chart shows only a part of the data and I will demonstrate in this article a few techniques you can use to filter chart data based on the Excel version.

If you are looking for a VBA solution then this article is something for you. Change chart data range using a drop down list (vba) So how do you change the data source without using a Visual Basic macro?

As far as I know, you have these options:

1. Manually changing chart data source

This technique works in all Excel versions, however, it is tedious to change the chart source every time you want to display another part of the data.

The image above shows the data in cell range B3:E14 and the column chart below shows months horizontally and the columns show temperatures for column D.

I will now describe how to change the chart data source manually, I don't recommend doing this on a daily basis. There are better options.

To change the item press with right mouse button on on the chart, press with left mouse button on "Select Data...". A dialog box appears.

Press with left mouse button on the "arrow" button, see image above.

Select cell range B2:B14. Press and hold the CTRL key while selecting cell range E2:E14. Press Enter to return to the dialog box.

Press with left mouse button on OK button on the dialog box named "Select Data Source" to apply the changes we made.

Another option is to select the chart to display the chart data source ranges. Press and hold with the left mouse button on a data source range line, then drag with the mouse to select a new range.

Back to top

2. Filter chart data using an Excel Table

I recommend that you use an Excel Table if you can. The befits are great, the chart data source expands automatically if you add more records. Also, the chart data source range shrinks if you delete rows in the Excel Table.

Excel Tables came in Excel 2007 and works in all later versions.

I had to transpose the data to be able to filter based on an item. Skip this step if this is not needed in your worksheet.

Copy the original range, press with right mouse button on on a destination cell.

A popup menu appears, press with left mouse button on "Paste Special...". A dialog box shows up named "Paste Special".

Press with left mouse button on the checkbox next to Transpose, see image above. Press with left mouse button on OK button.

This will rearrange data so columns become rows and vice versa. This allows us to filter data based on an item.

Delete the old data and move the transposed data to above the chart. Select the transposed data and Press CTRL + T.

A dialog box named "Create Table" shows up, press with left mouse button on OK button.

Press with left mouse button on the arrow next to column header Month, a popup menu appears.

Deselect all items except "London".

The chart shows only the filtered Excel Table data and is almost instantly refreshed.

Back to top

3. Filter chart data with Slicers

Slicers were introduced in Excel version 2010 and I highly recommend this tool if you need to quickly change the chart data source based on what the user selects.

They provide buttons that the user can press with left mouse button on which filter both the Excel Table and the chart simultaneously.

The image above shows a clustered column chart, an Excel Table below, and a slicer to the right. A slicer works only with Excel Tables so you need to convert data to an Excel Table which is quickly done. I describe how to create an Excel Table in the previous section above.

Press with mouse on an item and the Excel Table is filtered based on the selected value, see image above. The chart graphs what the Excel Table shows and the changes are almost instant.

To clear selections press with left mouse button on the button located on the top right corner of the slicer. I have described in another article how to create slicers, check it out: Use slicers to quickly filter chart data

Now, if you have an earlier version than Excel 2010 or slicers are not suitable for your worksheet then read on.

Back to top

4. Use a named range to change chart data

The animated image above demonstrates a drop down list that allows you to show specific chart data based on the selected column name.

The named range contains a formula that uses the selected value in cell C25 to create a cell reference that the chart then uses to plot data.

Drop down list -> Named range - > Chart

Basically the same technique used here as in post Make a dynamic chart for the most recent 12 months data, however, the named range formula is different.

4.1 Drop down list

Here are the steps to create the drop-down list in cell C25:

1. Select cell C25.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data Validation" button.
4. Go to tab "Settings".
   
5. Select List.
6. Source: Select table headers.
7. Press with left mouse button on OK button.

4.2 Create a named range

1. Go to tab "Formulas".
2. Press with left mouse button on "Name Manager" button.
3. Press with left mouse button on "New.." button.
   
4. Enter a name.
5. Enter the following formula:
   =INDEX(Table16,0, MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)+1)
6. Press with left mouse button on OK button.

4.3 Explaining the named range formula

Step 1 - Find column in Excel Table based on the selected drop-down list value

The MATCH function returns the relative position of an item in a cell range.

MATCH(lookup_value, lookup_array, [match_type])

The first argument lookup_value is the drop down list cell, the second argument is a cell reference to the Excel Table column header names, and the third argument is 0 (zero) which means there must be an exact match.

MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)

becomes

MATCH("London",{"London","Tokyo","Los Angeles"},0)

and returns 1.

Step 2 - Return values from column

The INDEX function returns a single value or multiple values based on a row and optionally a column number.

INDEX(array, [row_num], [column_num])

The first argument is an array or a cell reference, in this case, a cell reference to Excel Table named Table16.

The second argument is the row number, however, if you use 0 (zero) the INDEx function returns all the value from the specified Excel Table column.

INDEX(Table16,0, MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)+1)

becomes

INDEX(Table16,0, 1+1)

There is a column before the column name "London" in the Excel Table, that is why we need to add 1.

INDEX(Table16,0, 1+1)

becomes

INDEX(Table16,0, 1+1)

and returns {5; 5; 8; 9; 13; 16; 18; 18; 16; 12; 8; 6}.

4.4 Setting up the chart

Here are the steps to create the chart:

1. Press with right mouse button on on chart.
2. Press with left mouse button on "Select Data...".
   
3. Press with left mouse button on "Edit" button (Legend Entries).
   
4. Series name: Cell C25.
5. Series values: The named range "data". Don't forget the worksheet name!
6. Press with left mouse button on OK button.
7. Press with left mouse button on OK button again.

Back to top

5. "Helper" table

The following example has two tables and a drop-down list. The table to the right has "dynamic" values and they change depending on the chosen value in cell C25. But if you add values to the first table, the second table does not automatically include the new values. You have to change the table size yourself.

It is not as pretty as a named range but easier to work with if you want multiple data columns and chart series. The attached file below contains an example with multiple chart series.

5.1 Drop down list

1. Select cell C25.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data Validation" button.
4. Go to tab "Settings".
   
5. Select List.
6. Source: Select table headers.
7. Press with left mouse button on OK button.

5.2 Create the second table

1. Type month in cell F1 and city in cell G1.
2. Select cell range F1:G13.
3. Go to tab "Insert" on the ribbon.
4. Press with left mouse button on "Table" button.
5. Press with left mouse button on "My tables has headers".
6. Press with left mouse button on OK.

5.3 Enter Excel Table formula

1. Select cell F2.
2. Type:
   =Table1[@Month]

   and press Enter.

3. Select cell G2.
4. Type:
   =INDEX(Table1[@[London]:[Los Angeles]],MATCH($C$25,Table1[[#Headers],[London]:[Los Angeles]],0))
5. Press Enter.

Back to top

Get the Excel file

Dynamic-chart-data-source.xlsx

Back to top