Compare data in an Excel chart using drop down lists

I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the drop down lists allow the Excel user to easily compare data series based on two Excel Tables.

The image above shows one drop down list in cell B21 and another in cell D21, the chart above those drop down lists displays the selected data series.

How to build

You will in this article learn how to:

• create drop down lists.
• link drop down list to Excel Table.
• create a named range containing a formula.
• insert a chart
• link chart data source to named range

Drop down lists

A drop down list allows you to control which values a user can enter in a cell. If the Excel user selects a cell containing a drop down list it changes and shows an arrow next to the cell.

Press with left mouse button on the arrow next to the cell with left mouse button and the cell displays all valid values you can choose from. Press with left mouse button on a value to select it and the cell will be populated with the selected value.

Note that you can't spot a drop down list on a worksheet, the arrow is only displayed if you select a cell containing a drop down list.

Be aware that it is really easy to enter an invalid value in a cell that contains a drop down list, copy a cell containing the value you want to use and paste it to the cell containing the drop down list. It will be overwritten with the new value and the drop down list is now gone.

Here are the steps to create a drop down list:

1. Select cell B21.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on the "Data Validation" button.
   
4. Allow: Select List, see image above.
5. Source: =INDIRECT("Table1[#Headers]")

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

The INDIRECT function makes it possible to use structured references in drop down lists. A structured reference is basically a reference to an Excel Table, in this case it is a cell reference to the column headers in Table2 which is an Excel Table.

The following post explains in greater detail 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

A named range allows you to specify a name for a single cell or a cell range, constant, formula or a data set. This makes it easier to work with formulas, charts, etc. For example, a cell reference in a formula usually don't say much about what it contains whereas a named range makes it clear what it is, given that you use a descriptive name.

Compare cell reference Sheet4!B3:G6 with the named range Budget2019 and you understand the benefits of using named ranges. It is easy to manage named ranges, Excel has a tool for that named "Name manager". Go to tab "Formulas" on the ribbon, press with left mouse button on "Name manager" button.

The reason I am using a named range in this example is that it allows you to define a formula as a named range, I will be using that formula in a chart to display values based on the selected values in the drop down lists.

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

1. Go to worksheet Calculation.
2. Go to tab "Formulas" on the ribbon.
3. Press with left mouse button on "Name Manager" button and a dialog box appears.
4. Press with left mouse button on "New..." button.
   
5. Name: Chrt_tbl1
6. Refers to: =INDEX(Table1,0,MATCH(Chart!$B$21,Table1[#Headers],0))
7. Press with left mouse button on OK

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

Explaining formula in named range Chrt_tbl1

Step 1 - Find position of selected value

The MATCH function returns a number representing the relative position of the value in cell Chart!$B$21 in Table1[#Headers].

MATCH(Chart!$B$21,Table1[#Headers],0)

becomes

MATCH("North America 2011",Table1[#Headers],0)

becomes

MATCH("North America 2011",{"Region", "Africa 2011", "Europe 2011", "North America 2011", "South America 2011", "Australia 2011", "Asia 2011"},0)

and returns 4.

"North America 2011" is the fourth header name in the Excel Table.

Step 2 - Return header name based on position

The INDEX function returns, in this case, an array of values based on the selected drop down value.

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

An array is returned if you use 0 (zero) in the [row_num] or [column_num] argument or in both. In this example the [row_num] is 0 (zero) and the INDEX function will return all values from a given column.

INDEX(Table1,0,MATCH(Chart!$B$21,Table1[#Headers],0))

becomes

INDEX(Table1, 0, 4)

and returns {56612.3491014125; 57438.8810088729; 59697.2292143781; 56747.2057561886; 54464.3935799677; 53768.3710509187; 53320.1658788137; 54348.2912056957; 54497.7912101573; 53331.9166437539; 51050.540119587; 51948.5058470412}.

Insert chart

1. Go back to sheet "Chart".
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Column" chart button | "Clustered column" chart button.
4. Press with right mouse button on on the empty chart.
5. Press with left mouse button on "Select Data...", see image above.
6. Press with left mouse button on "Add" button.
   
7. Series name: Chart!$B$21
8. Series values: =Calculation!Chrt_tbl1 (Don't forget to use the sheet reference)
9. Press with left mouse button on OK button.
10. Press with left mouse button on "Add" button again.
    
11. Series name: Chart!$D$21.
12. Series values: =Calculation!Chrt_tbl2  (Don't forget to use the sheet reference)
13. Press with left mouse button on OK button.
14. Press with left mouse button on OK button.

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

Animated image

Get the Excel file

Compare-data-series-in-a-chart.xlsx

Charts category

Excel charts tips and tricks
Table of Contents How to add lines between stacked columns/bars (Excel charts) Use slicers to quickly filter chart data How […]

How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]

Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Drop down lists category

Create dependent drop down lists containing unique distinct values
Table of Contents Create dependent drop down lists containing unique distinct values Create dependent drop down lists containing unique distinct […]

Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Excel categories

Comments (11)