100% stacked column
Waterfall chart 
Treemap chart 
Sunburst chart 
Histogram chart 
Pareto chart 
Box and Whisker 
Axis lines & values
Slicers - Filter chart data
Resize a chart
Align chart to cell grid
Group chart categories
Lines between columns
Graph an equation
Heat map calendar
Gantt Chart Template
Group of chart bars
Line in line chart
Bar in a bar chart
Change data source
Two drop down lists
Click to select series
Select a cell
Drop down list
Custom data labels(1)
Custom data labels(2)
Label line chart series
Between tick marks
Add line to chart
Add pictures to chart axis
Color chart bars by value
Primary data hidden
Stock chart with 2 series
Adjust axis value range
Color based on prior val
Hide specific columns
Dynamic stock chart
Use pictures in a chart
Chart color based on cell
Heat map using pictures
Dynamic Gantt charts
Weekly Blog EMAIL
Get a FREE PDF book!
Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
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(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
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
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 […]
Highlight group of values in an x y scatter chart programmatically
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
Use a map in an Excel chart
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 […]
How to create a dynamic 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 […]
Heat map yearly calendar
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 […]
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?
Create another named range, named Chrt_tbl2 and use this formula: =INDEX(Table1, 0, MATCH(Chart!$D$21, Table1[#Headers], 0))
you are right, thank you!
I have made changes to this article.
Mail (will not be published) (required)
Notify me of followup comments via e-mail
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.
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
You can contact me through this contact form