 | 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 dataThe list or table that's used to create a PivotTable. Source data can be taken from an Excel list or database, or another PivotTable. 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:
Sales of items by state
Sales for California
The following example shows how the same data can be better summarized in a PivotTable:
PivotTable summarizing quarterly sales by state
Summary for California
In a PivotTable, each column or field in the source data becomes a PivotTable fieldA category of data derived from a column in a source list or table. For example, the Month field in a source list or database becomes the Month field in a PivotTable. Items from the source list or table — such as March, April, and so on — become row labels, column labels, or page field items in the PivotTable. 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 itemA subcategory of a PivotTable field. Items in a PivotTable are derived from unique entries in a database field or from unique values in a list column. For example, a field called Month might contain items such as March, April, and so on. In a PivotTable, items appear as row or column labels, or in the lists for page fields. 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.
