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
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
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.
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
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
Compare data series in a chart.xlsx
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
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Dynamic chart – Display values from a table row or column
Use dynamic ranges to automatically add new values
Add pictures to a chart axis
Excel charts: Multiple series and named ranges
Change chart data range using a Drop Down List [VBA]
Highlight a data series in a line chart
How to create a dynamic chart
Use a mouse hovering technique to create an interactive chart
Interactive Excel chart [VBA]
Create dependent drop down lists containing unique distinct values
Create a drop down list containing only unique distinct alphabetically sorted text values
Apply dependent combo box selections to a filter
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
Dependent data validation lists in multiple rows
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.
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.
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.
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.
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?
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.
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.
Can you explain what you are trying to do?
How to add a formula to your comment:
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
You can contact me through this webpage
Mail (will not be published) (required)
Notify me of followup comments via e-mail
Most used functions
Free Blog Emails