Pivot tables

Why it matters

SUMIF answers "how much for Safety?". A pivot table answers "how much for every category, by month, with counts and averages" in one go, and you can rearrange it in seconds to ask the next question. Pivot tables are one of the most-used features in analyst, lab and project work because they summarise thousands of rows without a single formula.

The core idea: group, then summarise

A pivot table takes a tidy table and lets you drag fields into four areas:

The pivot table does not change your data. It is a summary view that you refresh when the source changes.

Creating one

1. Click a cell inside your clean table (it must have a single header row and no blank rows).
2. Google Sheets: Insert > Pivot table, choose New sheet, then use the editor panel on the right (Rows: Add, Values: Add). LibreOffice Calc: Insert > Pivot Table (in older versions Data > Pivot Table > Insert or Edit), keep Current selection, then drag field names into Row Fields, Column Fields, Data Fields and Page (filter) Fields. Excel: Insert > PivotTable, New Worksheet, then drag fields in the PivotTable Fields pane.
3. Change how a value is summarised: Summarize by (Sheets), double-click the data field (Calc), or Value Field Settings (Excel). Options include SUM, COUNT, AVERAGE, MIN, MAX.

Worked example: three questions, one pivot

Using the Orders table with its Total column:

Question 1: How much did we spend per category? Rows: Category. Values: SUM of Total.

The grand total matches the SUM you calculated earlier, a good sanity check.

Question 2: How many orders per category? Add a second value: COUNT of Item (or COUNTA in Sheets). Consumables 3, Office 3, Safety 4. Safety has the most orders but not the most spend.

Question 3: Which item cost the most overall? Change Rows to Item and sort by SUM of Total, descending. Pipette tips (rack) is first with 231.00 (two orders: 105.00 + 126.00), then Nitrile gloves (box) at 170.00.

The same pivot answered all three by moving fields. With a Supplier column (from the lookup lesson), put Supplier in Columns to get a category-by-supplier grid.

Grouping dates

For data that spans months, group dates: right-click a date in the pivot and choose Create pivot date group > Month (Sheets), Group and Outline > Group (Calc) or Group > Months (Excel). Or add a helper column in the source, such as =TEXT(A2, "yyyy-mm"), and use it as a row field. The helper-column approach works the same everywhere.

Keeping it right

• Refresh after the source changes: Sheets updates automatically; Calc needs Data > Pivot Table > Refresh; Excel needs PivotTable Analyze > Refresh.
• Include new rows: if you add rows below the source range, make sure the pivot's range covers them. Selecting whole columns (Sheets: Orders!A:F) or using an Excel Table avoids this.
• Count versus sum: summing an ID column gives nonsense; count it instead. If a number field shows COUNT instead of SUM, some cells in it are text or blank (go back to cleaning).
• Spelling variants become separate rows: "Safety" and "safety " show up as two categories. That is a quick way to find cleaning problems.

Common mistakes

• Building a pivot on a messy table with merged headers or blank rows.
• Forgetting to refresh and presenting stale numbers.
• Typing over pivot results or writing formulas that point at pivot cells, which move when the layout changes.
• Too many fields at once. Ask one question per layout.

Quick recap

• A pivot table groups rows (Rows/Columns) and summarises numbers (Values), optionally filtered.
• Change SUM to COUNT or AVERAGE to ask different questions.
• Check the grand total against a known total; refresh when data changes.

Try it

Open the Do tab to answer three questions with one pivot table.