Chapter 35: Analyzing Data with Pivot Tables

A Reverse Pivot Table

The Excel Pivot Table feature creates a summary table from a list. But what if you want to perform the opposite operation? Often, you may have a two-way summary table, and it would be convenient if the data were in the form of a list.

In the figure here, range A1:E13 contains a summary table with 48 data points. Notice that this summary table is similar to a pivot table. Column G:I shows part of a 48-row table that was derived from the summary table. In other words, every value in the original summary table gets converted to a row, which also contains the region name and month. This type of table is useful because it can be sorted and manipulated in other ways. And, you can create a pivot table from this transformed table.

The companion CD-ROM contains a workbook, reverse pivot.xlsm, which has a macro that will convert any two-way summary table into a three-column normalized table.

Filtering Pivot Tables with Slicers

A Slicer is an interactive control that makes it easy to filter data in a pivot table. Figure 35.20 shows a pivot table with three Slicers. Each Slicer represents a particular field. In this case, the pivot table is displaying data for New customers, opened by tellers at the Westside branch.

731

Part V: Analyzing Data with Excel

New Feature

Slicers are new to Excel 2010. n

The same type of filtering can be accomplished by using the field labels in the pivot table, but Slicers are intended for those who might not understand how to filter data in a pivot table. Slicers can also be used to create an attractive and easy-to-use interactive “dashboard.”

FIGURE 35.20

Using Slicers to filter the data displayed in a pivot table.

To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table. Then choose Insert Filter Slicer. The Insert Slicers dialog box appears, with a list of all fields in the pivot table. Place a check mark next to the Slicers you want, and then click OK.

Slicers can be moved and resized, and you can change the look. To remove the effects of filtering by a particular Slicer, click the icon in the Slicer’s upper-right corner.

To use a Slicer to filter data in a pivot table, just click a button. To display multiple values, press Ctrl while you click the buttons in a Slicer.

Figure 35.21 shows a pivot table and a pivot chart. Two Slicers are used to filter the data (by state and by month). In this case, the pivot table (and pivot chart) shows only the data for Missouri for the months of January through March. Slicers provide quick and easy way to create an interactive chart.

On the CD

This workbook, named pivot chart slicer.xlsx, is available on the companion CD-ROM. n

732

Chapter 35: Analyzing Data with Pivot Tables

FIGURE 35.21

Using Slicers to filter a pivot table by state and by month.

Referencing Cells within a Pivot Table

After you create a pivot table, you may want to create a formula that references one or more cells within a pivot table. Figure 35.22 shows a simple pivot table that displays income and expense information for three years. In this pivot table, the Month field is hidden, so the pivot table shows the year totals.

On the CD

This workbook, named income and expenses.xlsx, is available on the companion CD-ROM. n

FIGURE 35.22

The formulas in column F reference cells in the pivot table.

733

Part V: Analyzing Data with Excel

Column F contains formulas, and this column is not part of the pivot table. These formulas calculate the expense-to-income ratio for each year. I created these formulas by pointing to the cells. You may expect to see this formula in cell F5:

=D5/C5

In fact, the formula in cell F5 is

=GETPIVOTDATA(“Sum of Expenses”,$A$3,”Year”,2007)/GETPIVOTDATA

(“Sum of Income”,$A$3,”Year”,2007)

When you use the pointing technique to create a formula that references a cell in a pivot table, Excel replaces those simple cell references with a much more complicated GETPIVOTDATA function. If you type the cell references manually (rather than pointing to them), Excel does not use the

GETPIVOTDATA function.

The reason? Using the GETPIVOTDATA function helps ensure that the formula will continue to reference the intended cells if the pivot table layout is changed. Figure 35.23 shows the pivot table after expanding the years to show the month detail. As you can see, the formulas in column F still show the correct result even though the referenced cells are in a different location. Had I used simple cell references, the formula would return incorrect results after expanding the years.

Caution

Using the GETPIVOTDATA function has one caveat: The data that it retrieves must be visible. If you modify the pivot table so that the value returned by GETPIVOTDATA is no longer visible, the formula returns an error. n

Tip

If, for some reason, you want to prevent Excel from using the GETPIVOTDATA function when you point to pivot table cells when creating a formula, choose PivotTable Tools Options PivotTable Options Generate GetPivot Data. (This command is a toggle.) n

734