Creating Dynamic Lists by Using PivotTables in Microsoft Office Excel 2007
- 1/3/2007
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, you can choose the summary operation you want to use.
You can also change how the PivotTable displays the data in the Values area. On the Show Values As tab of the Value Field Settings dialog box, you can 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.
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. A GETPIVOTDATA formula appears in the formula box of the worksheet with the PivotTable. When you press Enter, the contents of the PivotTable cell appear in the linked cell.
In this exercise, you will 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.
On the PivotTable worksheet, click any cell in the PivotTable.
On the Options contextual tab, in the PivotTable group, in the PivotTable Name field, type VolumeSummary.
Excel 2007 renames the PivotTable.
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.
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.
On the Quick Access Toolbar, click the Undo button.
Excel 2007 reverses the last change.
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.
On the Quick Access Toolbar, click the Undo button.
Excel 2007 reverses the last change.
Right-click any data cell in the PivotTable and then click Value Field Settings.
The Value Field Settings dialog box appears.
Click the Show values as tab.
The Show values As tab appears.
In the Show values as list, click % of row.
Click OK.
Excel 2007 changes how it calculates the values in the PivotTable.
On the Quick Access Toolbar, click the Undo button.
Excel 2007 reverses the last change.
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.
Click the Package Summary sheet tab.
The Package Summary worksheet appears.
In cell C4, type =, but do not press
.Click the PivotTable sheet tab.
The PivotTable worksheet appears.
Click cell K32 and then press
.Excel 2007 creates the formula =GETPIVOTDATA(“Volume”,PivotTable!$A$3,"Year”, 2007) in cell C4.
