Creating Dynamic Worksheets by Using PivotTables in Microsoft Excel 2010

6/2/2010

Contents

Analyzing Data Dynamically by Using PivotTables
Filtering, Showing, and Hiding PivotTable Data
Editing PivotTables
Formatting PivotTables
Creating PivotTables from External Data
Key Points

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 a PivotTable named PivotTable2 that summarizes package volumes for every Consolidated Messengers regional distribution hub.

Excel displays the PivotTable name on the Options contextual tab, in the PivotTable Options group. The name PivotTable2 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 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, on 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 determines the best way to summarize the data in the column you assign to the Values area. For numeric data, for example, Excel 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 Values 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 Values By page 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 page 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.

If you want, you can create a formula that incorporates a value from a PivotTable cell. To do so, you click the cell where you want to create the formula, type an equal sign, and then click the cell in the PivotTable that contains the data you want to appear in the other cell. A GETPIVOTDATA formula appears in the formula box of the worksheet that contains the PivotTable. When you press Enter, Excel creates the GETPIVOTDATA formula and displays the contents of the PivotTable cell in the target 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 formula that incorporates a value in 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 and press Enter.

Excel renames the PivotTable.
On the Design contextual tab, in the Layout group, click Subtotals, and then click Do Not Show Subtotals.

Excel 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 removes the cells that calculate each row’s grand total.
On the Quick Access Toolbar, click the Undo button.

Excel reverses the last change.
Right-click any data cell in the PivotTable, point to Summarize Values By, and then click Average.

Excel changes the Value field summary operation.
On the Quick Access Toolbar, click the Undo button.

Excel reverses the last change.
Right-click any data cell in the PivotTable, and then click Value Field Settings.

The Value Field Settings dialog box opens.
Click the Show Values As tab.

The Show Values As page appears.
In the Show Values As list, click % of Row Total.
Click OK.

Excel changes how it calculates the values in the PivotTable.
On the Quick Access Toolbar, click the Undo button.

Excel 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 displays subtotals in the workbook.
Click the Package Summary sheet tab.

The Package Summary worksheet appears.
In cell C4, type =, but do not press Enter.
Click the PivotTable sheet tab.

The PivotTable worksheet appears.
Click cell K32, and then press Enter.

Excel creates the formula =GETPIVOTDATA(“Volume”,PivotTable!$A$3,"Year”,2010) in cell C4.