- Excel Book Excerpt
Excel Add Visual Filters to a Pivot Table
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Add Visual Filters to a Pivot Table
Problem: Excel 2007 added the ability to select multiple items from a filter. But when I do this, it uses the ambiguous (Multiple Items) heading. When I print this report, no one knows which customers are in the report.
Figure 861 When you select two customers from the filter dropdown, you can’t see which customers are selected.
Strategy: That addition in Excel 2007 was a first step towards the full visual filters called Slicers in Excel 2010. If you have Excel 2010, you can see which fields are included or not included.
After building a pivot table, choose Insert Slicers. You can choose as many fields as you want from the current pivot table.
Figure 862 Select fields to use as visual filters.
Initially, Excel tiles all of the slicers and shows them with one column. Here is the default arrangement of four slicers.
Figure 863 By default, the slicers are tiled.
You will want to rearrange and resize the slicers. You can move and resize the slicers. In the Slicer Tools ribbon tab, use the Columns spinbutton to add more columns to a slicer. As you can see below, product and region can fit in a single row by increasing the number of columns to three or four.
Figure 864 Rearrange and resize the slicers.
I also use a different color for each slicer. This is controlled in the Slicer Tools ribbon tab as well.
Once you have the slicers, you can choose from any slicer. The pivot table will update to reflect the filters. The other slicers will also update. Below, after choosing Manufacturing in the East & Central regions, several non-manufacturing customers are “greyed out” at the bottom of the customer slicer.
Figure 865 AIG is greyed out since it isn’t in the Manufacturing sector.
Gotcha: I found it tough to select multiple items from one slicer. If you happen to need adjacent items, you can click on one and drag across to the next item. But, if you want to select ABC and XYZ, you have to choose ABC, then Ctrl+Click XYZ.
For more resources for Microsoft Excel: