How to Edit Pivot Table | Modify Fields & Source Data

Click inside a pivot table to use the field list for rows, columns, values, and filters, or the Analyze tab to update the source data and refresh.

Whether you need to swap a dimension or pull from a fresh dataset, knowing how to edit a pivot table keeps your summary reports accurate without starting from scratch each time. The edit tools live in two places: the field list controls which data appears where, and the ribbon tabs handle the source range, formatting, and refresh. The same logic works in Excel and Google Sheets — you shape the summary by moving fields, not by typing over cells.

What Can You Change in a Pivot Table?

A pivot table lets you swap fields between Rows, Columns, Values, and Filters, change the source range it draws from, adjust number formatting, and switch aggregation methods like Sum to Count or Average. The underlying raw data stays untouched in its original table. Any edit you make reshapes only the summary view, and refreshing the pivot table re-applies those settings to the current data.

Edit Your Pivot Table: Field List and Source Data

The field list is your main control panel for rearranging the summary. In Excel, clicking anywhere inside the pivot table makes the field list appear on the right side of the window. If it stays hidden, right-click inside the pivot and choose Show Field List from the menu.

To move a field, drag it from one area to another — for example, pull a field from Rows down to Values to switch from a grouped list to a summed total. To remove a field, uncheck its box in the field list or click Remove next to its name. The field list shows four zones:

• Rows — categories that appear as row labels on the left
• Columns — categories that spread across column headers
• Values — the numeric data being summarized (sum, count, average, etc.)
• Filters — top-level filters that apply to the whole table

To change how a value is calculated, click the dropdown arrow on a field in the Values area and open Value Field Settings to switch from Sum to Count, Average, Max, Min, or another function.

Change the Source Data Range in Excel

When your source table gets new rows or columns, point the pivot table at the updated range instead of rebuilding it. Select any cell inside the pivot table, head to the Analyze tab (it appears when the pivot table is selected), and in the Data group click Change Data Source. The dialog shows the current range — select the new table or range and click OK. If the data sits in an external connection, choose Use an external data source and then Choose Connection. Microsoft’s official source data guide covers the full connection workflow including the Browse and New Source buttons.

Why Refreshing Matters After Edits

Changing the source range does not automatically update the pivot table. You have to run a refresh to pull in the new data. In Excel, right-click inside the pivot table and select Refresh, or go to the Analyze tab and click Refresh in the Data group. Google Sheets refreshes pivot tables automatically when the source data changes, but you can force a refresh by clicking the Refresh button in the side panel. A stale pivot table shows old numbers even after the source has been updated — make refresh a habit after every data change.

Edit a Pivot Table in Google Sheets

Google Sheets uses a side panel instead of a ribbon. Click any cell in the pivot table, then click the Edit button that appears just below the table. The panel opens showing Rows, Columns, Values, and Filters. Drag a field to a different section to move it, or click Remove to take it out. To change the source range, click Select data range at the top of the panel and choose the new cells. This feature requires a computer — the Google Sheets mobile app does not support pivot table editing.

Source data must have headers in the first row. If the column headers are missing, the pivot table won’t build or edit correctly. Google Sheets also refreshes pivot tables automatically when the data changes, so you rarely need a manual refresh step.

Formatting and Number Display Tweaks

Number formatting inside a pivot table works differently than formatting a normal cell range. Select the value cells you want to change, press Ctrl+1 (Windows) or Cmd+1 (Mac), and apply the format normally. The format sticks until the pivot table is refreshed or rebuilt. To change how empty cells appear, right-click inside the pivot table, open PivotTable Options, go to the Layout & Format tab, and enter a value in the “For empty cells show” box. Renaming a value field — for example changing “Sum of Sales” to just “Sales” — is done in the Pivot Field Name box on the Analyze tab.

Common Mistakes That Trip Up Editors

• Typing directly into a pivot cell — you can overwrite a value, but the change disappears on the next refresh. Use copy-paste-as-values if you need a static editable range.
• Forgetting to refresh after changing source data — the pivot table shows the old data until you refresh, which can lead to wrong decisions if you don’t catch it.
• Missing headers in the source data — Excel and Sheets both require column headers to build a pivot table. Without them, the field list shows generic names or the table fails to create.
• Blank rows or columns in the source — these break the data range detection in Excel and ONLYOFFICE. Keep the source data contiguous and fill or skip blank rows.
• Hidden field list in Excel — the pivot table still works, but you cannot edit it. Right-click inside the table and choose Show Field List to bring it back.

The consolidated workflow: Click inside the pivot table — use the field list to set rows, columns, values, and filters — change the source range from the Analyze tab if the data expanded — refresh to pull in updates — apply number formatting with Ctrl+1 — rename value fields for clean labels. That sequence covers 95% of everyday edits without rebuilding the table.