Excel: your first pivot table
A pivot table summarises a long list in seconds. If you have hundreds of sales rows, a pivot table can show the total for each person, month or product without a single formula. Do not worry about the name, as it is far simpler than it sounds.
Prepare your data
- Make sure your list has headings in the top row, one heading per column.
- Remove blank rows and columns inside the list.
- Do not mix types, such as text in a column of numbers.
- Tip: click inside the list and press Ctrl+T to turn it into a table, which grows automatically.
Create the pivot table
- Click any cell in your list.
- Click the Insert tab, then PivotTable.
- Check that the range looks right and choose New Worksheet.
- Click OK.
If you are unsure what you want, try Recommended PivotTables, next to the main button. Excel suggests some layouts.
Build the summary
On the right is a pane called PivotTable Fields, listing your column headings. Drag them into the boxes below:
- Rows: what you want listed down the side, such as Salesperson.
- Columns: an optional second split across the top, such as Month.
- Values: the numbers to add up, such as Sales.
- Filters: an optional drop-down at the top of the report.
Try it. Put Salesperson in Rows and Sales in Values. You now see each person's total.
Change the sum
If it says 'Count of Sales' instead of 'Sum of Sales', click the heading in Values, choose Value Field Settings and pick Sum, Average or Count.
Update after the data changes
Pivot tables do not refresh automatically. Right-click the pivot table and choose Refresh, or use the Refresh button on the PivotTable Analyze tab. The tab name can vary slightly between versions.
Common problems
- Numbers are being counted instead of added: some cells are probably stored as text.
- New rows are missing: refresh it, or check the range.
When to get more help
If your pivot table gives numbers you do not trust, send us the file and we will help you check them.