Figure 856 The report shows only sales for East.

To produce the report for Central, you simply change the Region dropdown from East to Central. You can repeat for each other region.

Alternate Strategy: The Slicer feature introduced in Excel 2010 produce a modern way to filter a pivot table. See "Add Visual Filters to a Pivot Table or Regular Table" on page 335.

Figure 857 Ad hoc reporting tool.

CREATE A REPORT FOR EVERY CUSTOMER

Problem: I need to print a report for each of my customers. Using the Report Filter field is tedious: I spend my whole morning selecting a customer, clicking Print, selecting a customer, clicking Print, and so on.

Strategy: The feature you use to solve this problem—the Show Report Filter Pages command—is the most powerful feature of pivot tables. I don’t know why Microsoft buries it so deeply in the menu system. You can use the Show Report Filter Pages command to make a report for every customer. Follow these steps:

1. Build a pivot table with the information you want to replicate for each customer. 2. Add the Customer field as one of the Report Filter fields.

3. Select PivotTable Tools Analyze, Options dropdown, Show Report Filter Pages. Gotcha: Don’t click on the big Options icon. Click on the tiny dropdown next to the Options icon.

Figure 858 Set up the report and select Show Report Filter Pages.

4. A dialog box will appear, asking you to show all Report Filter Pages and giving you a list of all the fields in the Report Filter. Even though this seems silly when you have only one field in the Report

Filter area, choose Customer and click OK.

Results: In a matter of seconds, Excel will add a new worksheet for each customer. Each worksheet will be named after the customer, and the Customer dropdown will be changed to the particular customer. In a matter of seconds, you will have one worksheet for each customer.

Figure 859 One report per customer.

Gotcha: Get the pivot table perfect before making hundreds of copies. Even though I thought I did a lot of formatting, I forgot to replace blanks with zeroes in the above figure. At this point, I would delete the customer worksheets, change the original pivot table, then use Show Pages again.

You can imagine that this feature could be useful if you need one report per department, one report per product, etc.

CREATE PIVOT CHARTS

Problem: Can I show the results of a pivot table in a chart?

Strategy: Pivot charts have improved to the point where they are actually usable. Here’s what you do: 1. Select a single cell in your data. Select Insert, PivotTable dropdown, PivotChart.

2. Build a pivot table by using the Field List dialog. Note that the row fields are now called axis fields.

Put Region in the Axis Fields drop zone.

3. Column fields are now called legend fields. Put Product in the Legend Field drop zone.

4. Add Customer to the Report Filter drop zone.

5. Add Revenue to the ∑ Values drop zone.

Excel will show both a pivot table and a chart on the worksheet. When you select the chart, you can use the PivotChart Tools tabs on the ribbon to control the chart type and all formatting.

Figure 862 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. 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.

Figure 863 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 864 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.