Editing PivotTables

After you create a PivotTable, you can rename it, edit it to control how it summarizes your data, and use the PivotTable cell data in a formula. As an example, consider the following PivotTable.

Excel 2007 displays the PivotTable name on the Options contextual tab, in the PivotTable Options group. The name PivotTable5 doesn't help you or your colleagues understand the data the PivotTable contains, particularly if you use the PivotTable data in a formula on another worksheet. To give your PivotTable a more descriptive name, click any cell in the PivotTable and then, on the Options contextual tab, in the PivotTable Options group, type the new name in the PivotTable Name field.

When you create a PivotTable with at least one field in the Row Labels area and one field in the Column Labels area of the PivotTable Field List task pane, Excel 2007 adds a grand total row and column to summarize your data. You can control how and where these summary rows and columns appear by clicking any PivotTable cell and then, in the Design contextual tab, in the Layout group, clicking either the Subtotals or Grand Totals button and selecting the desired layout.

After you create a PivotTable, Excel 2007 determines the best way to summarize the data in the column you assign to the Values area. For numeric data, for example, Excel 2007 uses the Sum function. If you want to change a PivotTable summary function, right-click any data cell in the PivotTable values area, point to Summarize Data By, and then click the desired operation. If you want to use a function other than those listed, click More Options to display the Value Field Settings dialog box.

On the Summarize By tab of the dialog box, click the summary operation you want to use and then click OK.

The Value Field Settings dialog box also enables you to change how the PivotTable displays the data in the Values area. If you click the Show Values As tab of the Value Field Settings dialog box, you can use the Show Values As list box to select whether to display each cell's percentage contribution to its column's total, its row's total, or its contribution to the total of all values displayed in the PivotTable.

In versions prior to Office Excel 2002, you couldn't create a link to a cell in a PivotTable. Now you can create a link from a cell in another workbook to a cell in your PivotTable. To create a link, you click the cell you want to link to your PivotTable, type an equal sign, and then click the cell in the PivotTable with the data you want linked. When you click the PivotTable cell, a GETPIVOTDATA formula appears in the formula bar of the worksheet with the PivotTable. When you press Enter, the contents of the PivotTable cell will appear in the linked cell.

In this exercise, you'll rename a PivotTable, specify whether subtotal and grand total rows will appear, change the PivotTable summary function, display each cell's contribution to its row's total, and create a link to a PivotTable cell.

USE the Editing workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\PivotTables folder.

OPEN the Editing workbook.

1.	On the PivotTable worksheet, click any cell in the PivotTable.
2.	On the Options contextual tab, in the PivotTable group, in the PivotTable Name field, type VolumeSummary. Excel 2007 renames the PivotTable to VolumeSummary.
3.	On the Design contextual tab, in the Layout group, click Subtotals and then click Do Not Show Subtotals. Excel 2007 removes the subtotal rows from the PivotTable.
4.	On the Design contextual tab, in the Layout group, click Grand Totals and then click On for columns only. Excel 2007 removes the cells that calculate each row's grand total.
5.	On the Quick Access Toolbar, click the Undo button. Excel 2007 reverses the last change.
6.	Right-click any data cell in the PivotTable, point to Summarize Data By, and then click Average. Excel 2007 changes the Value field summary operation.
7.	On the Quick Access Toolbar, click the Undo button. Excel 2007 reverses the last change.
8.	Right-click any data cell in the PivotTable and then click Value Field Settings. The Value Field Settings dialog box appears.
9.	Click the Show values as tab. The Show values as tab appears.
10.	Click the Show values as field down arrow and then click % of row.
11.	Click OK. Excel 2007 changes how it calculates the values in the PivotTable.
12.	On the Quick Access Toolbar, click the Undo button. Excel 2007 reverses the last change.
13.	On the Design tab, in the Layout group, click Subtotals and then click Show All Subtotals at Bottom of Group. Excel 2007 displays subtotals in the workbook.
14.	On the tab bar, click the Package Summary sheet tab. The Package Summary worksheet appears.
15.	In cell C4, type =, but do not press .
16.	On the tab bar, click the PivotTable sheet tab. The PivotTable worksheet appears.
17.	Click cell K32 and then press . Excel 2007 creates the formula =GETPIVOTDATA("Volume",PivotTable!$A$3,"Year", 2007) in cell C4.

CLOSE the Focusing workbook.