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:
- Select cell B21.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on the "Data Validation" button.
- Allow: Select List, see image above.
- 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.
- Go to worksheet Calculation.
- Go to tab "Formulas" on the ribbon.
- Press with left mouse button on "Name Manager" button and a dialog box appears.
- Press with left mouse button on "New..." button.
- Name: Chrt_tbl1
- Refers to:Â =INDEX(Table1,0,MATCH(Chart!$B$21,Table1[#Headers],0))
- 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
- Go back to sheet "Chart".
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Column" chart button | "Clustered column" chart button.
- Press with right mouse button on on the empty chart.
- Press with left mouse button on "Select Data...", see image above.
- Press with left mouse button on "Add" button.
- Series name: Chart!$B$21
- Series values: =Calculation!Chrt_tbl1 (Don't forget to use the sheet reference)
- Press with left mouse button on OK button.
- Press with left mouse button on "Add" button again.
- Series name: Chart!$D$21.
- Series values: =Calculation!Chrt_tbl2Â Â (Don't forget to use the sheet reference)
- Press with left mouse button on OK button.
- 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
Charts category
Table of Contents How to add lines between stacked columns/bars (Excel charts) Use slicers to quickly filter chart data How […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
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
Table of Contents Create dependent drop down lists containing unique distinct values Create dependent drop down lists containing unique distinct […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Excel categories
11 Responses to “Compare data in an Excel chart using drop down lists”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have followed the "Use drop down lists to compare data series in an excel chart" tutorial to the letter and it is not working for me. I am using this type of chart in a dashboard and want it to compare the information. The part I am having the issue with is linking the chart and dropdowns I am using the =Calculation!Chrt_tbl1 formula in the series value but excel keeps telling me:-
A formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and cell reference.
Can you please, please help me I have been driving myself nuts over it for almost a week now.
Jaye,
Can you provide the named range formulas or upload your workbook.
I don't know if Oscar has answered or not, you simply need to make a selection from your dropdown. If you do, then the calculation should be accepted.
Hi, Oscar;
Is it possible to have a multi variable dropdown list comparing two countries (these are also drop down lists).
I have three drop-down lists, two of them are regions (much like your example here), and the third consist of 2 variablees -- cost and manpower. I would like to compare one region's cost or manpower to another region, but I am stumped on how to populate my chart's data tables in order to reflect the above.
Thanks!
Hi, I am trying to compare the data series not in excel chart but in excel graph.
However, I cannot set the Headers of the table.
I got your excel file and the Headers pull down list also is not working. Please help me.
Hi Oscar!
Thank you so much for your comprehensive guide, it's been extremely helpful. I'm trying to add a live filter function with a slicer, so I can filter which months are displayed on the X-axis. When I do so however, I need to reselect the ranges in the dropdown menu for it to update the chart. Do you have any idea how I could ensure that the charts will update right away as the user presses with left mouse button on the filter options in the slicer?
Much appreciated!
Gerbrand
Hi,
Could you help me please how to use countifs formula based on Data validation list?. Same thing I have been written the formula but when I selecting the dropdown values are not changing kindly help me on that.
Thanks
Sri.
Hi,
Could you help me please how to use countifs formula based on Data validation list in VBA?. Same thing I have been written the formula but its not working when I changing the names in the dropdown kindly help me on that.
Thanks
Sri.
Hi Sri
Can you explain what you are trying to do?
Create another named range, named Chrt_tbl2 and use this formula: =INDEX(Table1, 0, MATCH(Chart!$D$21, Table1[#Headers], 0))
should be
Create another named range, named Chrt_tbl2 and use this formula: =INDEX(Table2, 0, MATCH(Chart!$D$21, Table2[#Headers], 0))
chris,
you are right, thank you!
I have made changes to this article.