How to Make a Pivot Table in Excel (Step by Step)
A pivot table summarizes a large table into a short, readable report in seconds. In Excel: select your data, click Insert > PivotTable, then drag fields into Rows, Columns, and Values. Here is the full walkthrough.
A pivot table is the fastest way to turn a long spreadsheet into an answer. Instead of writing formulas, you drag fields around and Excel does the math — totals, counts, and averages update instantly. This guide uses a simple sales table, but the steps work for any data.
Step 1: Prepare your data
A pivot table needs clean, tabular data: one row of headers at the top, and no fully blank rows or columns inside the range. Each column should hold one kind of value (dates in one, amounts in another). If your headers are missing, add them first — the pivot table uses them as field names.
Step 2: Insert the pivot table
- Click any cell inside your data.
- Go to the Insert tab and click PivotTable.
- Excel auto-selects your range. Choose New Worksheet and click OK.
- A blank pivot table appears with the PivotTable Fields pane on the right.
Step 3: Build the report with Rows, Columns, and Values
In the Fields pane, drag each field into one of four areas. This is where the summary takes shape.
| Area | What it does | Example |
|---|---|---|
| Rows | Groups data down the left side | Product |
| Columns | Groups data across the top | Region |
| Values | The number being calculated | Sum of Sales |
| Filters | Narrows the whole report | Year |
Drag Product to Rows and Sales to Values, and you instantly get total sales per product. Add Region to Columns to break it down further.
Common pivot table problems (and quick fixes)
- "PivotTable field name is not valid" — your data is missing a header. Add one and rebuild.
- Numbers show as Count instead of Sum — a blank or text cell is in your number column. Clean the column.
- New data not showing — right-click the pivot table and choose Refresh, or use a Table as the source so it expands automatically.
Pivot tables pair well with lookups. Once you can summarize data, learn how to use VLOOKUP in Excel to pull matching details into your report. And if you work in Google Sheets instead, see how to make a pivot table in Google Sheets.
Frequently asked questions
What is a pivot table used for?
To summarize large amounts of data quickly — totals, counts, and averages grouped by category — without writing formulas. It answers questions like "total sales per product per region" in seconds.
Do pivot tables update automatically?
Not by default. Right-click and choose Refresh after your data changes. If you build the pivot table from an Excel Table (Ctrl+T), new rows are included automatically on refresh.