How to filter chart data
What if you want to show a selection of a data set on a chart and easily change that selection? Later Excel versions have tools that make this easy, I will in this article describe the options you have and how to configure them.
The image above shows a chart and data, the chart shows only a part of the data and I will demonstrate in this article a few techniques you can use to filter chart data based on the Excel version.
If you are looking for a VBA solution then this article is something for you. Change chart data range using a drop down list (vba) So how do you change the data source without using a Visual Basic macro?
As far as I know, you have these options:
What's on this page
1. Manually changing chart data source
This technique works in all Excel versions, however, it is tedious to change the chart source every time you want to display another part of the data.
The image above shows the data in cell range B3:E14 and the column chart below shows months horizontally and the columns show temperatures for column D.
I will now describe how to change the chart data source manually, I don't recommend doing this on a daily basis. There are better options.
To change the item press with right mouse button on on the chart, press with left mouse button on "Select Data...". A dialog box appears.
Press with left mouse button on the "arrow" button, see image above.
Select cell range B2:B14. Press and hold the CTRL key while selecting cell range E2:E14. Press Enter to return to the dialog box.
Press with left mouse button on OK button on the dialog box named "Select Data Source" to apply the changes we made.
Another option is to select the chart to display the chart data source ranges. Press and hold with the left mouse button on a data source range line, then drag with the mouse to select a new range.
2. Filter chart data using an Excel Table
I recommend that you use an Excel Table if you can. The befits are great, the chart data source expands automatically if you add more records. Also, the chart data source range shrinks if you delete rows in the Excel Table.
Excel Tables came in Excel 2007 and works in all later versions.
I had to transpose the data to be able to filter based on an item. Skip this step if this is not needed in your worksheet.
Copy the original range, press with right mouse button on on a destination cell.
A popup menu appears, press with left mouse button on "Paste Special...". A dialog box shows up named "Paste Special".
Press with left mouse button on the checkbox next to Transpose, see image above. Press with left mouse button on OK button.
This will rearrange data so columns become rows and vice versa. This allows us to filter data based on an item.
Delete the old data and move the transposed data to above the chart. Select the transposed data and Press CTRL + T.
A dialog box named "Create Table" shows up, press with left mouse button on OK button.
Press with left mouse button on the arrow next to column header Month, a popup menu appears.
Deselect all items except "London".
The chart shows only the filtered Excel Table data and is almost instantly refreshed.
3. Filter chart data with Slicers
Slicers were introduced in Excel version 2010 and I highly recommend this tool if you need to quickly change the chart data source based on what the user selects.
They provide buttons that the user can press with left mouse button on which filter both the Excel Table and the chart simultaneously.
The image above shows a clustered column chart, an Excel Table below, and a slicer to the right. A slicer works only with Excel Tables so you need to convert data to an Excel Table which is quickly done. I describe how to create an Excel Table in the previous section above.
Press with mouse on an item and the Excel Table is filtered based on the selected value, see image above. The chart graphs what the Excel Table shows and the changes are almost instant.
To clear selections press with left mouse button on the button located on the top right corner of the slicer. I have described in another article how to create slicers, check it out: Use slicers to quickly filter chart data
Now, if you have an earlier version than Excel 2010 or slicers are not suitable for your worksheet then read on.
4. Use a named range to change chart data
The animated image above demonstrates a drop down list that allows you to show specific chart data based on the selected column name.
The named range contains a formula that uses the selected value in cell C25 to create a cell reference that the chart then uses to plot data.
Drop down list -> Named range - > Chart
Basically the same technique used here as in post Make a dynamic chart for the most recent 12 months data, however, the named range formula is different.
4.1 Drop down list
Here are the steps to create the drop-down list in cell C25:
- Select cell C25.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Data Validation" button.
- Go to tab "Settings".
- Select List.
- Source: Select table headers.
- Press with left mouse button on OK button.
4.2 Create a named range
- Go to tab "Formulas".
- Press with left mouse button on "Name Manager" button.
- Press with left mouse button on "New.." button.
- Enter a name.
- Enter the following formula:
=INDEX(Table16,0, MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)+1)
- Press with left mouse button on OK button.
4.3 Explaining the named range formula
Step 1 - Find column in Excel Table based on the selected drop-down list value
The MATCH function returns the relative position of an item in a cell range.
MATCH(lookup_value, lookup_array, [match_type])
The first argument lookup_value is the drop down list cell, the second argument is a cell reference to the Excel Table column header names, and the third argument is 0 (zero) which means there must be an exact match.
MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)
becomes
MATCH("London",{"London","Tokyo","Los Angeles"},0)
and returns 1.
Step 2 - Return values from column
The INDEX function returns a single value or multiple values based on a row and optionally a column number.
INDEX(array, [row_num], [column_num])
The first argument is an array or a cell reference, in this case, a cell reference to Excel Table named Table16.
The second argument is the row number, however, if you use 0 (zero) the INDEx function returns all the value from the specified Excel Table column.
INDEX(Table16,0, MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)+1)
becomes
INDEX(Table16,0, 1+1)
There is a column before the column name "London" in the Excel Table, that is why we need to add 1.
INDEX(Table16,0, 1+1)
becomes
INDEX(Table16,0, 1+1)
and returns {5; 5; 8; 9; 13; 16; 18; 18; 16; 12; 8; 6}.
4.4 Setting up the chart
Here are the steps to create the chart:
- Press with right mouse button on on chart.
- Press with left mouse button on "Select Data...".
- Press with left mouse button on "Edit" button (Legend Entries).
- Series name: Cell C25.
- Series values: The named range "data". Don't forget the worksheet name!
- Press with left mouse button on OK button.
- Press with left mouse button on OK button again.
5. "Helper" table
The following example has two tables and a drop-down list. The table to the right has "dynamic" values and they change depending on the chosen value in cell C25. But if you add values to the first table, the second table does not automatically include the new values. You have to change the table size yourself.
It is not as pretty as a named range but easier to work with if you want multiple data columns and chart series. The attached file below contains an example with multiple chart series.
5.1 Drop down list
- Select cell C25.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Data Validation" button.
- Go to tab "Settings".
- Select List.
- Source: Select table headers.
- Press with left mouse button on OK button.
5.2 Create the second table
- Type month in cell F1 and city in cell G1.
- Select cell range F1:G13.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Table" button.
- Press with left mouse button on "My tables has headers".
- Press with left mouse button on OK.
5.3 Enter Excel Table formula
- Select cell F2.
- Type:
=Table1[@Month]
and press Enter.
- Select cell G2.
- Type:
=INDEX(Table1[@[London]:[Los Angeles]],MATCH($C$25,Table1[[#Headers],[London]:[Los Angeles]],0))
- Press Enter.
Charts category
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
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 […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
Drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
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. […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Interactive category
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
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 […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
Functions in this article
More than 1300 Excel formulas
Excel categories
3 Responses to “How to filter chart data”
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.
Your works are wonderful
Is there a way to use this method with pivot tables? I'm having some difficulty updating the structured references of the normal tables to those of pivot tables.
Hi,
I tried the method- Use a named range to change chart data
In my case I am also using some slicers on the data. It worked perfectly. But when I save and close the file, and then reopen again -- the "series values" of the chart changes from 'sheet_name!data' to 'file_name!data'. Due to this when selecting a different value in the slicer, the data in the table changes but not in the chart, the chart remains constant no matter what i select in the slicer.