A PivotTable turns a long list of rows into a summary you can reshape in seconds: totals by region, counts by product, share of the whole. It is one of Excel’s most useful tools, and it works best when the source data is clean. This guide follows Microsoft’s steps for creating one in Excel on Windows.
Step 1: Prepare your source data
- Organize data in columns with a single header row.
- Give every column a unique, non-blank label.
- Avoid double header rows and merged cells.
- Format the data as an Excel table, or use Power Query to reshape complex or nested data.
Clean, tabular data gives the best results. Most PivotTable frustration traces back to messy source data.
Step 2: Insert the PivotTable
- Select the cells you want to use.
- Go to Insert > PivotTable.
- Confirm the table or range. You can optionally add the data to the Data Model.
- Choose whether the report goes on a new or existing worksheet.
- Select OK.
The dropdown arrow on the PivotTable button also lets you build from an external data source, the Data Model, or Power BI datasets.
Step 3: Arrange the fields
In the PivotTable Fields pane, check a field to add it. Excel places it automatically:
| Field type | Default area |
|---|---|
| Text | Rows |
| Date and time | Columns |
| Numeric | Values |
Drag fields between areas to rearrange the layout until the report reads the way you want.
Step 4: Change how values are summarized
- Values default to Sum, but text data is counted instead, so avoid mixing data types in a value field.
- Open Value Field Settings to choose a different calculation under Summarize Values By, or to set a number format.
- Use the Show Values As tab to display values as percentages, such as a percent of the grand total.
Step 5: Keep it up to date
- One PivotTable: right-click it and select Refresh.
- Several PivotTables: select a cell in any of them, then choose PivotTable Analyze > Refresh > Refresh All.
A PivotTable doesn’t update itself when the source data changes, so refresh before you share numbers.
Tips from Microsoft
- Rename fields last. Changing the summary calculation changes field names, so finish the setup before you rename anything.
- Remove “Sum of” quickly. Press Ctrl+H and replace “Sum of ” with nothing.
- Recommended PivotTables: these use AI analysis as part of Microsoft’s connected experiences, and they are disabled if you opt out of connected experiences.
