Pivot tables and summaries
A clean table of 24,000 rows answers nothing until you summarise it. This lesson teaches you to build pivot tables and SUMIFS summaries, to pick the right summary (sum, count or average), to compare periods fairly, and to find which stores and categories make up a change. In the course story, this is where East city's drop stops being one number and becomes 9 stores and two aisles.
By the end you can
- Build a pivot table from rows, columns, values and filters, and say what each cell adds up
- Choose sum, count or average, and compute averages as a ratio of sums
- Show % of total, week-over-week change and change against the same weeks last year
- Write the same summary with SUMIFS when you need a fixed report layout
- Split a total change into the contribution of each store and category, and find where it is concentrated
The everyday picture
A shopkeeper keeps every receipt in a shoebox. At the end of the month she wants to know what sold. She lays envelopes on the table in a grid: one row of envelopes per product group, one column per week. She drops each receipt line into its envelope, then adds up each envelope and writes the total on the front.
Now she puts last year's box next to this year's and does the same. Most envelopes weigh about the same as last year. Two are much lighter: fruit and bread. She doesn't need to read all 1,000 receipts again. She knows where to look next.
A pivot table is that envelope grid, built in a second. You choose what goes in the rows, what goes in the columns and what to add up, and the spreadsheet sorts every row of your data into exactly one envelope.
How a pivot table works
A pivot table does three things, in this order. First it filters: rows that don't match the filter are left out. Then it groups: every remaining row goes into exactly one cell, chosen by its value in the row field and the column field. Finally it summarises: for each cell, it adds up (or counts, or averages) the value field over the rows in that cell. The grand totals are the same summary over a whole row, a whole column or everything.
You set this up in four areas. Excel calls them Filters, Columns, Rows and Values; Google Sheets has Rows, Columns, Values and Filters in its pivot table editor. In Excel, click any cell of your data and choose Insert, then PivotTable. In Google Sheets, choose Insert, then Pivot table. Then drag field names into the areas.
Three habits keep pivots honest. Start from a clean, tidy table with one header row and no subtotal rows, as you built in Cleaning data in a spreadsheet. Know the grain of the source: here one row is one store, one day, one category, so 54 stores times 56 days times 8 categories gives 24,192 rows. And remember that an Excel pivot does not update when the data changes: you refresh it (right-click the pivot and choose Refresh). If your data is an Excel Table (Ctrl+T), new rows are included when you refresh. Google Sheets pivot tables update on their own when the source cells change.
Quick check
You want one row per store and one column per week, showing that store's sales in that week. Where do the fields go?
The row field decides the rows, the column field decides the columns, and Values holds the number you add up. Swapping rows and columns gives the same numbers turned sideways, but sales in Filters or week in Values would not add up sales at all.