Excel 2007 For Dummies
Book image
Explore Book Buy On Amazon

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.

About This Article

This article is from the book:

About the book author:

Greg Harvey has authored tons of computer books, the most recent being Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies, and the most popular being Excel 2003 For Dummies and Excel 2003 All-In-One Desk Reference For Dummies. He started out training business users on how to use IBM personal computers and their attendant computer software in the rough and tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of the last century. After working for a number of independent training firms, Greg went on to teach semester-long courses in spreadsheet and database management software at Golden Gate University in San Francisco.
His love of teaching has translated into an equal love of writing. For Dummies books are, of course, his all-time favorites to write because they enable him to write to his favorite audience: the beginner. They also enable him to use humor (a key element to success in the training room) and, most delightful of all, to express an opinion or two about the subject matter at hand.
Greg received his doctorate degree in Humanities in Philosophy and Religion with a concentration in Asian Studies and Comparative Religion last May. Everyone is glad that Greg was finally able to get out of school before he retired.

This article can be found in the category: