Create a Pivot Table in Excel 2007

1 of 10 in Series: The Essentials of Working with Pivot Tables and Pivot Charts in Excel 2007

A pivot table is a special type of summary table that’s totally unique to Excel 2007. Pivot tables are great for summarizing values in a table because they do their magic without making you create formulas to perform the calculations. Pivot tables also let you play around with the arrangement of the summarized data. It’s this capability of changing the arrangement of the summarized data on the fly simply by rotating row and column headings that gives the pivot table its name.

  1. Open the worksheet that contains the table you want summarized by the pivot table, and select any cell in the table.

    Ensure that the table has no blank rows or columns and that each column has a heading.

  2. On the Insert tab, click the PivotTable command button in the Tables group.

    Click the top portion of the button; if you click the arrow, click PivotTable in the drop-down menu. Excel opens the Create PivotTable dialog box and selects all the table data as indicated by a marquee around the cell range.

  3. Adjust the range in the Table/Range text box under the Select a Table or Range option button, if necessary.

  4. Select the location for the pivot table.

    By default, Excel builds the pivot table on a new worksheet it adds to the workbook. If you want the pivot table to appear on the same worksheet, click the Existing Worksheet option button and then indicate the location of the first cell of the new table in the Location text box.

    Indicate the data source and pivot table location in the Create PivotTable dialog box.
    Indicate the data source and pivot table location in the Create PivotTable dialog box.
  5. Click OK.

    Excel adds a blank grid for the new pivot table and displays a PivotTable Field List task pane on the right side of the worksheet area.

    The Field List task pane is divided into two areas: the Choose Fields to Add to Report list box with the names of all the fields in the source data for the pivot table, and an area divided into four drop zones (Report Filter, Column Labels, Row Labels, and Values) at the bottom.

    New pivot table displaying the blank table grid and the PivotTable Field List task pane.
    New pivot table displaying the blank table grid and the PivotTable Field List task pane.
  6. To complete the pivot table, assign the fields in the PivotTable Field List task pane to the various parts of the table. You do this by dragging a field name from the Choose Fields to Add to Report list box and dropping it in one of the four areas below called drop zones:

    • Report Filter: This area contains the fields that enable you to page through the data summaries shown in the actual pivot table by filtering out sets of data — they act as the filters for the report. So, for example, if you designate the Year Field from a table as a Report Filter, you can display data summaries in the pivot table for individual years or for all years represented in the table.

    • Column Labels: This area contains the fields that determine the arrangement of data shown in the columns of the pivot table.

    • Row Labels: This area contains the fields that determine the arrangement of data shown in the rows of the pivot table.

    • Values: This area contains the fields that determine which data are presented in the cells of the pivot table — they are the values that are summarized in its last column (totaled by default).

  7. Continue to manipulate the pivot table as needed until the desired results display.

    Completed pivot table after adding the fields from the employee table to the various drop zones.
    Completed pivot table after adding the fields from the employee table to the various drop zones.

As soon as you create a new pivot table (or select a cell of an existing table in a worksheet), Excel displays the Options tab of the PivotTable Tools contextual tab. Among the many groups on this tab, you find the Show/Hide group that contains the following useful command buttons:

  • Field List to hide and redisplay the PivotTable Field List task pane on the right side of the Worksheet area.

  • +/- Buttons to hide and redisplay the expand (+) and collapse (-) buttons in front of particular Column Fields or Row Fields that enable you to temporarily remove and then redisplay their particular summarized values in the pivot table.

  • Field Headers to hide and redisplay the fields assigned to the Column Labels and Row Labels in the pivot table.

blog comments powered by Disqus

SERIES
The Essentials of Working with Pivot Tables and Pivot Charts in Excel 2007

Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.