Use drop down lists to compare data series in an excel chart
The two drop down lists below the chart let´s you compare two data series from separate tables.
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
- Select cell B21
- Go to tab "Data"
- Click "Data Validation" button
- Allow: Select List
- Source: =INDIRECT("Table1[#Headers]")
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.
- Go to sheet Calculation
- Go to tab "Formulas"
- Click "Name Manager" button
- Click "New..." button
- Name: Chrt_tbl1
- Refers to: =INDEX(Table1,0,MATCH(Chart!$B$21,Table1[#Headers],0))
- 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
- Go back to sheet "Chart"
- Go to tab "Insert"
- Click "Column" chart button | "Clustered column" chart button
- Right click on the empty chart
- Click "Add" button
- Series name: Chart!$B$21
- Series values: =Calculation!Chrt_tbl1 (Don´t forget to use the sheet reference!)
- Click OK
- Click "Add" button again
- Series name: Chart!$D$21
- Series values: =Calculation!Chrt_tbl2 (Don´t forget to use the sheet reference!)
- Click OK
- 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_value, lookup_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
Related articles
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 […]
Dynamic data labels in a chart
My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values […]
This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter […]
Quickly change chart data source
In excel it is not easy to change a chart´s data source without manually changing the data source in chart […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
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 […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
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. […]
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
Dependent drop-down lists in multiple rows
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
11 Responses to “Use drop down lists to compare data series in an excel chart”
Leave a Reply to Jaye
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.
English
Spanish
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 downloaded 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 clicks 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.