How to make a pivot table in Excel
Summarize a long table by category with Insert, PivotTable: put a field in Rows, put a number field in Values, and refresh when the data changes.
A pivot table turns a long list into a short summary: one row per category, with a total, count or average beside it. It reads your data without changing it, which is why it is the usual tool for “total by region” questions.
The hard part is rarely the dialog. It is having a clean source block and knowing which field belongs in Rows and which in Values.
Steps in Excel for Windows
- Make sure the data is a clean block. One header row, no blank rows or columns inside the data, and one kind of value per column. Click any cell in it.
- Insert the pivot table. On the Insert tab choose PivotTable, then From Table/Range. Check that the range shown is the whole table, and leave New Worksheet selected.
- Put a category field in Rows. In the PivotTable Fields pane, drag Region (or tick it) into the Rows area. Each distinct region becomes one row label.
- Put a number field in Values. Drag Sales into Values. Excel names it Sum of Sales and adds up the numbers for each row label, with a Grand Total at the bottom.
- Change the calculation if needed. Click the field in Values, choose Value Field Settings and pick Count, Average, Max or another summary. Number Format in the same dialog sets currency or decimals.
- Refresh after the data changes. A pivot table does not update by itself. Right-click it and choose Refresh, or press Alt+F5. Refresh All is Ctrl+Alt+F5.
Worked example
1. Total sales by region
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | East | Ana | 300 |
| 3 | West | Kim | 450 |
| 4 | East | Raj | 200 |
| 5 | West | Ana | 250 |
| 6 | East | Kim | 100 |
| A | B | |
|---|---|---|
| 1 | Row Labels | Sum of Sales |
| 2 | East | 600 |
| 3 | West | 700 |
| 4 | Grand Total | 1300 |
The five detail rows collapse to one row per region. East adds 300, 200 and 100 for 600, West adds 450 and 250 for 700, and the Grand Total is 1300. The pivot table sorts the labels alphabetically unless you change the order.
2. Check a pivot figure with SUMIFS
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | East | Ana | 300 | 600 | |
| 3 | West | Kim | 450 | ||
| 4 | East | Raj | 200 | ||
| 5 | West | Ana | 250 | ||
| 6 | East | Kim | 100 |
Before you rely on a pivot table, rebuild one number with a formula. This SUMIFS adds the Sales in rows 2 to 6 where the region in column A is East, and it agrees with the pivot table's 600.
Common mistakes
- Blank cells in the number column. If a column has a blank or a text entry, Excel defaults to Count instead of Sum and the numbers look wrong. Change the summary in Value Field Settings, and fix the text that should have been a number.
- Forgetting to refresh. Edit the source data and the pivot table keeps showing the old figures until it is refreshed. A total that disagrees with a SUM of the source usually means the refresh was skipped.
- Blank rows or merged cells in the source. A blank row splits the data, so the pivot table picks up only part of it. Merged header cells leave fields without names. Unmerge, delete the blank rows, then rebuild.
- Typing over the pivot table. You cannot overwrite pivot cells with your own numbers. Put extra calculations beside the pivot table or build them in the Fields pane.
- Grouping dates by accident. Newer versions of Excel may group a date field into months or years when you drag it in. Right-click a date label and choose Ungroup if you wanted the individual days.
On Excel job tests
A pivot-table prompt tends to be phrased as a summary: "show total sales by region and product", "how many orders per month". The row labels are the "by" words, and the figure being added is the thing named before them.
Our simulator does not build pivot tables yet, so this page teaches the Excel steps only. If a task allows formulas, SUMIFS and COUNTIFS give the same figures and are a good way to cross-check a pivot table.
Related guides
Questions
- Why does my pivot table show Count instead of Sum?
- Excel counts when the value column holds any text or blank cell. Fix the text entries, then change the summary to Sum in Value Field Settings.
- Does a pivot table update automatically?
- No. Refresh it with Alt+F5 or by right-clicking it and choosing Refresh after the source data changes.
- What does my data need to look like before I build a pivot table?
- One header row with a name in every column, no blank rows or columns inside the data, and one kind of value per column. Fix those first and the Fields pane lists every column by name.
- Where does the pivot table go?
- New Worksheet is the default and keeps it away from the source data. Choose Existing Worksheet only if you give it an empty area where it can grow.
Practice this
Our practice grid does not build pivot tables yet, so there is no matching lesson. The closest practice is the free 5-item sampler, which uses the same kind of tasks in a working grid.
Try a 5-item Excel test sample