A pivot table turns a long list of rows into a summary, like revenue by region, in a few drags and no formulas.
B2 = 3180 Each number in a pivot is a sum of the rows that share its label. North’s line adds the two North rows: 1470 + 1710.
Someone asks for “revenue by region”, and the data is a long log with one row per sale. The usual workaround is a SUMIF for every region, typed out by hand and quietly wrong the day a new region appears. A pivot table builds the same report in a few drags, and rebuilds it when the question changes.
Below is a café chain’s sales log. You build one report with guidance, split it by quarter, then answer a second question on your own. After that, small sheets show what a pivot’s numbers are made of, and where they go wrong.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
Every column header in the data becomes a field, and you place fields in four areas. Rows lists each value down the side, Columns spreads them across the top, Values is the number worked out for each line, and Filters limits the whole report to a slice of the data.
Reach for one whenever a question has “by” in it: sales by rep, hours by project, tickets by month. Changing the question means dragging a different field, not rewriting a block of formulas.
Values doesn’t only add up. It can count the rows, average them, or find the largest. For a column of numbers Excel starts with Sum; for text, with Count.
B2 = 3180 Sum adds up the revenue on North’s rows. It’s the default for a column of numbers.
If a number column holds any text or blank cells, Excel summarizes it with Count instead. When a total looks far too small, check the value field’s summary first.
A pivot puts rows together only when their labels match exactly. A typo, or a space at the end of a name, gives the same region a second line, and both lines are short.
B2 = 1470 North’s line is missing 1710: one row of the log says Nrth, and a pivot only puts identical labels together. A trailing space does the same.
Data cleaning practice covers finding and fixing those before the report is built.
A pivot doesn’t recalculate when the data changes. It keeps the old figures until you refresh it, so refresh before anyone else reads the report. A pivot built on a fixed range also ignores rows added below it, even after a refresh. Build it on an Excel table instead, and the range grows with the data.
Field names come from the header row, so a blank header stops Excel from making the pivot at all, and a title row sitting right above the data causes the same kind of trouble.
To keep practicing in your own Excel, download the workbook: a year of orders for a fictional office-supplies company, 350 rows across regions, reps and categories, with a tasks sheet of pivot questions and an answers sheet to check against.
A year of orders for a fictional office-supplies company — regions, reps, categories, quantities and totals. The classic pivot table dataset; also good for SUMIF, sorting and filtering.
350 rows · Order ID · Date · Region · Sales Rep · Product · Category · Units · Unit Price · Total
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches pivot table: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →