Here we have a simple summary of sales by quarter in 4 regions, with totals for each month, and quarterly totals as well. Now, if I select any cell in the data, and insert a chart, Excel includes the totals, which you probably don’t want. So let’s go over some ways to exclude this data. First, you can always exclude data is when the chart is created. If I make a selection that excludes monthly totals, and then insert a chart, they aren’t plotted. And, I can extend this approach to exclude the quarterly totals as well. I just need to hold down the control key and make separate selections. So, selecting specific data first is a handy way to plot just the data you want. Let me undo these changes, and let’s look at another way to filter the data. So, once once you have a chart, you can use the data selection range on the worksheet to include or exclude data. It works really well when all the data together in adjacent cells. However, you can’t exclude cells that aren’t contiguous. For example, I can’t use this technique to remove the first quarterly total. Let me undo those changes and try again. Another way to filter out data in a chart is to hide rows and columns manually. This works because, by default, Excel ignores data in hidden rows or columns. It’s a little cumbersome, but it’s simple and effective. Where hiding rows and columns is especially useful is when you’re using a filter to hide rows in a table, and we’ll look at that in another video. Now, starting with Excel 2013, you can take a different approach using something called chart filters. With chart filters, you typically include all the data and then filter out what you don’t want. Now I just select the chart, and click the filter icon. A fly out menu opens and, as you hover over items with your mouse, Excel will highlight each item in the chart. Simply uncheck those items you want to remove, and click apply. Note that filtering data this way has no affect on the source data, it only affects the data visible in the chart. Notice when you’ve used the chart filters menu, the worksheet will still highlight all of the data. However, the Select Data dialog will show what data is included and excluded. And if I check or uncheck a box here, the Chart Filters menu changes to stay in sync.
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.
