Let’s take a look. Let’s create a pivot chart that shows product sales by Region. Since we have a pivot table already, we’ll create a pivot chart first, and then start customizing. Go to the Options tab of the PivotTable Tools ribbon and click the PivotChart button. For chart styles, we’ll use a simple column chart. At this point, our chart is plotting the full set of source data. Let’s filter the pivot chart to show only certain products: Banana Chocolate and Orange Chocolate. One way to do this is to add Product as a Report Filter. You can filter the data in a pivot chart directly using field buttons. If field buttons aren’t visible, navigate to the Analyze tab, and click Field Buttons to toggle them on. With the field buttons visible, we can manually filter to show only Orange and Banana Chocolate. Now the chart displays only the data for those 2 products. If we check the pivot table, we see it is filtered in exactly the same way. In fact, because the pivot table and the pivot chart are linked together, you might want to turn off the Chart buttons, and just use the pivot table to control filtering. This will give your chart a much cleaner look, but you might want to label the chart to make active filters clear. Using the pivot table, you can filter the chart in any way you like. For example, let’s exclude the East region from this chart. To clear filters, you can use the Clear Filters command that appears in each Field Button menu. Or, to clear all filters at once, click the Clear button on the Analyze tab, and choose Clear Filters. All filters will be removed from both the pivot table and pivot chart.
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.
