The Pivot tables guide
A pivot table totals a list by category without a single formula. Click a cell in the data, Insert → PivotTable, then put the category in Rows and the number in Values. Check that Values says Sum, not Count.
Build it
- Click any cell in your data. The data needs a header row and no empty rows.
- Insert → PivotTable. Excel picks the whole table; choose where the pivot table goes (here, next to the data).
- In the field list, tick the category (Region). Text fields go to Rows.
- Tick the number (Sales). Number fields go to Values.
You get one row per region, its total, and a Grand Total at the bottom. Change the data and press Refresh to update it: a pivot table doesn't recalculate on its own.
Count of Sales instead of Sum
If the number column has even one empty cell or a cell with text, Excel puts it in Values as Count: the number of entries, not their total. It's easy to miss, because Count still gives believable numbers. Read the header: it says "Count of Sales" or "Sum of Sales". To change it, open the field in Values and choose Summarize Values By → Sum.
Pivot table or SUMIFS?
A pivot table is quicker to build and to rearrange: drag a field to Columns and you get regions across the top. SUMIFS updates by itself when the data changes and can sit anywhere in a report. Analysts use both, and tests often ask for either.
Common mistakes
- Count instead of Sum. Read the Values header every time.
- Part of the data. An empty row, or a selection that stops early, leaves rows out.
- Forgetting to refresh. New rows and edits don't show until you do.
- Too many fields at once. Start with one in Rows and one in Values; add more once that reads right.
FAQ
- How do I make a pivot table in Excel?
- Click a cell in your data, choose Insert > PivotTable, then tick the fields: text fields go to Rows, number fields to Values. The data needs a header row and no empty rows.
- Why does my pivot table count instead of sum?
- The number column contains a blank or text cell, so Excel starts with Count. Change it in Values: Summarize Values By > Sum. Clean the column to stop it happening.
- Why doesn't my pivot table update?
- Pivot tables don't recalculate automatically. Right-click it and choose Refresh, or Data > Refresh All. If you added rows below the data, change the source range too, or turn the data into a table first.
- Should I use a pivot table or SUMIFS?
- A pivot table is faster to build and rearrange. SUMIFS updates by itself and fits into a report layout. Knowing both is useful in Excel tests.
This task is in the timed data analyst and advanced Excel practice tests. What Excel interview tests check
