What you can change in a pivot table after you create it

Once you build a pivot table, you are not locked into that layout. You can move fields between areas, swap which data appears in rows versus columns, filter out unwanted information, and change how the numbers calculate. Most edits happen in the same interface where you built the table — usually a sidebar or dialog box — rather than by editing the table itself like you would a normal spreadsheet.

The specific steps depend on whether you are using Excel, Google Sheets, or another program, but the underlying logic is the same: you drag fields around, toggle filters on and off, and adjust settings. This section covers the moves you will make most often.

Key Takeaways

  • Pivot table fields live in a sidebar or panel where you can drag them between rows, columns, values, and filters without touching the table itself.
  • Moving a field from rows to columns (or vice versa) when ready reorganizes how your data displays without recalculating anything.
  • Filters let you hide specific entries — like showing only one sales region or one product category — without deleting data.
  • You can change how numbers combine (sum, average, count, maximum) by right-clicking the value field or using the field settings menu.
  • Refreshing the pivot table pulls in new rows from your source data if you added information after creating the table.

Moving fields between rows, columns, and filters

The pivot table field panel shows all available fields from your data source. In Excel, this panel appears on the right side when you click inside the pivot table. In Google Sheets, it appears as a sidebar when you click Data and then Pivot table editor. Each field can sit in one of four zones: Rows, Columns, Values, or Filters.

To reorganize, drag a field from one zone to another. If you drag Region from Rows to Columns, your table flips — regions become column headers instead of row labels, and whatever was in columns moves to rows. This does not change your data; it only changes how the pivot table displays it. You can undo any move by pressing Ctrl+Z (or Cmd+Z on Mac) or by dragging the field back.

Removing a field is as straightforward as dragging it out of the panel entirely, or right-clicking it and choosing Remove. The field stays available in the list above; you have not deleted anything from your source data.

Filtering to show only certain rows or categories

Filters let you hide rows without deleting them. Drag a field into the Filters zone at the top of the field panel. A filter button appears above your pivot table. Click it to open a list of all unique values in that field — for example, if you filtered by Product, you would see a checkbox list of every product in your data.

Uncheck the items you want to hide. If you have 50 products but only want to see 5, uncheck the other 45. The pivot table recalculates when ready, showing only rows that match your filter. The hidden rows are still in your data; they are just not displayed. You can change the filter at any time by clicking the button again.

You can add multiple filters. If you filter by both Region and Product, the table shows only the combination of regions and products you selected. This is useful when you want to focus on a specific slice of your data without rebuilding the entire table.

Changing how numbers combine: sum, average, count, and more

By default, pivot tables add up (sum) the numbers in your Values field. If you want a different calculation — average, count, minimum, maximum, or others — right-click the value field name in the pivot table itself. In Excel, choose Summarize Values By and pick your option. In Google Sheets, click the field in the Values zone and select Summarize by.

You can also add the same field to Values multiple times with different calculations. Drag Sales into Values twice. Right-click the first copy and set it to Sum; right-click the second and set it to Average. Your pivot table now shows both the total and the average for each row. This is useful when you want to compare totals against typical values at a glance.

Some programs also offer advanced options like standard deviation or percentile. These appear in the same menu. Choose what makes sense for your question — if you are tracking inventory, Count might matter more than Sum; if you are analyzing test scores, Average tells a different story than Maximum.

Sorting pivot table rows and columns

Click any value in the pivot table and look for a sort button — usually an arrow or a menu option. In Excel, go to the Data tab and choose Sort A to Z or Sort Z to A. In Google Sheets, right-click a cell and select Sort range. You can sort by the row labels themselves (alphabetically) or by the numbers in a specific column.

Sorting is useful when you have many rows and want to see the largest or smallest values first. If your pivot table shows sales by region, sorting by the sales column (descending) puts your top region at the top. The sort order updates if you refresh the table with new data, so you do not have to re-sort manually each time.

Refreshing when your source data changes

If you add new rows to the spreadsheet that feeds your pivot table, the pivot table does not automatically include them. You have to refresh it. In Excel, right-click anywhere in the pivot table and choose Refresh, or press Ctrl+Alt+F5. In Google Sheets, click the refresh icon in the pivot table editor sidebar.

Refreshing pulls in any new data that falls within your original data range. If your original data was rows 1 through 100 and you add data in row 101, you may need to rebuild the pivot table to include row 101 — refreshing only updates rows already in the range. To avoid this, select a larger range when you first create the table, or rebuild the table after adding significant new data.

Changing the data source or expanding the range

If you need to include more rows or columns than your original pivot table covers, you can change the source data range. In Excel, right-click the pivot table, choose Pivot Table Options, and look for Data Source. In Google Sheets, click the pivot table editor and change the data range at the top.

You can also delete a pivot table and rebuild it from scratch with a new range. This is sometimes faster than hunting through settings. Select the entire pivot table, press Delete, and create a new one using the updated data range. Your original data is never affected — you are only removing the summary table.

Frequently Asked Questions

Can I edit the numbers in a pivot table directly?

No. Pivot tables are read-only summaries of your source data. If you change a number in the pivot table, it reverts when you refresh. To change data, edit the original spreadsheet, then refresh the pivot table. This design prevents accidental changes to your summary.

What if I want to undo all my changes and start over?

Delete the pivot table and create a new one. In Excel and Google Sheets, select the entire table and press Delete. Your source data remains untouched. Then build a fresh pivot table with the layout you want. This is often faster than trying to undo many individual moves.

Can I copy a pivot table to a different sheet?

Yes. Select the entire pivot table, copy it (Ctrl+C), and paste it into another sheet. The copy becomes a static table — it will not update when you refresh the original. If you want a live copy that updates together, create a second pivot table from the same source data instead.

How do I show subtotals for groups within my pivot table?

If you have nested row fields — for example, Region and then Product within each region — Excel and Google Sheets automatically show subtotals for each region. You cannot turn this off in Google Sheets, but in Excel you can right-click and choose Pivot Table Options to control whether subtotals appear.

What happens if I delete a row or column from my source data?

When you refresh the pivot table, that row or column disappears from the summary. If you deleted a product, it no longer appears in the pivot table. The pivot table always reflects what is currently in your source data.