About PivotTables

A PivotTable report is an interactive tool that combines and compares data. You can rotate its rows and columns to see different summaries of the source data and display the details for areas of interest. Use a PivotTable when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. Because a PivotTable is interactive, you can freely experiment with layout of the data to focus on specific details or calculate different summaries, such as counts or averages.

When to use a PivotTable

PivotTables are especially designed for the following uses:

  • Getting perspective on large amounts of data in multiple ways

  • Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas

  • Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas that are interesting to you

  • Moving data fields to columns to rows (or "pivoting") to see different summaries of the source data

  • Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want

  • Presenting concise, attractive, and annotated reports

Use a PivotTable when you want to analyze related totals, especially when you have a long list of figures to sum, and aggregated data or subtotals would help you look at the data from different perspectives and compare figures of similar data.

The following example shows a worksheet with quarterly sales by state. The data is in no particular order:

PivotTable Source Data

Callout 1 Sales of items by state

Callout 2 Sales for California

The following example shows how the same data can be better summarized in a PivotTable:

PivotTable Quarter Sales

Callout 3  PivotTable summarizing quarterly sales by state

Callout 4  Summary for California

In a PivotTable, each column or field in the source data becomes a PivotTable field that summarizes multiple rows of information. In the previous example, the Location column becomes the Location field, and each record (a collection of information about a field) for California is summarized in a single PivotTable item for California.

A value field, such as Sum of Sales, contains the values to be summarized. In the previous report, the Q3 California summary contains the sum of the Sales value from every row in the worksheet for which the Location column contains California and the Quarter column contains Q3.

Preparing your data for a PivotTable

When you use a worksheet as the basis for a PivotTable, the data should be in a list or table format with descriptive column names in the first row. Each cell in later rows should contain data appropriate to its column name. There should be no blank rows or blank columns within the data of interest. Excel uses your column names as field names in PivotTables. Using the PivotTable Builder, you can drag these fields into the row, column and report filter areas of the PivotTable.

See also

Create a PivotTable

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!