5 Pivot Tables

What a pivot table does

A pivot table turns detailed records into an interactive summary. It groups records by selected fields and calculates summaries such as sums, counts, or averages without changing the underlying records. Because fields can be rearranged, the same data can be examined from different perspectives.

Prepare the source data

Reliable summaries begin with well-structured source data. Use one header row with a distinct name for each column, and put one record in each row. Avoid merged cells and blank rows within the data.

Keep each column to one kind of information: for example, store dates as dates and sales amounts as numbers. Check for missing or inconsistent entries, such as West, west, and W. being used for the same region.

A sales log might contain the fields Date, Region, Product, Units, and Sales, with each row representing one transaction.

Arrange fields to build a summary

A spreadsheet’s field list or editor commonly provides four areas for arranging fields:

• Rows places categories down the left side, such as Region.

• Columns places categories across the top, such as Product.

• Values specifies summaries, such as Sum of Sales.

• Filters limits which records contribute to the summary, such as by Year.

For example, placing Region in Rows, Product in Columns, and Sales in Values produces sales for each region–product combination, along with totals where enabled. The pivot table calculates these values from the underlying transactions; it does not merely copy selected rows.

Choose a calculation for values

A value field can often be summarized in several ways:

• Sum adds numeric values.

• Count counts nonempty entries, making it useful for counting transactions or records.

• Average calculates the mean.

• Minimum and Maximum show the smallest and largest values.

The default calculation depends on the spreadsheet application and the data type it detects. If a number column is stored as text, the pivot table may count entries instead of summing them. When a result looks unexpected, check both the source data and the selected summary calculation.

Rearrange the report

Move a field between Rows, Columns, Values, and Filters to change the summary’s layout. For example, put Product in Rows and Region in Columns to turn a report around, or move Product from Columns to Rows to list products vertically.

Adding Date below Region in Rows creates nested detail, such as region followed by transaction date. Removing a field simplifies the report but does not delete the field or the source data. Row or column categories can also be reordered and sorted by their names or summarized values.

Group detailed data

Grouping combines detailed items into broader categories, which is useful when individual values make a report too long. Depending on the spreadsheet program, selected labels can be grouped manually, while dates and numbers can be grouped according to rules.

Examples include grouping transaction dates by month, quarter, or year; grouping ages into intervals such as 0–9, 10–19, and 20–29; and combining product types into a category such as “Accessories.” Keep groups meaningful and check the date or number range so records are not unintentionally left outside them.

Filter the included records

A filter limits which source records contribute to a summary without deleting those records. You can filter by a field’s items, such as showing only the East and West regions, or by a condition, such as dates in a particular period. A field in Filters provides a report-level control; filters on row or column labels narrow those categories. Some spreadsheet programs also offer slicers, visible controls for selecting filter values.

Check which filters are active before interpreting or sharing a result. A filtered total describes only the included records and may not represent the whole dataset. In some applications, a value-based filter may need adjustment after new source values are added.

Create and verify a useful report

Use this workflow to create and check a report:

1. Inspect and clean the source table; confirm that headers, dates, and numeric fields are recognized correctly.

2. Create a pivot table from the complete data range.

3. Place category fields in Rows and, if useful, Columns.

4. Place a measure in Values and choose an appropriate calculation, such as Sum, Count, or Average.

5. Add grouping or filters to answer a focused question.

6. Rearrange or sort fields to compare results from another perspective.

7. Check totals against the source data, and confirm that displayed filters and groups match the question.

8. Refresh or update the pivot table when the source data changes if the spreadsheet does not update it automatically.

To investigate whether monthly sales differ by region, group Date by month, put Region in Rows, place Sales in Values as Sum, and filter to the period of interest. To compare products instead, move Product into Rows or Columns.