Excel · Summarising
Pivot Tables & the formulas behind them
A Pivot Table is a drag-and-drop SUMIFS. Learn both and you can answer "summarise this data" two ways, and check one against the other.
Your win todayTurn an 8-row sales table into a Region × Product revenue summary with a pivot, then reproduce one cell with
SUMIFS.The data
| A Date | B Region | C Rep | D Product | E Units | F Revenue | |
|---|---|---|---|---|---|---|
| 2 | 05-Jan | North | Asha | Laptop | 2 | 120000 |
| 3 | 09-Jan | South | Ravi | Phone | 5 | 75000 |
| 4 | 15-Jan | North | Asha | Phone | 3 | 45000 |
| 5 | 02-Feb | East | Meera | Laptop | 1 | 60000 |
| 6 | 11-Feb | South | Ravi | Laptop | 2 | 120000 |
| 7 | 20-Feb | North | John | Phone | 4 | 60000 |
| 8 | 03-Mar | East | Meera | Phone | 6 | 90000 |
| 9 | 18-Mar | South | Fatima | Laptop | 3 | 180000 |
The source data needs a single header row and no blank rows or columns.[MS] Pro move: press Ctrl+T to make it a Table first, so new rows join the pivot when you refresh.
Four boxes: where each field goes
Pivot facts interviewers check
- Insert → PivotTable, choose New Worksheet, then tick or drag fields.
- Value Field Settings switches Sum → Count / Average / Max. Show Values As → % of Grand Total gives shares.
- A pivot does not auto-update. After the source changes: right-click → Refresh.
- Group dates by Month/Quarter (right-click a date in Rows → Group). Slicers are clickable filter buttons.
- Count of Revenue counts rows, not money. It's a common mis-click.
The basic formulas you must know cold
| Formula | Does | On this data |
|---|---|---|
SUM / AVERAGE / MIN / MAX | The obvious | =SUM(F2:F9) → 750000 |
COUNT vs COUNTA | COUNT = numeric cells only; COUNTA = all non-empty cells | =COUNT(B2:B9) → 0 · =COUNTA(B2:B9) → 8 |
SUMIF(range, criteria, sum_range) | Sum with one condition | =SUMIF(B2:B9,"North",F2:F9) → 225000 |
SUMIFS(sum_range, crit_range1, crit1, …) | Sum with many conditions. sum_range comes first! | =SUMIFS(F2:F9,B2:B9,"South",D2:D9,"Laptop") → 300000 |
COUNTIFS(crit_range1, crit1, …) | Count rows meeting conditions | =COUNTIFS(D2:D9,"Laptop") → 4 |
AVERAGEIFS(avg_range, crit_range1, crit1, …) | Average with conditions | =AVERAGEIFS(F2:F9,B2:B9,"East") → 75000 |
IF(test, if_true, if_false) | Branch | =IF(F2>=100000,"Big","Small") |
IFERROR(value, value_if_error) | Replace any error (#N/A, #DIV/0!) with something clean | =IFERROR(VLOOKUP(G2,A2:F9,6,FALSE),"Not found") |
TRIM / LEFT / RIGHT / MID / LEN | Clean and slice text | =LEFT("E103",1) → E |
CONCAT or & | Join text | =C2&" - "&B2 → Asha - North |
Syntax as documented by Microsoft for SUMIFS and IFERROR.
Classic trap
SUMIF puts the sum range last. SUMIFS puts it first. Interviewers ask this on purpose.Say it in the interview"A Pivot Table summarises a flat table by dragging fields into Rows, Columns, Values and Filters. I keep the source as an Excel Table so a Refresh picks up new rows. When I need the summary inside a model or report, I use SUMIFS or COUNTIFS. Each pivot cell is really a SUMIFS with the row and column labels as its criteria, so I can cross-check one against the other."
Retrieval check
Rows = Region, Columns = Product, Values = Sum of Revenue. What's in South × Laptop?
South laptops: Ravi 120000 + Fatima 180000. 375000 is the South row total.
You add 50 new rows to the source and the pivot doesn't change. Why?
Pivots cache their source. Refresh, and use a Table (Ctrl+T) so the range grows.
What does COUNTA count?
COUNT is numbers only. COUNTA is anything non-empty. COUNTBLANK is empty cells.
Which SUMIFS is written correctly?
SUMIFS takes sum_range first, then (criteria_range, criteria) pairs.
What does =IFERROR(VLOOKUP(…), "Not found") do?
It doesn't fix anything. It catches any error and shows your fallback instead. That's also a risk, because it can hide real mistakes.
Type it
Write a formula for total North revenue.
Count how many South Laptop sales there were.
Your turn
- Type the 8-row sales table. Press Ctrl+T to make it a Table.
- Insert → PivotTable → build Region (Rows) × Product (Columns) × Sum of Revenue. Match the diagram.
- Switch Values to Count, then to Show Values As → % of Grand Total. Read off South's share (50%).
- Drag Rep into Filters and show only Meera.
- Add a 9th row (any values). Confirm the pivot ignores it until you click Refresh.
- Reproduce 3 pivot cells with SUMIFS. Add an IF column labelling revenue ≥ 100000 as "Big".
Go deeper
Primary source: Microsoft Support: Create a PivotTable to analyze worksheet data. Then SUMIFS function.
Related: Lesson 2 · Lesson 5: GROUP BY is SQL's pivot · Excel cheat sheet
💬 Ask your teacher: "give me a messy dataset to pivot" or "what's a calculated field?"