The two drop down lists below the chart let´s you compare two data series from separate tables.

compare chart series4

It is easier to follow the tutorial steps if you download the excel *.xlsx file:
Compare data series in a chart.xlsx

Drop down lists

  1. Select cell B21
  2. Go to tab "Data"
  3. Click "Data Validation" button
    compare data series in a chart drop down list
  4. Allow: Select List
  5. Source: =INDIRECT("Table1[#Headers]")

Repeat above steps with cell D21 and use this Data Validation formula:

=INDIRECT("Table2[#Headers]")

The following post explains how to use table names in drop down lists and conditional formatting formulas: How to use a table name in data validation lists and conditional formatting formulas

Named ranges

There are two tables in sheet Calculation. Let´s create named ranges and later we are going to use them as series values in a chart.

  1. Go to sheet Calculation
  2. Go to tab "Formulas"
  3. Click "Name Manager" button
  4. Click "New..." button
    compare data series in a chart drop down list2
  5. Name: Chrt_tbl1
  6. Refers to: =INDEX(Table1,0,MATCH(Chart!$B$21,Table1[#Headers],0))
  7. Click OK

Create another named range, named Chrt_tbl2 and use this formula: =INDEX(Table1, 0, MATCH(Chart!$D$21, Table1[#Headers], 0))

Named ranges are really useful, this post demonstrates how to add values automatically to a chart: How to create a dynamic chart

Insert chart

  1. Go back to sheet "Chart"
  2. Go to tab "Insert"
  3. Click "Column" chart button | "Clustered column" chart button
  4. Right click on the empty chart
  5. Click "Add" button
    compare data series in a chart drop down list3
  6. Series name: Chart!$B$21
  7. Series values: =Calculation!Chrt_tbl1 (Don´t forget to use the sheet reference!)
  8. Click OK
  9. Click "Add" button again
    compare data series in a chart drop down list4
  10. Series name: Chart!$D$21
  11. Series values: =Calculation!Chrt_tbl2  (Don´t forget to use the sheet reference!)
  12. Click OK
  13. Click OK

The following post shows you how to select a series using both an index column and an index row: Dynamic chart – Display values from a table row or column

Download excel *.xlsx file

Compare data series in a chart.xlsx

Functions in this post:

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

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