Editing a pivot table lets you update its data source, adjust fields, refresh calculations, and reformat the layout to show exactly what you need.
A pivot table is a snapshot of your source data, not a live feed. When the numbers behind it shift — new rows added, old figures corrected, categories renamed — the table stays frozen on the old version until you act. The first thing to understand about how to edit a pivot table is that the table won’t refresh itself. Every edit runs through the Analyze and Design tabs, and those tools only appear when a cell inside the pivot table is selected.
Editing a Pivot Table: The Step Order That Works
Editing a pivot table always follows the same sequence. Select any cell inside the table, which activates the PivotTable Analyze and Design tabs on the ribbon. Then decide what needs to change — the source range, the fields shown, the calculation, or the layout. Each edit is a separate action, and after any change to the underlying data, a refresh is required before the table updates.
How To Change The Data Source In Excel (Windows)
The most common edit is expanding or replacing the data your pivot table draws from. On Windows, select the table, go to Analyze > Change Data Source, and choose a new table or range in the dialog that opens. Microsoft’s official guidance on changing a PivotTable data source also covers external connections and the Browse for More option. Confirm with OK, then run a refresh — the table now displays the updated data from the new range.
How To Change The Data Source On Mac
On Mac, the same process uses a differently named tab. Select a cell inside the pivot table, click PivotTable Analyze, then Change Data Source. The rest of the dialog works the same way — choose the new range and confirm.
What Happens If You Edit The Source Data Without Refreshing?
The pivot table shows stale numbers. Every change you make to the source cells — typing a new value, adding a row, deleting a column — is invisible to the pivot table until you tell it to refresh. To refresh, right-click inside the pivot table and choose Refresh, or go to Analyze > Refresh. After the refresh runs, the table recalculates from the current source range and the updated values appear in place.
Rearranging Fields In The Pivot Table
The PivotTable Fields pane lists every column from your source data. Checking a box adds it to the table in its default area. To move a field, drag it from one area to another in the pane — from Rows to Columns, from Values to Filters, or remove it entirely by unchecking it or dragging it out. The table updates instantly as fields are moved, so you see the new arrangement without an extra step.
| Task | Steps | Key Detail |
|---|---|---|
| Change data source (Windows) | Select table → Analyze → Change Data Source → select new range → OK | The new range must include every row and column you need |
| Change data source (Mac) | Select table → PivotTable Analyze → Change Data Source | Same dialog, different tab name |
| Refresh after source edits | Right-click inside the table → Refresh, or Analyze → Refresh | Required after every change to the source data |
| Add a field to the table | Check the field in the PivotTable Fields pane | The field appears in its default area |
| Move a field between areas | Drag the field from one area box to another in the Fields pane | Rows, Columns, Values, and Filters are all accessible |
| Remove a field from view | Uncheck the field or drag it out of the Fields pane | The source data is unaffected |
| Change the report layout | Design → Report Layout → choose Show in Tabular Form | Makes the table resemble a flat spreadsheet |
Formatting And Layout Adjustments
Pivot tables come with subtotals, grand totals, and a compact layout by default. To clean up the look, go to the Design tab and use Subtotals to turn them off or reposition them. Grand Totals can be disabled the same way. Report Layout offers Show in Tabular Form, which repeats category labels in each row and removes blank lines — the result looks like a standard spreadsheet that can be copied or printed cleanly.
Why Can’t You Change The Data Source?
If the Change Data Source option is grayed out or missing, the pivot table is likely based on the Workbook Data Model. Microsoft blocks source changes on Data Model tables, so the workaround is to rebuild the pivot table from a standard worksheet range or table instead of the Data Model connection.
Editing Pivot Tables In LibreOffice Calc
LibreOffice Calc calls its pivot tables Data Pilots. To edit one, click a cell inside the table, open the context menu, and choose Properties to open the Pivot Table Layout dialog. Fields can be rearranged by drag-and-drop, moved from columns to rows, or removed entirely by dragging them out of the layout area.
| Mistake | Why It Happens | The Fix |
|---|---|---|
| Analyze and Design tabs don’t appear | A cell inside the pivot table isn’t selected | Click any cell inside the table to activate the tabs |
| New data doesn’t show in the table | Source data was edited but the table wasn’t refreshed | Right-click inside the table and choose Refresh |
| New rows are missing from the table | The source range in Change Data Source wasn’t expanded | Use Change Data Source to widen the range to include the new rows |
| The field list is empty or missing | The pivot table isn’t currently selected | Click inside the table to bring the field list back |
| Change Data Source is grayed out | The pivot table uses the Workbook Data Model | Rebuild the table from a standard worksheet range instead |
Get The Workflow Right Every Time
The cleanest routine for editing a pivot table follows four steps. First, make all changes to the source data — add rows, fix values, rename categories. Second, click inside the pivot table to activate the tools. Third, update the source range if new rows or columns were added. Fourth, run a refresh. The table now reflects the current data, and the fields can be rearranged or formatted from there without repeating the source setup.
References & Sources
- Microsoft. “Change the source data for a PivotTable.” Official Microsoft support article covering data source changes, refresh, and the Workbook Data Model limitation.