To edit pivot table data in Excel, update the source range via Analyze > Change Data Source, then Refresh to apply changes.
To edit pivot table data in Excel, you update the source range and refresh — you never type into the summary cells. A pivot table is a dynamic summary, so editing means changing where its data comes from or how it’s arranged. The steps depend on whether you’re expanding the raw data, swapping fields, or altering calculations.
How Do You Change the Source Data Range?
The most common edit is expanding the source range when you add rows or columns to the underlying worksheet. Click any cell inside the pivot table, then go to the Analyze tab (labeled PivotTable Analyze in some versions). In the Data group, click Change Data Source, then choose Select a table or range. Enter the new range (e.g., Sheet1!$A$1:$E$100) or use the collapse button to select it on the sheet. Click OK.
If the pivot table uses the Workbook Data Model, Microsoft notes this method won’t work — you must change the connection instead. After changing the source, always refresh to see the new data.
The pivot table numbers update to include the new rows or columns immediately after refresh.
How to Refresh After Editing Source Data
Refresh is mandatory after any source change. Right‑click inside the pivot table and choose Refresh, or go to the Analyze tab and click Refresh. You can also set the pivot table to refresh automatically when the workbook opens via PivotTable Options > Data > Refresh data when opening the file.
Editing Pivot Table Data: The Step Order That Works
The reliable workflow is: add new rows or columns to the source data (keeping a clean column‑header row and no blank rows/columns), then select the pivot table, open Analyze > Change Data Source to include the added cells, and finally click Refresh. Skipping the source‑range update is the most common mistake — new data stays hidden until the range is expanded.
| Edit Type | Steps | Notes |
|---|---|---|
| Change data source | Analyze > Change Data Source > enter new range | Works for table/range pivot tables; not for Data Model pivots |
| Add a field | Check a field in the PivotTable Fields pane | Appears in the default area |
| Remove a field | Uncheck the field or drag it out of its area | Layout updates instantly |
| Change aggregation | Right‑click a value > Value Field Settings > choose Sum/Count/Average | Switches the calculation type |
| Change sorting | Click the dropdown on a row/column label > Sort A to Z or More Sort Options | Affects display order |
| Add a calculated field | Analyze > Fields, Items & Sets > Calculated Field > build formula | Custom calculations using existing fields |
| Move field to different area | Drag a field between Rows, Columns, Filters, or Values | Rearranges the entire layout |
Can You Change the Summary Calculation?
Yes — change the aggregation on any value field by opening Value Field Settings. Right‑click a cell in the Values area, choose Value Field Settings, then pick a different function like Sum, Count, Average, Max, or Min. This only recalculates the existing field; it doesn’t alter the source data.
What If the Pivot Table Won’t Update?
When the pivot table doesn’t show new data after refreshing, first verify the source range actually covers the added rows or columns. Also check that the source table has no blank rows or columns and that every column has a header. If the pivot is based on a Data Model, you need to update the connection manually or rebuild the pivot table.
Editing Pivot Table Layout Without Changing Data
You can rearrange fields and filter without touching the source data. Use the PivotTable Fields pane to drag fields between areas — drop them into Rows, Columns, Filters, or Values. Slicers and timelines provide interactive filtering, and changes apply immediately with no need to refresh.
| Problem | Cause | Fix |
|---|---|---|
| Data not updating after refresh | Forgot to expand source range | Use Change Data Source to include new cells |
| New rows missing | Range too narrow | Expand the range in the Change Data Source dialog |
| Blank cells inside pivot table | Source data has empty cells | Fill blanks or filter them out in source |
| Wrong totals (e.g., Sum instead of Count) | Incorrect aggregation setting | Change in Value Field Settings |
| “Change Data Source” is grayed out | Pivot table not selected | Click any cell in the pivot table first |
| Field doesn’t appear in field list | Header has spaces or special characters | Rename header in source data to remove spaces |
| Pivot table based on Data Model | Cannot change source via dialog | Edit the connection or recreate pivot table |
Getting the Edit Right Every Time
The three‑step core process covers nearly every edit: (1) add or modify data in the source table, (2) update the source range in Analyze > Change Data Source, and (3) Refresh the pivot table. For layout or calculation changes, use the PivotTable Fields pane and Value Field Settings — those don’t need a source range update. Stick to clean, header‑only source data, and your pivot table will stay accurate and easy to maintain.
References & Sources
- Microsoft Support. “Change the source data for a PivotTable.” Official steps for updating the data range and when the Data Model applies.