Pivot Tables in Excel: The Complete Guide
A pivot table is the difference between building a summary and asking for one. The same data, grouped differently, in seconds — provided the data is in the right shape first.
Short answer
What a pivot table is actually doing
Three operations, in this order: group the rows by the values in a column, aggregate a number within each group, and lay the groups out so you can read them. That is the whole idea.
Which is why it replaces the pattern most people learn first — a column of SUMIFS formulas, one per category, copied down a list of category names somebody typed. That approach works, and it breaks the moment somebody asks for the same figures by month instead of by category, because the formulas have to be rewritten.
| Question | SUMIFS columns | Pivot table |
|---|---|---|
| Revenue by state | One formula per state, copied down a typed list | Drag State to Rows, Amount to Values |
| Now by state and month | Rewrite every formula, add a column per month | Drag Month to Columns |
| Now only for the south region | Add another criteria to every formula | Drop Region into Filters |
| Now as a percentage of total | Divide every formula by a total formula | Show Values As → % of Grand Total |
| New category appears next month | The typed list has to be updated | Refresh |
The one thing it does not do
The data rule
Pivot tables are unforgiving about the shape of their source. Get this right and everything else is easy; get it wrong and the totals are quietly incorrect.
| Rule | Why |
|---|---|
| One header row, in row 1 | A pivot table takes the first row of the range as the field names. Anything above it becomes a field called Column1 |
| One record per row | Each row is one transaction. A row that represents a subtotal gets counted as a transaction |
| No blank rows or columns inside the data | A blank row ends the range as far as the pivot table is concerned; anything below it is ignored |
| No merged cells | A merged cell holds its value in the top-left cell only; the rest read as blank |
| No total rows at the bottom | The total row is counted, so every figure in the report is inflated by exactly the total |
| One value per cell | A cell containing two invoice numbers cannot be grouped by either |
| Consistent types in a column | A date column with three text dates groups into a separate text bucket |
The total row is the expensive one
Building one
Convert the range to a Table first
Insert → PivotTable
Put a category in Rows
Put a number in Values
Add a second category to Columns
Add filters
Set the number format
Name the pivot table
The four areas
| Area | Use it for | Watch out for |
|---|---|---|
| Rows | The categories you want down the page — vendor, ledger head, state | Too many nested fields makes a report nobody reads |
| Columns | A second dimension that is short and fixed — months, quarters, a handful of categories | Twelve months is fine; a thousand customer names is not |
| Values | The numbers to aggregate — one or more measures | Multiple value fields create a second column that readers miss |
| Filters | A field the reader changes — a company, a branch, a period | A filter one person set stays set for everybody who opens the file |
Value field settings
| Setting | What it does |
|---|---|
| Summarize by | Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, Var |
| Show values as | Percentage of totals, running total, difference from a base item, rank |
| Number format | The format applied to the value area — persisted across refreshes, unlike cell formatting |
Count in a value field is a diagnostic
Grouping dates
A real date column can be grouped — right-click a date in the row labels and choose Group, then pick Months, Quarters and Years. Excel creates the grouping fields for you and adds them to the field list.
| Situation | What happens | Fix |
|---|---|---|
| The column contains real dates | Grouping offers Seconds through Years | Nothing to fix |
| Group is greyed out | The column is text, not dates | Convert with DATEVALUE, or fix the type in the export |
| The export uses Indian format dates as text | dd/mm/yyyy is ambiguous — Excel may read it as text or misread the month | Re-import with the type set explicitly, or split and rebuild with DATE |
| Some dates are text and some are real | The pivot table grows a separate text group alongside the dates | Find them with ISNUMBER on the date column and fix each one |
| You need a financial year, not a calendar year | Excel groups by calendar year | Add a financial year column to the source and group by that |
That last row matters for Indian reporting. A financial year running April to March is not something the built-in grouping knows about, so the source table needs a column that maps each date to the right financial year and month number. Add it once in the source and the pivot table can group by it forever.
Filtering what you see
| Method | Best for |
|---|---|
| Row label filter | Excluding a few categories from an existing report |
| Value filter | Top 10 by amount; anything above or below a threshold; above or below average |
| Report filter (the Filters area) | A single selection applying to the whole pivot table |
| Slicer | A visible button panel a reader clicks — the clearest option for anyone who does not build pivot tables |
| Timeline | A date range slider on a real date field |
A slicer is worth the extra two clicks when the report is going to somebody else. A filter drop-down hides its current state inside a menu; a slicer shows which items are selected without anything being opened.
Calculated fields: usually not what you want
| Calculated field | Column in the source | |
|---|---|---|
| Evaluated | On the sums of the underlying fields | On each row, before aggregation |
| Addition, subtraction, multiplication, division | Works | Works |
| Anything else — IF, ratios of ratios, text | Produces the wrong answer or errors | Works |
| Where the logic lives | Inside the pivot table, invisible from the sheet | In the source, visible to anybody who opens it |
| Effect on the source data | None, which sounds good but means the logic is hidden | The column exists and can be audited |
A calculated field that divides one sum by another sum is correct. A calculated field that tries to apply any per-row logic — a conditional amount, a rate that depends on a category — is not, and it will return a plausible-looking wrong figure. Add the column to the source table with a formula, then aggregate that.
Refreshing
- Right-click → Refresh updates one pivot table. Data → Refresh All updates every query and pivot table in the workbook.
- Refresh on open is set in PivotTable Options → Data. It is convenient for a workbook you open yourself and risky for one that is emailed, because the recipient may not have access to the source.
- A pivot table built on a fixed range does not see new rows. Converting the source to an Excel Table fixes this permanently.
- Formatting applied to cells is lost on refresh — column widths, fills and fonts. Number formats set through Value Field Settings survive.
If a refresh appears to do nothing, the cause is one of a small number of things — covered in pivot table not refreshing.
Pivot table, SUMIFS, or Power Query
| Approach | Best for | Weak at |
|---|---|---|
| Pivot table | Exploring and summarising a well-shaped list; changing the grouping without rework | Anything needing per-row logic; a layout that has to look a specific way on a printed page |
| SUMIFS formulas | A fixed report layout with a known, short list of categories; a figure that must sit in a specific cell | Adding dimensions; scaling to many categories |
| Power Query Group By | A summary that must be produced the same way every period and fed into something else | Ad hoc exploration — you re-open the query editor each time |
These are not alternatives. The pattern that works in most finance teams is Power Query preparing the data, a pivot table summarising it, and a small number of SUMIFS formulas where the layout demands a figure in a fixed position. Power Query in Excel covers the first part.
Common mistakes
- Building it on a range that is not a Table. The report silently stops including new rows.
- Leaving total rows in the source. Every figure doubles.
- Typing over the pivot table. Excel refuses with an error, and people respond by copying and pasting values elsewhere — which breaks the link to the source permanently.
- Adding a blank row for spacing in the source. Everything below it falls out of the range.
- Deleting the source sheet. The pivot table survives as a frozen, wrong summary with no visible sign that anything happened.
- Using Count to hide a text problem. The total becomes plausible and wrong.
- Spreading the pivot table across a sheet a macro also writes to. One of the two will lose.
- Never refreshing, then reporting last month's figures under this month's heading. The most common reporting error there is, and the least visible.
Where this goes next
A pivot table produces numbers; a report needs to be read. The next step is a chart that follows the pivot table's shape and updates with it, which is what pivot charts are for. And when the same summary has to be produced every month in the same format, that is the problem described in building a monthly MIS report.
One caveat worth stating plainly: a pivot table summarises agreed data. If the figure you are summarising came out of a reconciliation that has not been finished, the report is precise and wrong. That earlier step — matching two sets of records and explaining every difference — is where Piloteq Automate does its work.
If you would rather not build this by hand
For the reconciliation behind the report
A pivot table summarises whatever is in the sheet. The figure is only as good as the reconciliation it came from. Piloteq Automate handles that earlier step — matching two sets of records and explaining the differences — so what the pivot table summarises is agreed.
- ✓Saved match rules applied every period
- ✓Tolerance and group matching
- ✓Exceptions listed with a reason
- ✓Comparisons produced consistently
Frequently asked questions
What is a pivot table used for?+
Summarising a long list of records into totals grouped by categories you choose. A sales register with fifty thousand rows becomes revenue by state by month in a few clicks, and the grouping can be changed without rewriting a single formula. It replaces the set of SUMIFS columns people build by hand to produce the same summaries.
What data format does a pivot table need?+
One header row at the top, one record per row below it, no blank rows or columns inside the data, no merged cells, and no total rows. Every column needs a heading with nothing above it. If the data meets that shape, the pivot table will work; if it does not, the totals will be wrong in ways that are hard to spot.
Why does my pivot table show Count instead of Sum?+
Because at least one cell in the value column is text rather than a number — usually a blank cell with a space in it, or a number that arrived from an export as text. Excel defaults the value field to Count when it cannot sum the column. Clean the column, refresh, then change the field setting back to Sum.
Should I add a calculated field or a column in the source data?+
Add the column to the source data, if you can. A calculated field operates on the sums of the underlying fields rather than on each row, which produces the wrong answer for anything other than simple addition, subtraction, multiplication and division. A column in the source table is evaluated per row and behaves the way you expect.
Why does my pivot table not pick up new rows?+
Because the source range was fixed when the pivot table was created. If the data is in a properly formatted Excel Table, the range expands automatically as rows are added and the pivot table picks them up on refresh. If it is a plain range, you have to change the data source, or convert the range to a Table.
Do pivot tables update automatically?+
No. A pivot table shows the data as of the last refresh. You refresh manually with Refresh on the PivotTable Analyze tab, or Refresh All on the Data tab, or by setting the option to refresh when the file is opened. Reports distributed on a schedule need somebody to own that step.