How to Create a Pivot Table in Excel
Step-by-step guide to building a PivotTable in Excel: insert it, drag fields into Rows, Columns, and Values, switch Sum to Count, and refresh after edits.
Excel builds a PivotTable in a few clicks: click inside your data, go to Insert > PivotTable, then drag fields into the Rows, Columns, and Values boxes in the PivotTable Fields pane. From there you swap the default Sum for Count or Average, and refresh the table any time the source numbers change.
Insert the PivotTable
Start with clean data: one header row, no blank columns, no merged cells. A drop-down list on any text column keeps entries spelled consistently, which avoids rows splitting into near-duplicates later. Then follow these steps.
| Step | What to do |
|---|---|
| 1 | Click any single cell inside your data range, or select the full range including the header row. |
| 2 | Go to the Insert tab and select PivotTable. |
| 3 | Confirm the range Excel picked, then choose New Worksheet or Existing Worksheet for where the PivotTable goes. |
| 4 | Select OK. Excel opens a blank PivotTable and the PivotTable Fields pane on the right. |
Drag fields into Rows, Columns, and Values
The PivotTable Fields pane lists every column header as a checkbox, with four boxes underneath: Filters, Columns, Rows, and Values. Checking a box places that field automatically — text and dates to Rows, numbers to Values — but dragging a field into any box overrides that. For a sales-by-region sheet, drag Region to Rows, Month to Columns, and Sales to Values, and the numbers cross-tabulate instantly.
Switch the summary from Sum to Count
Excel sums numeric fields and counts text or blank fields by default. To change that, right-click any number in the PivotTable and choose Summarize Values By, then pick Sum, Count, Average, Max, or Min. Value Field Settings, on the same menu (or from the PivotTable Analyze tab’s Active Field group), adds more functions and lets you rename the field.
Refresh the PivotTable when data changes
A PivotTable does not update on its own. After editing the source data, right-click anywhere inside the table and select Refresh, use the Refresh button on the PivotTable Analyze tab, or press Alt+F5. Refresh updates one table; Refresh All, from the same tab, updates every PivotTable in the workbook.
| Situation | Fix |
|---|---|
| Values changed, same rows/columns | Refresh (right-click, or Alt+F5) |
| New rows added below a plain range | Extend the range via Change Data Source on the PivotTable Analyze tab |
| New rows added to a formatted Excel Table | Just Refresh — the table’s range grows automatically |
That last row is why it pays to convert source data to a formatted Table before building the PivotTable: rows added later get picked up on refresh with no manual range editing. If your workflow also pulls matching values from another sheet before summarizing them, see our guide to using VLOOKUP in Excel.
Keep the source data and the PivotTable on separate sheets so a stray edit does not land in your raw numbers, and refresh right after every paste or import. That habit alone heads off most “my totals don’t match” complaints.