Edit a PivotTable by updating its source data, refreshing the report, and adjusting fields and layout using the Analyze and Design tabs.
Learning how to edit a pivot table in Excel starts with the source data. Unlike a regular table, you can’t type directly into a pivot table’s values. Instead, you make changes in the original data range, then refresh the pivot table to see the updates. You can also rearrange fields, rename labels, and apply formatting — all from the dedicated PivotTable Analyze and Design tabs.
How to Update a PivotTable’s Source Data
The most common edit is updating the underlying data that feeds the pivot table. You modify the source table or range, then refresh the pivot table to pull in the changes.
- Select any cell inside the pivot table.
- Go to the PivotTable Analyze tab (on some versions it’s just Analyze).
- In the Data group, click Change Data Source.
- In the dialog box, choose Select a table or range and enter the new range, or pick Use an external data source and select a connection.
- Click OK.
- Right-click the pivot table and choose Refresh, or go to PivotTable Analyze > Refresh.
If the source range grew (new rows or columns added), the Change Data Source step must include the expanded range. Otherwise the new data won’t appear. Change the source data for a PivotTable covers this in detail. One important limit: if the pivot table uses the Workbook Data Model, you cannot change its data source. The option is grayed out.
A successful refresh updates all calculated values and totals in the report.
How to Adjust the Fields Your PivotTable Shows
To change what data appears in rows, columns, and values, use the PivotTable Fields pane. It appears automatically when you click inside the pivot table. If it doesn’t, click Field List on the PivotTable Analyze tab.
- Add a field: Check its box in the field list. It lands in a default area (usually Rows).
- Move a field: Drag it between the Rows, Columns, Values, or Filters sections.
- Remove a field: Uncheck the box or drag it out of the list.
- Rename a label: Click directly on the row or column heading cell in the pivot table and type a new name. This only changes the display, not the source data.
These changes let you shift how the pivot table summarizes your data without altering the original spreadsheet.
| Editing Action | Key Steps | Notes |
|---|---|---|
| Update source data | Select PivotTable → Analyze → Change Data Source → enter new range → Refresh | New rows/columns must be inside the specified range |
| Refresh after edits | Right-click PivotTable → Refresh, or Analyze → Refresh | Required whenever source data changes |
| Add or remove fields | Click inside PivotTable → Fields pane → check/uncheck fields | Fields pane appears when clicking inside |
| Move fields between areas | Drag field in Fields pane between Rows, Columns, Values, Filters | Instantly changes report layout |
| Rename row/column labels | Click the label cell and type a new name | Does not affect source data |
| Change pivot table style | Select PivotTable → Design tab → pick a style | 85 built-in styles available (Microsoft Press) |
| Change data source to external | Analyze → Change Data Source → Use an external data source → select connection | May require database administrator for connection settings |
How to Change a PivotTable’s Layout and Formatting
The Design tab gives you control over the pivot table’s appearance, from subtotals and totals to cell styles. You have 85 built‑in styles to choose from, as documented by Microsoft Press. To customize a style, duplicate an existing one in the gallery and edit it in the Modify PivotTable Quick Style dialog box.
Common layout tweaks include:
- Turning subtotals on/off (Design > Subtotals)
- Showing grand totals for rows and columns (Design > Grand Totals)
- Switching to Tabular Form (Design > Report Layout)
- Repeating item labels so every row shows its category (Design > Report Layout > Repeat All Item Labels)
- Removing blank rows between groups (Design > Blank Rows)
These formatting changes affect only the report’s look, not the underlying data.
Where People Get Stuck Editing PivotTables
Even experienced users make the same handful of mistakes. Knowing them ahead of time keeps your pivot table accurate.
| Mistake | What Happens | How to Avoid |
|---|---|---|
| Forgetting to refresh after source change | PivotTable shows old data | Always click Refresh after editing source data |
| Adding rows outside original source range | New rows not included in report | Update Change Data Source to include the new range |
| Clicking outside the pivot table | Analyze tab disappears | Select any cell inside the pivot table |
| Trying to change data model source | Option grayed out | Create a new pivot table from a regular range instead |
| Renaming a value field expecting a formula change | Field name changes only display | Use Calculated Field for custom calculations |
| Using Excel for the web for advanced edits | Some features missing | Use desktop Excel for full PivotTable control |
| Not checking external connection dependencies | Data may break if connection file moves | Update connection file location in Change Data Source |
Three Steps to Edit Any PivotTable
- Update the source data — add, remove, or change records in the original table or range.
- Adjust the pivot table’s range — use Change Data Source if new rows or columns were added, then Refresh.
- Tweak fields and layout — use the Fields pane for data display and the Design tab for formatting.
That workflow covers 99% of edits you’ll ever need. The one exception: pivot tables based on the Workbook Data Model cannot have their source changed, so plan accordingly when building the report.
References & Sources
- Microsoft Support. “Change the source data for a PivotTable.” Official steps for updating source, including external data and the Data Model limitation.
- Microsoft Press Store. “Customizing a pivot table.” Documents the 85 built‑in styles and style customization.