Pivot Tables, step by step on a messy sales sheet

The messy sheet we will use

Type this shop sales sheet into Excel or Google Sheets, exactly as shown — every total below is worked out on these numbers. Row 1 is the header. The Total column is already filled (Units × Price, for example =D2*E2 in F2, copied down).

It looks like a normal sales list, but it has two problems a real sheet would have — and both of them matter for what comes next:

1. Row 7 says "drinks" with a small d, while every other row says "Drinks". To Excel those are two different categories, so a pivot table would split Tea's sales across two groups.
2. Row 14 is an exact copy of row 12 — same date, product, category, units and price. It is the kind of duplicate a copy-paste leaves behind, and it would add an extra 3000 to every total it touches.

Clean it first: fix the case, remove the duplicate

A pivot table summarises what is there — it cannot know that "drinks" and "Drinks" are the same thing, or that row 14 should not exist. Two minutes of cleaning now saves a wrong answer later.

Fix the spelling. Click cell C7, retype Drinks, and press Enter. That is all it takes — but on a sheet with hundreds of rows you would use Find & Replace (Ctrl+H) instead, replacing drinks with Drinks across the whole column.

Remove the duplicate. Click anywhere inside the table, then go to Data → Remove Duplicates. In the dialog, leave every column ticked — a row only counts as a duplicate if *all* its values match — and click OK. Excel reports that 1 duplicate was removed and 12 unique rows remain.

Expected result: the sheet now has 12 data rows (rows 2–13), all four categories spelled consistently. Check it before going on: the Total column should still add up to 69,900 — 6000 + 7500 + 7200 + 7000 + 9000 + 4500 + 6000 + 4800 + 5000 + 5400 + 3000 + 4500. If your total is 72,900, row 14 is still there.

In Google Sheets the same step is Data → Data cleanup → Remove duplicates, and the spelling fix is identical. Google Sheets also has no separate Refresh step later — its pivot tables update automatically when the source data changes.

What is a pivot table, and when do I need one?

You already know how to answer a grouping question with a formula. "How much did Drinks sell in total?" is a SUMIF:

=SUMIF(C2:C13,"Drinks",F2:F13)

Expected result: 31500. That works — but you need one formula per category, and a new formula every time the question changes. Four categories, four SUMIFs; totals by product, another five; totals by day, another six. The formulas are the slow way to ask grouping questions.

A pivot table asks the same question by dragging: Category goes in Rows, Total goes in Values, and Excel does the adding. No formulas, no ranges to get wrong, and when the question changes — "now by product", "now by day" — you drag a different field instead of rewriting anything.

The rule of thumb: if the question contains the word "by" — by category, by product, by month — a pivot table is the tool. If it is a single total or a single condition, a formula is faster.

How do I build my first pivot table?

Click anywhere inside the cleaned table (rows 2–13), then Insert → PivotTable → From Table/Range. Excel shows the range A1:F13 — the whole table including headers — and asks where to put the pivot. Choose New Worksheet and click OK.

A blank pivot appears on a new sheet, with a PivotTable Fields pane on the right listing your six column names: Date, Product, Category, Units, Price, Total. Build the summary by dragging:

1. Drag Category into the Rows area.
2. Drag Total into the Values area. Excel shows it as "Sum of Total" — exactly what you want.

Expected result:

Check it against the hand calculation: Drinks is Tea 6000 + 4500 + 3000 = 13500 plus Coffee 7500 + 6000 + 4500 = 18000, which is 31500. Grocery is Sugar 7200 + 4800 = 12000. Snacks is Biscuits 7000 + 5000 = 12000. Dairy is Milk 9000 + 5400 = 14400. The Grand Total, 69900, matches the SUM of the Total column you checked during cleaning. If any number disagrees, the cleaning step was skipped — a leftover "drinks" row would show as a second Drinks line, and a leftover duplicate would push the total to 72900.

How do I read the result — and add a second grouping?

The pivot reads like a report: each row is one group, each number is that group's total, and the Grand Total is the check that everything adds up. To see more detail, drag a second field into Rows — fields stack, so the pivot groups by the first field, then by the second inside it.

Drag Product into the Rows area, underneath Category:

Expected result: Coffee (18000) outsells Tea (13500) inside Drinks, while every other category has a single product so its number is unchanged. The Grand Total is still 69900 — adding detail never changes the total, which is a useful check that the pivot is built right. Drag Product back out of Rows to return to the simple four-line version.

How do I change what the pivot counts — sum, count or average?

Values does not have to be a sum. Click the small arrow next to "Sum of Total" in the Values area and choose Value Field Settings, then pick a different calculation:

• Count tells you how many sales rows sit behind each total: Drinks 6, Grocery 2, Snacks 2, Dairy 2 — 12 rows in all, which matches the cleaned sheet.
• Average tells you the average sale per row: Grand Total 69900 ÷ 12 rows = 5825. Drinks averages 31500 ÷ 6 = 5250 per row; Grocery 6000; Snacks 6000; Dairy 7200.

Expected result: switching to Average and back to Sum leaves the pivot otherwise untouched — only the numbers change. Beginners most often want Sum (how much?) or Count (how many?); Average answers "how big is a typical sale?" and is the one people reach for by mistake when they wanted the total, so always read the column heading — "Sum of Total", "Count of Total", "Average of Total" — before trusting the number.

In Google Sheets the same choice lives in the pivot editor pane: click the field under Values and pick SUM, COUNTA, AVERAGE, or the others from the "Summarize by" list.

How do I group by date, and filter to one category?

The same two drags answer a different question. Remove Category and Product from Rows, and drag Date into Rows instead:

Check the first row: 2026-10-01 has Tea 6000 + Coffee 7500 = 13500. The busiest day was 2026-10-02 at 14200. The Grand Total is still 69900 — regrouping never changes it.

To look at one category only, drag Category into the Filters area. A filter dropdown appears above the pivot: pick "Drinks", and the daily table recomputes to Drinks only — 2026-10-01 shows 13500, 2026-10-02 shows nothing, and the Grand Total drops to 31500. Change the filter back to "(All)" to see everything again. Filters never touch the source data; they only change what the pivot shows.

Worked example: answer three questions in under a minute

With the pivot built, a manager's three questions take three glances — no new formulas, no new sheets. Each answer is a drag, not a calculation, and each one cross-checks against the others, because every regrouping must still total 69,900.

How do I refresh a pivot table when the data changes?

A pivot table is a snapshot: it copies the totals at the moment you build it, and it does not watch the source sheet. Change the data and the pivot goes stale until you tell it to refresh.

Try it: on the source sheet, change the Tea units in row 2 from 40 to 50. The Total in F2 updates itself to 7500 (the formula does that), but the pivot still says Drinks 31500 and Grand Total 69900. Now click anywhere inside the pivot, right-click, and choose Refresh — or press Alt+F5 with the pivot selected.

Expected result: Drinks becomes 33000 (31500 + 1500) and the Grand Total becomes 71400 (69900 + 1500). The pivot is right again. Press Ctrl+Z twice on the source sheet to put the Tea units back to 40, refresh the pivot once more, and the numbers return to 31500 and 69900 — the tutorial's numbers for everything else in this guide.

Forgetting Refresh is the single most common pivot-table error in real work: the pivot looks finished and authoritative, and it is quietly out of date. If a pivot's total disagrees with a SUM of the same column, refresh first and investigate second. (Google Sheets does not need this step — its pivot tables update automatically — which is one genuine difference worth remembering.)

Common mistakes

• Building the pivot before cleaning. A duplicate row inflates every total it touches, and "drinks" vs "Drinks" splits one category into two lines. Clean first, pivot second — always in that order.
• Forgetting to refresh. A pivot table is a snapshot. Change the source data and the pivot keeps showing the old totals until you right-click and choose Refresh (Excel) — in Google Sheets this happens automatically.
• Reading "Count" as "Sum". Count of Total for Drinks is 6 (six sales rows), not 31500. Always read the heading — "Sum of Total", "Count of Total", "Average of Total" — before quoting a number.
• No headers, or a blank header cell. A pivot table needs one header row with a name in every column. A blank header produces a field called "(blank)" and a pivot that is hard to read; a missing header row makes Excel guess the range wrong.
• Blank rows inside the source data. A blank row splits the table, and Excel's suggested range stops at the gap — rows below it silently vanish from the pivot. Delete blank rows before inserting the pivot table.

Frequently asked questions