PivotTables have a reputation for being complicated, but they’re one of the easiest ways to make sense of a big spreadsheet. With a few clicks — and no formulas — you can turn thousands of rows of sales, expenses or orders into a tidy summary.
Want to know total sales by region? Or by month? Or by salesperson and product? A PivotTable answers each question in seconds.
Step 1: Get your data ready
PivotTables work best with a simple, tidy list. Check that:
- Every column has one heading in the first row (for example: Date, Region, Salesperson, Product, Sales).
- There are no completely blank rows or columns in the middle.
- Each column holds one type of information — dates in the Date column, numbers in the Sales column.
Tip: Click inside your data and press Ctrl + T (Command + T on a Mac) to turn it into a table. When you add new rows later, your PivotTable will pick them up automatically.
Step 2: Create the PivotTable
- Click any cell inside your data.
- Go to Insert > PivotTable. (In some versions you’ll then choose From Table/Range.)
- Excel selects your data automatically. Check the range looks right.
- Choose New Worksheet.
- Click OK.
A new sheet opens with an empty PivotTable on the left and the PivotTable Fields pane on the right.
Step 3: Drag fields into place
The Fields pane lists every column heading from your data. Below it are four boxes:
| Box | What it does | Example |
|---|---|---|
| Rows | Creates a row for each item | Region |
| Columns | Creates a column for each item | Month |
| Values | The numbers to add up | Sales |
| Filters | Lets you filter the whole table | Year |
Try this: drag Region into Rows and Sales into Values. You instantly get total sales for each region, with a Grand Total at the bottom.
Now drag Product into Columns — each region is broken down by product.
Don’t like it? Drag fields out of the boxes and try something else. You can’t break your original data.
Showing Count instead of Sum?
If your numbers show as a count rather than a total, Excel thinks the column contains text (often because of a blank or a stray word).
- Click the field in the Values box.
- Choose Value Field Settings…
- Select Sum and click OK.
The same menu lets you switch to Average, Max, Min and more.
Make the numbers look nice
In the Value Field Settings window, click Number Format to add currency symbols or thousand separators. This formatting sticks, even when you refresh.
Group dates by month or year
Drag a Date field into Rows and Excel usually groups it by year, quarter and month automatically. If it doesn’t, right-click any date in the PivotTable and choose Group, then pick Months and Years.
Refresh your PivotTable
PivotTables don’t update automatically when your data changes. To refresh:
- Right-click anywhere in the PivotTable and choose Refresh, or
- Press Alt + F5, or
- Use Data > Refresh All to refresh every PivotTable in the workbook.
Not sure where to start?
Click in your data and choose Insert > Recommended PivotTables. Excel previews a few summaries it thinks are useful. Pick one and tweak it from there.
Troubleshooting
“The PivotTable field name is not valid.” One of your columns has a blank heading. Give every column a name.
New rows aren’t included. Your data isn’t a table, so the PivotTable is still using the old range. Go to PivotTable Analyze > Change Data Source and update it — or convert your data to a table with Ctrl + T.
The Fields pane has disappeared. Click inside the PivotTable. If it’s still hidden, go to PivotTable Analyze > Field List.
Wrapping up
Tidy data in, Insert > PivotTable, then drag and drop. Start with one field in Rows and one in Values, and build up from there.
