MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

You can disband a group by selecting Group And Show Detail and then Ungroup.

You can group nonadjacent selections by holding down the Ctrl key while you select nonadjacent rows or columns.

With numerical values or dates in a row field, you can group by number or dates in arbitrary intervals. For example, you can create groups for age ranges and then find the average income for all 25–34 year olds.

What is a calculated item?

A calculated item works just like a calculated field except that you are creating one row rather than a column. To create a calculated item, you should select an item in the row area of the PivotTable, not an item in the body of the PivotTable. Then from the Options tab, select Formulas, followed by Calculated Item. See Problem 11 in the “Problems” section of this chapter for an example of creating a calculated item. In our chip PivotTable example we could not create a calculated item because we had multiple copies of the Revenue field.

What is “drilling down”?

“Drilling down” is when you double-click a cell in a PivotTable to display all the detailed data that’s summarized in that field. For example, double-clicking any March entry in the microchip scenario will display the data that’s related to March sales.

I often have to use specfic data in a PivotTable to determine profit, such as the April sales of Chip 1 in France. Unfortunately, this data moves around when new fields are added to my PivotTable. Does Excel have a function that enables me to always pull April’s Chip 1 sales in France from the PivotTable?

Yes, there is such a function. The GETPIVOTDATA function fills the bill. Suppose that you want to extract sales of Chip 1 in France during April from the PivotTable contained in the file Getpivotdata.xlsx. (See Figure 38-45.) Entering in cell E2 the formula GETPIVOTDATA(A4,"April France Chip 1 Sum of Revenue") yields the correct value ($37,600) even if additional products, countries, and months are added to the PivotTable later. We can also obtain the resulting revenue by simply pointing to the cell containing Chip 2 April sales in France (cell D24).

Figure 38-45: Use the GETPIVOTDATA function to locate April Chip 1 Sales in France.

The first argument for this function is in the upper-left corner of the PivotTable (cell A4). We enclose in quotation marks (separated by spaces) the PivotTable headings that define the entry we want. The last entry must specify the data field, but other headings can be listed in any order. Thus, our formula means “For the PivotTable whose upper-left corner is in cell A4, find the Sum of Revenue for Chip 1 in France during April.” This formula will return the correct answer even if the sales data for Chip 1 in France in April moves to a different location in the PivotTable.

If you want to simply return total revenue ($1,026,278), you could enter the formula (see cell F2) GETPIVOTDATA(A4,"Sum of Revenue").

Often, the GETPIVOTDATA function is a nuisance. Suppose you want to refer to data in cells B5:B11 from a PivotTable elsewhere in your workbook. You would probably use the formula =B5 and copy it to the range B6:B11. Hopefully, this would extract B6, B7,…, B11 to desired cells. Unfortunately, if the GETPIVOTDATA option is active, you will get a bunch of GETPIVOTDATA functions that refer to the same cell. If you want to turn off GETPIVOTDATA, you can click the Microsoft Office Button and click Excel Options. Then select Formulas, and under Working With Formulas, clear the GetPivotdata Function For PivotTable References. This will ensure that clicking inside a PivotTable yields a formula like =B5 rather than a GETPIVOTDATA function.

Finally, we note that you can also combine the MATCH and OFFSET functions (explained in Chapter 4, “The MATCH Function,” and Chapter 20, “The OFFSET Function,” respectively) to extract various PivotTable entries.