How to create a pivot table in Excel
Pivot tables are the fastest way to summarise a spreadsheet in Excel — totals by category, counts, averages — without writing a single formula. Here’s how to build one step by step, the choices that trip people up, and an even quicker way to get the same summary automatically.
The five steps, start to finish
A pivot table needs clean columns and a clear question.
Before anything, make sure your data is in a clean, contiguous table: one header row, no blank rows or columns, one value per cell. Pivot tables break on merged cells and gaps. Select any cell in the range, then Insert → PivotTable, and let Excel pick the range and drop the pivot on a new sheet.
Now the core move: the field list has four zones — Rows, Columns, Values and Filters. Drag the field you want to group by into Rows (e.g. Region), the field you want to measure into Values (e.g. Revenue), and Excel sums it by default. Drag a second field into Columns to cross-tabulate (e.g. Region by Month).
Two choices trip people up. First, the summary type: click the Values field → Value Field Settings to switch from Sum to Count, Average, Max or Min — this is usually the actual question you’re answering. Second, refresh: a pivot table is a snapshot, so when the underlying data changes you must right-click → Refresh (it does not update live).
The pivot table field zones
Where each field goes, and what it does.
| Zone | Put here | Result |
|---|---|---|
| Rows | The category to group by | One row per value (e.g. per Region) |
| Columns | A second category | Cross-tab across the top (e.g. per Month) |
| Values | The number to measure | Summarised: sum / count / average |
| Filters | A field to slice by | A dropdown to filter the whole pivot |
The mistakes that make a pivot table wrong
Most bad pivots come from the data, not the pivot.
The commonest problem isn’t the pivot — it’s the source. If a numeric column is stored as text, Sum silently ignores it and your total is wrong with no warning. If there are blank rows, the pivot range stops early and misses data. And if categories are inconsistent (‘UK’ vs ‘U.K.’ vs ‘United Kingdom’), they split into separate rows that should be one.
So the reliable workflow is: clean the data first (consistent types, no blanks, standardised categories), then pivot. A five-minute data check up front saves a wrong total in front of your boss.
The faster way: summarise automatically
Skip the field-dragging and get the summary in one step.
Pivot tables are powerful but manual: you drag fields, pick summaries, and rebuild for each question. DataHub Pro gives you the same summaries automatically. Upload the spreadsheet and it detects your categories and numbers, builds the breakdowns (totals by category, trends over time, top contributors) and shows them as a dashboard — no field list, no refresh button.
You can still ask specific questions in plain language (‘total revenue by region this quarter’) and get an auditable answer that cites the rows behind it. For anyone who finds pivot tables fiddly, it’s the same result without the setup. Try it on a file and see the summary in about two minutes.
Frequently asked questions
How do I create a pivot table in Excel?
Select any cell in your data, go to Insert → PivotTable, then drag a category field into Rows, a number field into Values (it sums by default), and optionally a second category into Columns. Use Value Field Settings to switch Sum to Count or Average, and right-click → Refresh when the data changes.
What is the best way to create a pivot table?
Start with clean, contiguous data (one header row, no blank rows, consistent types and category labels). Put the field you group by in Rows, the number you measure in Values, and choose the right summary (sum vs count vs average). Clean data first — most wrong pivots come from the source, not the pivot.
Why is my pivot table total wrong?
Usually because a numeric column is stored as text (Sum ignores it), there are blank rows that cut the range short, or category labels are inconsistent and split into separate rows. Fix the source data, then refresh the pivot.
Do pivot tables update automatically?
No. A pivot table is a snapshot of the data at creation. When the source changes you must right-click the pivot and choose Refresh, or set it to refresh on file open. Tools like DataHub Pro rebuild the summary automatically when you upload new data.
What’s an alternative to pivot tables?
If you want the same category summaries without dragging fields, a spreadsheet-native tool like DataHub Pro auto-summarises an uploaded file into dashboards and lets you ask questions in plain language, with every number auditable to its source rows.
Can I make a pivot table from multiple sheets?
Yes, using the Data Model / Power Pivot to relate tables, but it’s advanced. A simpler route is to combine the sheets into one clean table first, or upload the files to a tool that blends them for you.
Explore related guides
More Excel guides and AI shortcuts.
Summarise any spreadsheet, no pivot required
Upload a file and DataHub Pro builds the category breakdowns and trends automatically — the pivot-table result without the field-dragging. Free tier, then $14.99/mo.
Try it free on your file →