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.
CLOSE the Focusing workbook.