Author: Oscar Cronquist Article last updated on August 20, 2020

Quickly change data source manually

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:

Manually changing chart data source

Quickly change data source manually

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 right-click on the chart, click on "Select Data...". A dialog box appears.

Quickly change data source Select Data Source

Click the "arrow" button, see image above.

Quickly change data source Select Data Source1

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.

Click OK button on the dialog box named "Select Data Source" to apply the changes we made.

Quickly change data source Select Data Source2

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

Change data source manually

Excel Table

Quickly change data source 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, right-click on a destination cell.

Quickly change data source transpose data

A popup menu appears, click on "Paste Special...". A dialog box shows up named "Paste Special".

Quickly change data source Paste Special

Click the checkbox next to Transpose, see image above. Click OK button.

Quickly change data source Paste Special transpose

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.

Quickly change data source create Excel Table

A dialog box named "Create Table" shows up, click OK button.

Quickly change data source Excel Table1

Click the arrow next to column header Month, a popup menu appears.

Quickly change data source Filter Excel Table

Deselect all items except "London".

Note, you can deselect all by disabling the checkbox next to "Select All", this is a time saver if you have plenty of items to deselect.

Quickly change data source Excel Table

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

Slicers

Change data source slicer 1

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 click 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.

Quickly change data source slicer items

Click 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.

Quickly change data source clear slicer

To clear selections click 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

Use a named range to change chart data

dynamic chart data source named range

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.

Drop down list

  1. Select cell C25
  2. Go to tab "Data" on the ribbon
  3. Click "Data Validation" button
  4. Go to tab "Settings"
    data valiation - drop down list
  5. Select List
  6. Source: Select table headers
  7. Click OK button
Note, you can't use structured references as a source in a drop-down list unless you also use the INDIRECT function. Actually, there are other workarounds as well if you are interested.

Create a named range

  1. Go to tab "Formulas"
  2. Click "Name Manager" button
  3. Click "New.." button
    Named range - dynamic chart data source
  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. Click OK

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}.

Setting up the chart

  1. Right click on chart
  2. Click "Select Data..."
    select data source - dynamic chart data
  3. Click "Edit" button (Legend Entries)
    edit series - dynamic chart data
  4. Series name: Cell C25
  5. Series values: The named range "data". Don't forget the worksheet name!
  6. Click OK button.
  7. Click OK button again.

"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.

dynamic chart data source tables

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.

Drop down list

  1. Select cell C25
  2. Go to tab "Data" on the ribbon
  3. Click "Data Validation" button
  4. Go to tab "Settings"
    data valiation - drop down list
  5. Select List
  6. Source: Select table headers
  7. Click OK button

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. Click "Table" button
  5. Click "My tables has headers"
  6. Click OK

Enter formulas

  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

Download Excel file


Dynamic-chart-data-source.xlsx

Recommended links

Filtering charts in Excel

Change the data series in a chart

Excel slicers for pivot tables and charts