Google Sheets Pivot Tables: How to Edit and Update

To change a pivot table in Google Sheets, click a cell inside it to open the Pivot table editor on the right, then edit its Rows, Columns, Values, or Filters settings. To include different source data, change the data range at the top of the editor. The original source table is not changed when you rearrange or summarize a pivot table.

What a pivot table does

A pivot table turns a larger set of records into a compact summary. Instead of manually adding every sale for each product or counting every response in a survey, you choose fields to group and summarize. For example, a sales list with columns for date, region, product, and amount could become a table of total sales by region and product.

In Google Sheets, the pivot table is a separate summary built from a selected source range. Its editor gives you controls for placing fields into rows and columns, choosing values to calculate, and filtering which records appear. Changing those settings changes the summary’s arrangement or calculation; it does not rewrite the underlying records.

You can create a pivot table in a browser from a selected data range using Insert > Pivot table. Choose whether to place it in a new sheet or an existing sheet. To edit an existing pivot table, select a cell in its output and use the editor that appears. If the editor is closed, clicking within the pivot table usually opens it again.

Change rows, columns, values, and filters

The Pivot table editor is organized around four types of fields. Use these controls to change what the pivot table shows:

Rows groups records vertically, such as listing each region down the left side.
Columns creates categories across the top, such as one column for each product type.
Values calculates a summary from a field, such as the sum of sales or a count of orders.
Filters limits the records included in the summary, for example to one year or one sales channel.

In the editor, use Add in the relevant section to choose an available source column. To remove a field, use its remove control in the editor. You can also change the field’s options. In Values, for example, select whether to summarize by a calculation such as SUM, COUNT, or AVERAGE when that calculation is appropriate for the data. A numeric amount is usually summed; a category such as product name is more often counted.

For a concrete example, suppose the source has Date, Region, Product, and Sales columns. Add Region to Rows, Product to Columns, and Sales to Values, summarized by SUM. The result compares total sales for each product across regions. To see just one region, add Region as a filter and choose the desired value. You can move or remove fields later without rebuilding the source data.

Change the source data range

A pivot table can only summarize records included in its source range. If the table has grown or moved, click inside the pivot table and look at the data range near the top of the Pivot table editor. Edit that range to cover the intended source cells, including the header row, and confirm the change. A range might look like Raw data!A1:D500; if a sheet name contains spaces, enclose the name in single quotation marks, as in 'Sales data'!A1:D500.

Check the range carefully before applying it. It should include all relevant columns and data rows but not unrelated notes or totals below the dataset. A source range that starts on the wrong row can treat a record as a header, while a range that omits a column makes that field unavailable in the editor. If the source is on another sheet, use that sheet’s name in the range.

When new records are regularly appended, consider setting a range that extends farther down the sheet than the current data. For example, a range ending at row 1,000 can accommodate later additions if the source has fewer than 1,000 rows. Keep the unused rows below the records free of unrelated content: formatting or stray values alone are not records, but data entered within the chosen range can be included in the pivot table.

Update the summary when source records change

Edits to cells already inside a pivot table’s source range are reflected in its summary as Google Sheets updates the pivot table. If you change a sales amount, correct a region name, or add a record within the defined range, the summary should reflect that source data. If it does not appear to change, verify that the edited cell is in the source range and that the pivot table is using the expected sheet and columns.

Adding records below the last row of a fixed range is a common reason new data is missing. For example, if the source range ends at row 200 and a new order is entered in row 201, the pivot table will not include it until the range is extended. Changing a column header can also affect the field list, so use clear, stable headers and check the editor after altering them.

There is an important distinction between updating the source and changing the pivot setup. Editing source values changes the records being summarized. Editing Rows, Columns, Values, or Filters changes how the existing records are summarized. Expanding the range does both only in the sense that it makes additional source records available; it does not automatically choose a new layout for them.

Sort, filter, and change the calculation

To control which records appear, add a field under Filters in the editor and choose the values to include or exclude. This is useful when the source contains several years but a report should show only the current one. A filter does not delete excluded source rows; it only limits the pivot table’s summary. For more targeted reporting, use a field that distinguishes the records clearly, such as status, region, or year.

For row or column fields, use the field’s options in the editor to adjust its sort order or how items are displayed. Sorting a pivot field changes the order in the summary, not the order of records in the source sheet. When a value field is wrong, check both the selected source column and the calculation. SUM is unsuitable for text categories, while COUNT can be useful for counting entries. An average of values can also be misleading if blank or atypical records have a particular meaning in the dataset.

When a pivot table contains dates or categories that appear separately, first inspect the source values for consistency. Dates stored as text may not behave like actual dates, and labels such as West, west, and West with a trailing space may be treated as different categories. Correcting the source data often produces a clearer summary than trying to compensate with extra pivot fields.

Prepare source data for reliable pivots

A well-structured source range makes pivot tables easier to edit and less likely to produce confusing results. Use one header row and one record per row. Each column should represent one kind of information, such as date, customer, quantity, or amount. Avoid merged cells, blank header names, repeated header rows inside the data, and subtotals mixed in with the records.

For example, a source table with a separate row for each order line is easier to summarize than a report where regions are section headings and totals are inserted between groups. If the source contains several unrelated tables side by side, select only the relevant one. Before diagnosing a pivot table, check that the source range has clear headings and that dates, numbers, and text are stored consistently in their columns.

Do not type new values into the pivot table’s generated output as though it were an ordinary report grid. The pivot table controls that area, and its results are generated from the source and editor settings. Make corrections in the source data or adjust the pivot configuration instead. If you need a presentation-specific version, copy the results to a separate area and treat that copy as a static report.

Common problems and practical fixes

The new rows are missing: Check whether they fall inside the pivot table’s data range, then extend the range if needed.
A field is missing from the editor: Confirm that its column is included in the source range and has a usable header.
The totals look too high: Look for duplicate source records, included subtotal rows, or a value field summarized with the wrong calculation.
Similar labels appear separately: Check for spelling differences, extra spaces, and inconsistent capitalization in the source column.
A date or number is categorized unexpectedly: Check whether the source cells are stored as real dates or numbers rather than text.

If a pivot table’s output is hard to understand, simplify it temporarily: remove extra fields, leave one row field and one value field, and confirm that the result matches a small sample of the source. Then add columns, filters, and other calculations one at a time. This makes it easier to identify which setting or source-data issue is responsible.

Google Sheets and Excel pivot tables

Google Sheets and Microsoft Excel both use pivot tables to summarize data, but their editors and menu paths are not identical. The directions here use Google Sheets in a web browser: the existing pivot table’s settings are changed in the Pivot table editor, and new tables are created through Insert > Pivot table. Excel has its own PivotTable tools and ribbon commands; instructions for one application should not be assumed to match the other exactly.

A pivot table in Google Sheets also depends on its source range in the spreadsheet. If you download or export a file for use in Excel, inspect the resulting workbook and pivot table there rather than assuming every setting or behavior will transfer unchanged. For work that will be edited by several people in Sheets, make the changes in the shared Google Sheets file so collaborators see the updated summary in that document.

Frequently Asked Questions

How do I open the Google Sheets pivot table editor?
Click a cell inside the pivot table. The Pivot table editor should appear on the right. If it is not open, click within the pivot table again; make sure you have selected the generated table rather than an unrelated cell.

Can I change a pivot table without changing the source data?
Yes. Change its Rows, Columns, Values, or Filters settings in the editor. These controls change the summary and do not rewrite the source records. Edit the source cells only when the underlying records themselves need correction.

Why does my pivot table not show newly added rows?
The new rows may be outside the table’s source range. Open the editor, check the range near the top, and extend it to include the added records and all required columns.

How do I change what a pivot table calculates?
In the Pivot table editor, open the relevant field under Values and choose a suitable summary calculation, such as SUM, COUNT, or AVERAGE. The available choices and sensible calculation depend on the type and quality of the source data.

Can I edit a Google Sheets pivot table in the mobile app?
The editor and steps described here are for Google Sheets in a web browser on a computer. Mobile app controls can differ and may not expose the same editing workflow, so use the browser interface when you need the full pivot table editor.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.