8 Pivot Tables and Data Summaries

What pivot tables summarize

A pivot table turns detailed records into a flexible summary. It groups records by selected fields and calculates summaries for each group. Moving fields between areas changes the view of the same data without changing the source records.

Prepare the source data

Start with a clean, rectangular source-data table. Give every column a unique, nonblank header; use one row per record and one column per variable; and avoid merged cells and blank rows or columns within the data. Keep values in each column consistent in type—for example, do not mix dates and text in a date column—and include the full range of records you want summarized.

These practices help spreadsheet programs identify fields and summarize them as intended. A sales table, for example, might use one row per transaction and columns for Date, Region, Product, Units, and Revenue.

Build the pivot table

In Excel, select a cell in the source table and choose Insert > PivotTable. In Google Sheets, select the source range and choose Insert > Pivot table. Both applications let you arrange fields into areas for rows, columns, values, and filters, although their labels vary slightly.

• Rows: Choose categories that define groups, such as Region.

• Columns: Choose categories to display across the top for comparison, such as Product.

• Values: Choose what to calculate for each group, such as the sum of Revenue.

• Filters: Choose which records to include in the view, such as one year or sales channel.

A field can often be moved between areas to change the view. A field in Rows and a field in Columns both define groups; their positions determine how the summary is laid out.

Choose the right summary

Common summaries include Sum, Count, and Average; some spreadsheet tools also offer minimum, maximum, and other calculations. Numeric fields commonly default to Sum, while text or mixed and non-numeric values may be counted instead. Check the selected summary rather than assuming the default is appropriate.

For the sales example, each calculation answers a different question:

• Sum of Revenue answers, “What was total revenue?”

• Count of transactions answers, “How many records are represented?”

• Average of Revenue answers, “What was the mean revenue per record?”

• Sum of Units answers, “How many units were sold?”

These questions are not interchangeable. Averaging transaction revenue gives the average transaction value, not revenue per unit. For a group, calculate revenue per unit by dividing its total revenue by its total units:

Group and compare results

Suppose a source table contains these records:

• East, Tea: Revenue $120$

• East, Coffee: Revenue $200$

• West, Tea: Revenue $150$

• West, Coffee: Revenue $100$

Place Region in Rows, Product in Columns, and Revenue in Values, summarized by Sum. The result is:

• East: Coffee $200$, Tea $120$, row total $320$

• West: Coffee $100$, Tea $150$, row total $250$

• Column totals: Coffee $300$, Tea $270$; Grand Total $570$

The view shows totals by region and product, as well as row and column totals. Move Product to Rows or remove it to ask a different question. Add more row fields, such as Salesperson beneath Region, to show finer groups. When the spreadsheet application supports it, suitable dates or numeric values can also be grouped into broader periods or intervals.

Pivot tables can also show values as a percentage of a row, column, or grand total. For example, showing each product's revenue as a percentage of the grand total makes its share of all revenue easier to compare. Identify the denominator: percent of grand total and percent of region total answer different questions.

Filter and explore

A pivot-table filter narrows the source records that contribute to a summary, for example by showing only one region or a date range. Groups can also be sorted by their labels or summarized values to help reveal the largest or smallest categories. Some spreadsheet tools offer interactive filters or slicers.

Explore by starting with a broad summary and then adding or removing one field at a time. Compare a total with its subgroups, change the aggregation, or filter the records to investigate a pattern. Excel and Google Sheets allow you to inspect the underlying records contributing to a summarized cell, though the interaction differs by application.

Follow an analysis workflow

Use a question-led workflow to create and check a useful summary:

1. Ask a specific question. For example, “Which product generated the most revenue in each region?”

2. Check the source. Confirm the headers and data types, check for missing values, and make sure the source range includes all relevant records.

3. Create the pivot table. Put Region in Rows, Product in Columns, and Revenue in Values; summarize Revenue by Sum.

4. Refine the view. Add a date filter or group dates by an appropriate period; sort by revenue if useful.

5. Validate the result. Check whether the grand total makes sense against the source data, and confirm that the aggregation and filters match the question.

6. Refresh after source changes. New or edited source data may require refreshing the pivot table. If the source range did not include new rows, update the range as well.

Check for common pitfalls

Check for common problems before interpreting or reporting a summary:

• Wrong aggregation: A count instead of a sum may indicate that values are stored as text or have inconsistent types. Check the field and summary setting.

• Double-counting: If each source row is not a distinct record for the question, summing every row may inflate totals. Check what one row represents before interpreting the result.

• Misleading averages: An average of group averages is not necessarily the overall average when groups contain different numbers of records. Use the underlying observations or a correctly weighted calculation when needed.

• Unnoticed filters: A filtered pivot table summarizes only the included records. Review filter selections before reporting results.

• Stale or incomplete data: Refresh after source edits, and ensure the source range covers the records intended for analysis.