3.1 PivotTables

What you'll be able to do

Summarise ten thousand rows five different ways in two minutes. Total by store, count by month, average by category — by dragging field names into four boxes instead of writing forty formulas.

Before you start

Download the pivot sales workbook. Its sales table is already a proper Excel Table, and that setup is what makes everything below painless.

The work

1. Same either way: click anywhere in the data and check that it is a real Table. If it is not, press Ctrl+T and accept. A Table grows when rows are added, and the PivotTable will follow it. This one habit prevents the refresh surprise in step 7.
2. No documented keyboard shortcut for this — use the menu: go to Insert → PivotTable, accept the range it suggests and New Worksheet, and press Enter. An empty PivotTable appears, with a list of your field names beside it.
3. Same either way: read the four boxes as a sentence. ROWS is what each line is about. COLUMNS is what spreads across the top. VALUES is the number worked out in each cell. FILTERS narrows the whole report. "Sales by store by month" means Store into Rows, Month into Columns, Amount into Values. Drag those three now, and the summary is done.
4. Same either way: here is the first confusion, before it happens. A column holding text, or holding blanks, gives you a count where you expected a total. That is a setting, not a fault. Open the dropdown on the Values entry and choose Value Field Settings, where you can pick Sum, Count or Average. Under Show Values As, % of Grand Total turns amounts into shares.
5. Same either way: now rearrange it. Drag Store out, drag Category in, and swap what is in Rows and Columns. Each drag summarises thousands of rows again at once. This is the point of the tool: five different answers in the two minutes promised above.
6. Same either way: with a date field in Rows, Excel offers to group by month, quarter and year. Open the context menu on a date with Shift+F10 and choose Group. Double-click any result cell and the rows behind it appear on a new sheet, which answers "which sales make up this number?".
7. Same either way: a PivotTable reads a copy of the data, not the live sheet. New data appears only after a refresh. Alt+F5 refreshes this PivotTable, and Ctrl+Alt+F5 refreshes everything. Here is the mistake everyone makes once: rows added below a plain range sit outside the source, and refreshing will never see them. Built on a Table, as in step 1, the source grows with the data.

Try it

From the sales workbook, answer four questions with no formulas. Total sales by store, then by month, then average sale by category, then each store's share of the grand total. Then add a made-up sale at the bottom of the Table, refresh, and watch it counted.

Check yourself