Author: Oscar Cronquist Article last updated on September 03, 2019

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

Compare data series in a chart.xlsx

### Drop down lists

1. Select cell B21
2. Go to tab "Data"
3. Click "Data Validation" button
4. Allow: Select List

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

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
5. Name: Chrt_tbl1
7. Click OK

Create another named range, named Chrt_tbl2 and use this formula: =INDEX(Table2, 0, MATCH(Chart!\$D\$21, Table2[#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
6. Series name: Chart!\$B\$21
7. Series values: =Calculation!Chrt_tbl1 (DonÂ´t forget to use the sheet reference!)
8. Click OK
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