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.
- 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. If you have Excel 2013, you can use Slicers on your Ctrl+T table in addition to Pivot Tables.
After building a pivot table, choose Insert Slicers. You can choose as many fields as you want from the current pivot table.
- 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.
- 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.
- 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.
- 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.