Excel 2007 For Dummies
Book image
Explore Book Buy On Amazon

You can filter and sort the data in an Excel 2007 pivot table to display a subset of data arranged how you want to view it. Excel automatically adds drop-down filter arrows to the Report Filter field as well as the labels for the Column and Row fields. These filter arrows enable you to filter out all but certain entries in any of these fields, and in the case of the Column and Row fields, to sort their entries in the table.

If you’ve added more than one Column or Row field to your pivot table, Excel adds collapse buttons (-) that you can use to temporarily hide subtotal values for a particular secondary field. After clicking a collapse button in the table, it immediately becomes an expand button (+) that you can click to redisplay the subtotals for that one secondary field.

Filter a pivot table report

Perhaps the most important filter buttons in a pivot table are the ones added to the Report Filter field(s). By selecting a particular option on the drop-down lists attached to one of these filter buttons, only the summary data for that subset you select is then displayed in the pivot table itself.

When you filter a pivot table field, Excel replaces the standard drop-down button icon with a cone-shaped filter icon, indicating that the field is currently being filtered to show only some of the values in the data source.

Filtering individual Column and Row fields

The filter buttons attached to the Column and Row field labels let you filter out entries for particular groups and, in some cases, individual entries in the data source. To filter the summary data in the columns or rows of a pivot table, follow these steps:

  1. Click the Column or Row field’s filter button.

  2. Deselect the check box for the (Select All) option at the top of the list box in the drop-down list.

  3. Click the check boxes for all the groups or individual entries whose summed values you still want displayed in the pivot table.

  4. Click OK.

    As with filtering a Report Filter field, Excel replaces the standard drop-down button icon for that Column or Report field with a cone-shaped filter icon, indicating that the field is currently being filtered and only some of its summary values are now displayed in the pivot table.

    Pivot table after filtering two fields in the table.
    Pivot table after filtering two fields in the table.

To redisplay all the values for a filtered Column or Report field, you need to click its filter button and then click (Select All) at the top of its drop-down list. Then click OK.

Sort a pivot table

You can instantly reorder the summary values in a pivot table by sorting the table on one or more of its Column or Row fields. To sort a pivot table, follow these steps:

  1. Click the filter button for the Column or Row field you want to sort.

  2. Click either the Sort A to Z option or the Sort Z to A option at the top of the field’s drop-down list.

Click the Sort A to Z option when you want the table reordered by sorting the labels in the selected field alphabetically or in the case of values from the smallest to largest value or in the case of dates from the oldest to newest date. Click the Sort Z to A option when you want the table reordered by sorting the labels in reverse alphabetical order (Z to A), values from the highest to smallest, and dates from the newest to oldest.

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: