A pivot table summarises thousands of rows in seconds without writing a single formula. Its one condition: data laid out as we learned in Lesson 1.
1 The five steps
Clean the data
Single headers, no gaps
Ctrl + T
Turn it into a named Table
Insert → PivotTable
On a new sheet
Place the fields
Rows · columns · values · filters
Format and refresh
Number formatting, then refresh when the data changes
A pivot never alters your source data — so experiment without fear
2 The four areas
| Area | What goes in it | Example |
|---|---|---|
| Rows | What you want broken down vertically | Cost centre · account |
| Columns | A second dimension for comparison | Month · quarter |
| Values | What gets calculated | Sum of amount · count of entries |
| Filters | What scopes the report | Branch · year |
Four rows here for illustration; the same principle works on forty thousand rows just as fast — provided the data is laid out as in Lesson 1, and you press Refresh before sending.
The raw data:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Cost centre | Account | Amount |
| 2 | 2026-01-12 | Administration | Salaries | 85,000 |
| 3 | 2026-01-20 | Sales | Salaries | 62,000 |
| 4 | 2026-02-03 | Administration | Rent | 30,000 |
| 5 | 2026-02-18 | Sales | Marketing | 45,000 |
The pivot output: cost centre in rows, month in columns, sum of amount in values.
| Cost centre | January | February | Total | |
|---|---|---|---|---|
| 1 | Administration | 85,000 | 30,000 | 115,000 |
| 2 | Sales | 62,000 | 45,000 | 107,000 |
| 3 | Total | 147,000 | 75,000 | 222,000 |
Drag “Account” into Rows beneath Cost centre and the report becomes hierarchical: each centre with its expenses broken out — with no formula at all.
A pivot does not update itself
If you change the source data, the report keeps its old figures until you press Refresh. Many a meeting has discussed last month’s numbers without anybody noticing. Make refreshing the last step before sending any report.
3 Tools that enrich a report
4 Conditional formatting
It colours cells automatically according to their value, so the reader sees the exception before reading the number:
- Data bars: a quick visual comparison within the column.
- Colour scales: higher is darker — useful for receivables ageing.
- A custom rule: colour the whole row if the debt is over 90 days old.
- Icon sets: ✓ and ! markers for the status of items.
Two colours are enough
A report that is coloured all over highlights nothing. Reserve one colour for a warning and one for a positive, and leave the rest neutral. The same rule applies to charts: one chart answers one question.
5 Before sending and printing
| Step | Why |
|---|---|
| Refresh the pivot and the connections | So you do not send stale figures |
| Freeze the header row | So it stays visible as you scroll |
| Repeat the header row on every page | Page Setup → Print Titles |
| Set the print area and fit to one page wide | Prevents truncated pages |
| Consistent number formatting with thousands separators | Readability and a professional look |
| Export to PDF for sharing | Preserves the layout and prevents casual editing |
Chapter 1 summary
- A sound table is the prerequisite for everything that follows.
- An absolute reference protects your formulas when you fill down.
- Ten functions cover most daily work.
- XLOOKUP or INDEX+MATCH for linking tables.
- A pivot condenses thousands of rows — provided you refresh before sending.
6 Test your understanding
Three quick questions
Pick the answer you believe is correct and you will see the result immediately.
1. You added new rows and they did not appear in the pivot. The most likely cause:
A Table expands automatically, but a Refresh is still needed for the data to appear.
2. You want each cost centre’s expenses spread across the months. Where do you put “Month”?
Rows for the vertical breakdown, Columns for the dimension you compare across, and Values for what gets calculated.
3. Best practice in formatting a report that management will read:
Too many colours cancel each other out. Formatting should serve the message, not compete with it.