Microsoft Excel 2007: Working with PivotTables (Win

Working with PivotTables

When your Excel Table or data range accumulates large amounts of mixed data, you need a way to identify the key trends and anomalies that exist deep within the data. For this, Excel 2007 offers the PivotTable report, a powerful tool designed to perform this very task. A PivotTable report provides a dynamic summary of an existing Table or data range that can be quickly expanded, collapsed, and rearranged to give you several different perspectives on your data.
NOTE: This document provides a brief overview of creating a PivotTable from existing data, and then using the PivotTable Field List to create different views of the data. There are many advanced features related to PivotTables (e.g., PivotCharts and the PivotTable Options and Design tabs) which are beyond the scope of this document.

• Notes on Working with PivotTables
• Creating a PivotTable
• Creating a PivotTable Report
• The PivotTable Field List Illustrated
• PivotTable Tools

Notes on Working with PivotTables

• As with Tables, PivotTables need first row column labels to determine how your data should be grouped
• PivotTables are most helpful for analyzing complex Tables and data ranges (e.g., those with three or more columns, with at least one column containing multiple data types)
• Each column in your data source becomes a PivotTable field, which summarizes its corresponding rows
• The initial PivotTable report is an empty shell; with this shell in place, you can add, remove, rearrange, and modify data using the PivotTable Field List or tools found in the Options and Design command tabs
• As you make changes in a PivotTable report, your data source remains untouched and completely secure
• The PivotTable Options and Design command tabs appear only when a PivotTable is active
• Before creating a PivotTable, be sure to disable any existing subtotals in your data source; PivotTables generate their own totals and subtotals.

Creating a PivotTable

Creating a PivotTable report from an existing Table or data range is easy. For information on Tables, refer to Tables Overview and Creating Tables.

1. Select a cell within the Table or range for which you are creating a PivotTable
2. From the Insert command tab, in the Tables group, click INSERT PIVOTTABLE
   The Create PivotTable dialog box appears
   
3. In the Choose the data that you want to analyze section, select Select a table or range
4. In the Table/Range text box, type the cell range (or range name) for which you want to create a PivotTable report
5. To place the PivotTable in a new worksheet, in the Choose where you want the PivotTable report to be placed section, select New Worksheet
   To place the PivotTable in the active worksheet,
   1. In the Choose where you want the PivotTable report to be placed section, select Existing Worksheet
   2. In the Location text box, type cell or range where you want the PivotTable placed
6. Click OKAn empty PivotTable appears in the specified location.
   The PivotTable Field List appears.
   

Creating a PivotTable Report

The PivotTable Field List lets you select which fields appear in your PivotTable report, and also where and how they are displayed. You can easily display or hide fields, and change how field data are viewed, sorted, or filtered.
When you select a field in the PivotTable Field List, Excel analyzes the data it contains and automatically assigns it to one of four categories: Report Filter, Column Labels, Row Labels, or Values. If, for example, a field contains numerical data, Excel likely will add it to the Values category; if it contains text, it will probably be displayed in either the Row Labels or Column Labels category. However, the strength (and the purpose) of PivotTables lies in the ease with which you can maneuver fields between these various categories.

1. From the PivotTable Field List, in the Choose fields to add to report section, select the check box next to the field you want to display
   Excel displays the selected field in a default area of the PivotTable Field List and its field data in the corresponding area of the PivotTable report.
2. Repeat step 1 for all desired fields

Customizing a PivotTable Report

By moving fields among different Field List categories, the corresponding PivotTable report changes accordingly. For a visual example, refer to The PivotTable Field List Illustrated.

1. In the PivotTable Field List category currently displaying the field, position the mouse over the field label
   The pointer becomes a four-headed arrow.
2. Click and drag the field label into the new category
3. Release the mouse button
   The field is added to the new category of the PivotTable Field List.
   The PivotTable report changes accordingly.
   NOTE: If a PivotTable Field List category contains multiple fields, the lower fields are displayed in the PivotTable report as cascading sub-entries of the top field.

The PivotTable Field List Illustrated

The graphics below illustrate how fields selected in a PivotTable Field List are displayed in the PivotTable report.

• The original data source is shown in fig. 1; the empty PivotTable Field List is shown in fig. 2.
• To understand the relationship between a data source, the PivotTable Field List, and the PivotTable report, follow the blue arrow in fig. 3 from the Class field of the Choose fields to add to report area to its default category, Row Labels, and then the red arrows in fig. 3 to the data as displayed in fig. 4.
• Notice that the Year field (i.e., column in the data source) is not selected and does not appear in any PivotTable Field List category or in the PivotTable report.
• Also see how the arrangement of the Class and Semester fields in the PivotTable Field List correlates in the PivotTable report.

PivotTable Tools

Once a PivotTable Report has been created, the Options and Design tabs appear on the Ribbon, under the PivotTable Tools heading. From these tabs you can sort, filter, format, and move your PivotTable report.
NOTE: For the PivotTable Tools (i.e., the Options and Design tabs) to be visible, the PivotTable report must be active (i.e., selected).

Make sure to let me know in the comments below or on our

Facebook page how you've got with it or Do you have any question