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

3

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

RUN MANY PIVOT TABLES FROM ONE SLICER

You can already see that slicers are better than Report Filters. Another major advantage is that you can filter many pivot tables from one set of slicers. This allows you to create dashboard-like reports.

When you create a pivot chart, the pivot table and chart are placed next to each other by Microsoft. They absolutely do not have to stay next to each other. You can build four pivot charts, each on their own sheet, then move the charts so that they are all on the same worksheet. You can move a chart using the Move

Chart icon on the Design tab, or simply select the chart, cut, then paste in a new location.

Here is the process for making the slicers drive all of your pivot tables:

1. Build the first pivot table or pivot chart. Add slicers to that pivot table.

2.Build additional pivot tables or pivot charts. Move the chart or table to be near the first pivot table.

3.While the second pivot table is selected, go to the Analyze tab. Open the dropdown attached to the

Insert Slicer icon and choose Slicer Connections. (If you have a Pivot Chart, the Slicer icon is on the Analyze ribbon tab.)

Figure 867 For the second pivot table, choose Slicer Connections.

4. Repeat steps 2 & 3 for each additional pivot table or pivot chart.

Below, slicers are driving two pivot charts and two pivot tables.

Figure 868 All pivot tables are filtered by the slicers.

FILTER DATES USING A TIMELINE IN EXCEL 2013 & NEWER

Excel 2013 introduced a new type of filter called a Timeline. It only works for date columns. You can choose to filter by Day, Month, Quarter, or Year.

Figure 869 Timelines are new in Excel 2013 pivot tables.

As in the previous topic, you can run many pivot tables from one Timeline.

Strategy: Build a pivot table with Revenue in the row labels. In order to get a count of the number of in- voices, move any text field to the values area. Add Revenue three more times to the Values area. You will get a pivot table that looks like this.

Figure 873 Arrange fields opposite of normal, numbers in the row labels and text in the values area.

Select the first revenue field in column D. Use the Field Settings icon and change the Show Values As to % of Column Total. Change the Field Settings for column E to Running % In. Change the headings in B3:E3 as shown below.

Select cell A4 and click Group Field on the Analyze ribbon tab. Enter starting, ending, and step values.

Perhaps from 1 to 30,000 in 5000 dollar increments. Click OK.

Figure 874 Group into equal-size buckets.

Result: a pivot table version of a frequency diagram, without ever having to figure out the FREQUENCY array-function.