In excel it is not easy to change a chart´s data source without manually changing the data source in chart settings. I wrote this post Change chart data range using a drop down list (vba) almost two years ago. So how do you change the data source without executing a visual basic macro?

Dynamic chart data source1

As far as I know, you have two options

  • named range
  • or a second excel table.

The named range example expands automatically, the second excel table does not.

Named range

dynamic chart data source named range

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

Follow these steps to create the chart, see picture above.

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

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

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 sheet name!
  6. Click OK
  7. Click OK

"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 *.xlsm file

Dynamic chart data source.xlsx

Functions in this post:

INDEX(arrayrow_num[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

MATCH(lookup_valuelookup_array[match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order