Modifying and Pivoting Fields in an Excel 2007 Pivot Table - dummies

Modifying and Pivoting Fields in an Excel 2007 Pivot Table

By Greg Harvey

Pivot tables are much more dynamic than standard Excel 2007 tables because they are so easy to manipulate and modify. Excel makes it just as easy to change which fields from the original data source are displayed in the table as it is to add them when a table is first created. In addition, you can instantly restructure the pivot table by dragging its existing fields to new positions on the table.

Modifying the pivot table fields

To modify the fields used in your pivot table, follow these steps:

  1. Click any cell in the pivot table.

    Excel adds the PivotTable Tools contextual tab with the Options and Design tabs to the Ribbon.

  2. Click the Options tab under the PivotTable Tools contextual tab.

  3. Click the Field List button in Show/Hide group if it isn’t already selected.

    Excel displays the PivotTable Field List task pane, showing the fields that are currently used in the pivot table as well as to which areas they’re currently assigned.

  4. Make any of the following modifications to the table’s fields:

    • To remove a field, drag its field name out of any of its drop zones (Report Filter, Column Labels, Row Labels, and Values) and, when the mouse pointer changes to an X, release the mouse button; or click its check box in the Choose Fields to Add to Report list to remove its check mark.

    • To move an existing field to a new place in the table, drag its field name from its current drop zone to a new zone at the bottom of the task pane.

    • To add a field to the table, drag its field name from the Choose Fields to Add to Report list and drop the field in the desired drop zone— note that if you want to add a field to the pivot table as an additional Row Labels field, you can also do this by simply selecting the field’s check box in the Choose Fields to Add to Report list.

Pivoting the table’s fields

As the name pivot implies, the fun of pivot tables is being able to restructure the table simply by rotating the Column and Row fields. In the PivotTable Field List pane, simply drag a label from the Row Labels drop zone to the Column Labels drop zone and vice versa so that the two field names are swapped. Voilà — Excel rearranges the data in the pivot table at your request.

You also can switch Column and Row fields by dragging their labels to their new locations directly in the pivot table itself. Before you can do that, however, you must select the Classic PivotTable Layout check box on the Display tab of the PivotTable Options dialog box (opened by clicking the Options button on the Options tab underneath the PivotTable Tools contextual tab). This check box might already be selected by default.