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.
Built-in Charts
Combo Charts
Combined stacked area and a clustered column chartCombined chart – Column and Line on secondary axis
Combined Column and Line chart
Chart elements
Chart basics
How to create a dynamic chartRearrange data source in order to create a dynamic chart
Use slicers to quickly filter chart data
Four ways to resize a chart
How to align chart with cell grid
Group chart categories
How to add lines between stacked columns/bars [Excel charts]
Custom charts
How to build an arrow chartHow to graph a Normal Distribution
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template
Sparklines
Win/Loss Column LineHighlight chart elements
Highlight a column in a stacked column chartHighlight a group of chart bars
Highlight a data series in a line chart
Highlight a column in a stacked column chart
Highlight a bar in a chart
Interactive charts
How to filter chart dataHover with mouse cursor to change stock in a candlestick chart
How to build an interactive map in Excel
Highlight group of values in an x y scatter chart programmatically
Use drop down lists and named ranges to filter chart values
How to use mouse hover on a worksheet [VBA]
How to create an interactive Excel chart [VBA]
Change chart series by clicking on data [VBA]
Change chart data range using a Drop Down List [VBA]
How to create a dynamic chart
Animate
Line chart Excel Bar Chart Excel chartAdvanced charts
Custom data labels in a chartImprove your X Y Scatter Chart with custom data labels
Label line chart series
How to position month and year between chart tick marks
How to add horizontal line to chart
Add pictures to a chart axis
How to color chart bars based on their values
Excel chart problem: Hard to read series values
Build a stock chart with two series
Change chart axis range programmatically
Change column/bar color in charts
Hide specific columns programmatically
Dynamic stock chart
How to replace columns with pictures in a column chart
Color chart columns based on cell color
Heat map using pictures
Dynamic Gantt charts
Stock charts
Build a stock chart with two seriesDynamic stock chart
Change chart axis range programmatically
How to create a stock chart
Excel categories
One Response to “How to create a stock chart”
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.
[…] Learn how to create a stock chart in excel […]