When plotting unsummarized data, the chart filter may not be convenient. For example, here we have historical stock data for Microsoft covering more than 15 years in more than 100 rows of data. If I insert a line chart, Excel automatically builds a condensed horizontal axis and plots all the data. And I can easily use the Chart Filters menu to exclude everything but the close price. But what if I only want to show data from 2016 and 2017? The way the entries are listed in the filter, I’ll have to uncheck a lot of checkboxes. This is a case where it makes sense to add a filter to the data itself, and then use that to exclude the data we don’t want. One way to apply a filter is to click the Filter menu on the Data tab. You can use the keyboard shortcut control + shift + L. Note that you can toggle the filter on and off. Another way to add a filter is to convert the data to an Excel table with the keyboard shortcut control T, which automatically applies a filter. Once you have a filter, you’ll want to set properties on the chart to make sure it won’t move or resize automatically. In the Format Task pane, under chart properties select Don’t move or size with cells. This is important, because we’ll soon be hiding a lot of rows, and the chart may shrink or disappear if we don’t unlink it from the grid. With this done, I can now use the filter on the data itself to set the date range. The table filter will automatically roll up dates up by year, so it’s easy to include just 2016 and 2017. When I click OK, the chart is updated. So this works because Excel ignores data in hidden rows and columns by default, and the filter works by hiding rows. If I check the chart filter, you can see its still being used to exclude everything but the close price. But the date range is being handled by the filter on the data itself. So, entries before 2016, simply don’t appear. So in summary, filtering the data directly is good way to filter out a large amount unsummarized data. Pivot Charts also provide another good way to do this. We’ll cover Pivot Charts separately in upcoming videos.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
