Pivot tables in Excel turn a long table of records into a report you rearrange by dragging field names, then rebuild on demand. You build one from a clean source, drag fields into rows, columns and values, then refresh. Most pivot table problems are not build problems. They are refresh and source data problems, and they usually surface as numbers that look plausible and are quietly wrong.
Table of Contents
What is a pivot table in Excel and what does it do?
A pivot table is a report you assemble by dragging field names into four areas, without touching the source data. It groups records, totals numbers and rearranges in seconds. The underlying records are never altered, and the pivot rebuilds from them each time it refreshes.
Excel gives you four areas. Rows and Columns set the grid, Values holds the numbers being totalled and Filters narrows what is included. Microsoft’s PivotTable documentation notes that when you tick fields rather than drag them, “non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values”. It is a reasonable guess and frequently the wrong one.
Knowing which field belongs in which area is a skill rather than a purchase, and in our experience the constraint is rarely the software. When the Australian Bureau of Statistics asked employing Australian businesses what held back their use of technology in 2024-25, the most common answer was insufficient staff skills and capabilities at 16%, ahead of uncertainty about cost and benefit at 13%. A pivot table is the cheapest place to close that gap, because it is already installed. For where spreadsheets sit in a business more broadly, see our guide to Excel for business.
How do you create a pivot table in Excel?
Select any cell in your data, choose Insert then PivotTable, confirm the range and pick where the pivot goes. Excel opens an empty pivot and a field list. Drag fields into Rows, Columns and Values and the report builds as you go. It takes under a minute once the source data is in shape.
The source data is where it goes wrong. Microsoft is specific: “Make sure all columns have headers, with a single row of unique, non-blank labels for each column. Avoid double rows of headers or merged cells.”
- Clean the source. One header row, no merged cells, no blank rows through the middle and no totals row sitting under the data.
- Check that number and date columns really are numbers and dates. A value column stored as text will not sum, and in our experience that single fault causes more wrong pivots than any other.
- Select any cell inside the data and press Ctrl+T to format it as a table.
- Choose Insert then PivotTable, then New Worksheet.
- Drag the field you want to group by into Rows and the field you want totalled into Values.
- Click the Values field, open Value Field Settings and confirm it is summarising the way you intend rather than the way Excel guessed.
Step three is the one people skip, and it is what stops the range problem below. A pivot on a plain range remembers that range. A pivot on a table follows the table as it grows.
Getting the whole workbook into shape is a larger job, covered in our guide to spreadsheets people actually want to use.
How do you refresh a pivot table when the source data changes?
Unless you are on the Microsoft 365 Insider channel, a pivot table on worksheet data does not update on its own. Select it, open the PivotTable Analyze tab and choose Refresh. Alt+F5 does the same and Refresh All covers the workbook. The reliable fix is ticking refresh on file open under PivotTable Options, so nobody has to remember.
That position is changing, and it is worth knowing how far. Microsoft documents that “In newer versions of Excel, PivotTables can automatically refresh when connected to local workbook data”, while stating on the same page that “PivotTable Auto Refresh is currently available to participants of the Microsoft 365 Insider program”. Unless you run the Insider channel, assume manual refresh. Microsoft describes Auto Refresh only for local workbook data, which is the case that lacked it. A pivot fed by an external connection or a Power Query query has had timed refresh for years, under Queries and Connections.
Refresh and range are different problems, and confusing them costs people days. Refresh updates the pivot from the range it was told to watch. It does not widen that range. If the pivot was built on A1:F500 and someone has pasted rows down to F900, refreshing gives a current summary of the first 500 rows.
Why is my pivot table not updating, or throwing errors?
In the workbooks we rescue, five causes account for nearly every wrong pivot: the source range never expanded, nobody refreshed the pivot, the source data is not clean enough to summarise, a filter is quietly excluding part of the set or the source has been deleted. Only the last reliably errors. The rest return a number that looks reasonable.
That last case aside, a pivot does not raise an error when it goes wrong. It returns a number that is quietly wrong and waits for somebody to notice. Here is what each symptom usually means.
| What you see | Usual cause | Fix |
|---|---|---|
| New rows missing from the totals | Pivot built on a fixed range, not a table | Convert the source with Ctrl+T, then repoint it under Change Data Source |
| Numbers match last month, not this month | Nobody refreshed | Alt+F5, then tick refresh on file open |
| Count where you expected a total | Excel is reading the value column as text | Convert the column to real numbers, then set Value Field Settings to Sum |
| A row labelled (blank) | Blank cells or rows in the source | Remove the blank rows, fill or exclude the blank cells |
| The same category listed twice | Inconsistent spelling or trailing spaces | Standardise the values at source, then refresh |
| Totals lower than the raw data | A filter or slicer left applied | Clear every filter and check each slicer |
| “Data source reference is not valid” | The source sheet or named range was deleted, or the workbook filename contains square brackets | Restore the source and repoint under Change Data Source, or rename the file |
Three of those fixes are worth spelling out. To convert a text column to real numbers, select it, open Data then Text to Columns, choose Delimited, untick every delimiter, leave the format on General and click Finish. Do not click straight through, because Text to Columns remembers the last delimiter set and a stray comma will split the column in two. One column at a time. To repoint a pivot, open PivotTable Analyze, choose Change Data Source and pick the new range. After cleaning a source, set Number of items to retain per field to None under PivotTable Options, Data, then refresh twice. Excel keeps deleted items by default, so the old spelling lingers in filters and slicers until you do.
The cheapest detection test is two lines. SUM the source column and compare it with the pivot’s grand total, then COUNT that column against the number of data rows. The sum catches a stale range or a filter still applied. The count catches values stored as text, which both the pivot and SUM ignore, so the two totals agree while both understate the truth.
How does a pivot table end up wrong for six months?
A pivot table can report wrong totals for months without anyone noticing. The file opens normally, nothing errors and the numbers stay plausible, so there is no moment where someone is prompted to check. What follows is one workbook we rebuilt and the three faults that had been compounding in it.
A community services provider in Sydney came to us with a reporting workbook they had inherited. The person who built it had left and nobody knew how it worked. Under 10,000 rows of source data, which is small. The totals had been wrong for around six months.
Three faults compounded quietly. Some value columns were stored as text, so the pivot could not sum them. Blank rows created a (blank) category people had learned to scroll past. And a filter left applied on the pivot excluded part of the set every time.
The numbers looked plausible, which is why the faults survived several reporting cycles. We converted the text columns to real numbers, removed the blank rows and cleared the filter, then rebuilt the pivots on a table and set the workbook to refresh on open. The reporting stayed in Excel.
None of that is unusual. The error is almost never spotted on the page. It surfaces when somebody finally reconciles a total back to the raw data, and in the places we see it that happens rarely.
If a workbook your organisation runs on has drifted into that state, that is what our spreadsheet solutions team fixes.
How do you group dates and categories in a pivot table?
Right-click any value in the Rows or Columns area and choose Group. Dates group into days, months, quarters or years, and numbers into bands of any size you set. You can also select several row labels, right-click and group them by hand into a category the source data does not contain.
Date grouping is the one people use most. Drop a date field into Rows and Excel usually offers a months and years hierarchy automatically. If it does not appear, the column is usually stored as text rather than real dates, the same fault that turns a Sum into a Count. Automatic date grouping can also be switched off in Excel’s options.
Grouping cannot repair inconsistent values, and that is where we see it misused. A column where the same category has been entered several ways, or with trailing spaces, hands the pivot several categories rather than one. Case is not one of those variations, since a pivot treats Sydney and SYDNEY as the same item. The totals have already split before anyone reaches for Group.
Our position is that grouping is for presentation, never repair. Fix the source column first. Genuine roll-ups are the exception, and grouping fifteen cost centres into the three divisions the board actually reads is a reporting view rather than a correction.
How do you make a pivot table interactive with slicers and timelines?
Select the pivot, open PivotTable Analyze and choose Insert Slicer for category fields or Insert Timeline for dates. Both are visual filters that sit beside the report, so anyone can narrow it without touching the field list. One slicer can drive several pivot tables through Report Connections.
Slicers are the difference between a report you have to explain and one people use. A drop-down filter inside the pivot header hides its own state. A slicer shows it, because a filter nobody can see is a filter nobody clears. A slicer left on is still a filter left on. You can just see that it is.
Report Connections binds on the pivot cache, and Excel shares one cache across pivots built from the same range. The pivots that will not appear in the Report Connections list are those built from a different range, a second copy of the data or the Data Model. Cache sharing cuts both ways: group dates in one pivot and every pivot on that cache regroups with it.
Timelines are the same idea for dates, and on a reporting workbook they usually beat a date slicer. A date field in a slicer lists every individual date as its own button, which stops being usable past a few weeks. A timeline gives a sliding bar instead, with a separate control for days, months, quarters or years. A reader widens one month to a full year by dragging the bar.
How do you add a calculated field to a pivot table?
Open PivotTable Analyze, choose Fields, Items and Sets, then Calculated Field. Give it a name and a formula referring to other field names, for example =Revenue-Cost. It appears in the field list like any other value. Use it when the calculation is not in the source data.
One behaviour here catches almost everyone, and Microsoft documents it precisely: “Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula.” Their example is that =Sales * 1.2 “multiplies the sum of the sales for each type and region by 1.2”, rather than multiplying each individual sale and then adding the results.
For addition and subtraction that makes no difference. For division, percentages or margins it changes the answer into a number that still looks credible. A margin calculated on summed totals is not the average of the row-level margins.
Excel offers three ways to add a calculation and they differ in what they calculate on, which is the whole decision.
| Approach | Calculates on | Use it when |
|---|---|---|
| Calculated field | The summed totals of the fields referenced | The maths is addition or subtraction, or you want the ratio of the totals |
| Calculated item | Individual items within one field | You need a formula comparing specific categories, one region against another |
| Helper column in the source | Each row, before the pivot summarises | You need a per-row flag or band, for example on time against late, then counted or totalled |
For a margin, do not add a margin column and sum it, because thirty rows at 20% sum to 600%. Put revenue and cost in Values as separate sums, then add a calculated field for the ratio. Less elegant, and correct.
Can pivot tables handle large datasets?
Yes, well past the point most businesses reach. Microsoft’s published limits put the ceiling at 1,048,576 unique items per field, on a worksheet of the same row count, with row and column fields bounded only by available memory. Past a worksheet you need a different container, but volume is rarely the reason a pivot table fails.
The Sydney community services workbook was under 10,000 rows and had been wrong for half a year. Size had nothing to do with it. Pivot tables break on structure, not scale, which is why “pivot tables cannot cope with big data” is usually the wrong diagnosis. Where size does bite is refresh time and file weight, long before any documented limit. That is a legitimate reason to move the reporting somewhere else. Hitting a ceiling almost never is.
When is a pivot table the wrong tool?
A pivot table summarises. It is the wrong tool when the job is looking up a single matching value, when the data needs cleaning or combining first or when the report must refresh and distribute itself with nobody opening the file.
For pulling one matching value out of another table, use a lookup formula, covered in our VLOOKUP and XLOOKUP guide. For cleaning, reshaping or combining data before it reaches the pivot, that work belongs to Power Query, which sits alongside the other methods in the Excel data analysis guide. For reporting that refreshes on a schedule and reaches people without anyone opening a workbook, start with our comparison of Power BI dashboards and reports.
What we push back on is treating any of those as a reason to replace the spreadsheet wholesale. CPA Australia’s Asia-Pacific Small Business Survey of more than 4,100 businesses across the region, reported in April 2026, found only 30% of the Australian small businesses surveyed said their 2025 technology investment improved profitability. Our read is that switching tools without naming the constraint explains much of it. Replacing a workbook because it feels dated buys a platform that reproduces the same broken report.
Frequently asked questions
Do pivot tables update automatically when the source data changes?
For a pivot on worksheet data, no, unless you are on the Microsoft 365 Insider channel. A pivot holds a cached copy of the source and rebuilds only when you refresh it, with Alt+F5 or Refresh All. Microsoft has introduced Auto Refresh for pivots on local workbook data, but its documentation states the feature is currently available to Microsoft 365 Insider participants. Tick “Refresh data when opening the file” under PivotTable Options either way.
Why does my pivot table show Count instead of Sum?
Excel defaults to Sum when it reads the column as numeric and Count when it does not. Almost always the column holds text or numbers stored as text. Fix the column at source, refresh, then set Value Field Settings back to Sum. Changing the setting without fixing the data gives a total that silently excludes the text entries.
Why does my pivot table show a (blank) row?
Blank cells or blank rows inside the source range produce a category labelled (blank). Remove blank rows running through the data, fill genuine gaps in the category column and check the source range does not extend past the last real row. If the source is an Excel table rather than a fixed range, trailing empty rows stop being an issue.
Can someone see the source data behind a pivot table I send them?
Yes, and it catches organisations out. A pivot stores a cached copy of its source inside the file, and double-clicking a value recreates the underlying rows on a new sheet. In December 2023 Cambridge University Hospitals NHS Foundation Trust disclosed two breaches on this mechanism, stating that “Excel actually stores the underlying data it uses to generate the Pivot table in the background”. Before sending a pivot externally, paste it as values into a new file.
What is the difference between a pivot table and a PivotChart?
A pivot table is the summarised numbers in a grid. A PivotChart is a chart built on that same summary, and the two stay linked, so filtering one filters the other. A PivotChart cannot exist without an underlying pivot table. Use the table when people need exact figures and the chart when the pattern matters more than the precise number.
How many rows can a pivot table handle?
Microsoft’s published specifications allow 1,048,576 unique items per field and 256 value fields per report, plus 256 report filters where memory permits, on a worksheet limited to 1,048,576 rows by 16,384 columns. In practice refresh speed and file size bite long before those ceilings.
Where to start
If a report your organisation depends on is built on pivot tables, check three things before learning any new feature: whether the pivot sits on an Excel table or a fixed range, whether the workbook refreshes on open and whether every value field is summarising the way you think it is.
The value field check alone would have caught the Sydney community services workbook on day one. The other two stop a workbook drifting in the first place. If yours turns up more than you want to fix alone, book a free call and our reporting and analytics team will look at it with you.
