Modifying a pivot table means changing what data it shows, how it's organized, or what calculations it displays

Once you've built a pivot table in Excel, you can rearrange it without rebuilding from scratch. You can move fields between areas (rows, columns, values), filter to show only certain data, change how numbers are calculated, refresh it when your source data changes, and rename fields. The pivot table stays linked to your original data, so modifications happen fast.

The main tool for all of this is the PivotTable Fields panel, which appears on the right side of your screen when you click anywhere inside the pivot table. If it doesn't appear, right-click the pivot table and select "Show Field List". From there, you can drag fields around, uncheck items to hide them, and access options to change how the table behaves.

Key Takeaways

  • Click inside your pivot table to open the PivotTable Fields panel on the right, where you can drag fields between rows, columns, and values areas.
  • Uncheck a field name in the panel to hide that entire field from the table, or click the dropdown arrow next to a field to filter specific items.
  • Right-click any value in the pivot table and select "Summarize Values By" to change from sum to average, count, or other calculations.
  • Press Ctrl+A to select the entire pivot table, then go to the Design tab and click "Refresh" to update it when your source data changes.
  • Double-click the border between column headers in the pivot table to auto-fit column widths so all data is visible.

Moving fields between rows, columns, and values

The PivotTable Fields panel divides into four drop zones at the bottom: Filters, Columns, Rows, and Values. Each zone controls where that field appears in your table. To move a field, drag it from the field list at the top into a different zone, or drag it from one zone to another.

For example, if "Region" is in Rows and you want it in Columns instead, find "Region" in the Rows zone and drag it up to the Columns zone. The table reorganizes when ready. You can also remove a field entirely by dragging it out of any zone, or by unchecking its checkbox in the field list at the top of the panel.

The Values zone is where your numbers live — typically sums, counts, or averages. If you drag a field into Values twice, Excel creates two separate calculations of that field, which is useful when you want to see both the total and the count of the same data.

Filtering to show only certain rows or items

Every field in your pivot table has a dropdown arrow next to its name. Click that arrow to see a list of all unique values in that field. Uncheck any item you want to hide from the table. For example, if your pivot table shows sales by region, click the dropdown next to "Region" and uncheck "West" to hide all West region rows.

You can also filter at the top level using the Filters zone in the PivotTable Fields panel. Drag a field into the Filters zone, and it creates a dropdown button above the pivot table itself. This is useful when you want to switch between different filtered views without opening the field list each time — for instance, filtering by year or by product category.

To clear a filter and show all items again, click the dropdown arrow and select "Select All", or right-click the pivot table and choose "Clear Filter".

Changing how numbers are calculated

By default, Excel sums the values in your Values zone. If you want a different calculation — average, count, minimum, maximum, or percentage of total — right-click any number in the Values area of the pivot table and select "Summarize Values By". A menu appears with calculation options. Click the one you want.

You can also access this through the PivotTable Fields panel: right-click the field name in the Values zone and select "Value Field Settings". This opens a dialog where you can choose the calculation type and also set number formatting, like decimal places or currency symbols.

If you need multiple calculations of the same field — for instance, both the sum and the average — drag that field into the Values zone twice, then set each copy to a different calculation type. Excel labels them automatically as "Sum of Sales" and "Average of Sales".

Refreshing when your source data changes

A pivot table is a snapshot. When you add new rows to your original data or change existing numbers, the pivot table doesn't update automatically. To refresh it, click anywhere inside the pivot table, go to the Data tab at the top, and click "Refresh". Or press Ctrl+Alt+F5.

If you've added entirely new rows to your source data and want the pivot table to include them, you need to expand the data range. Right-click the pivot table, select "PivotTable Options", go to the Data tab, and change the "Data source" range to include the new rows. Then refresh.

To make refreshing automatic, right-click the pivot table, select "PivotTable Options", and check "Refresh data when opening the file". The table will update every time you open the workbook.

Renaming fields and changing field labels

To rename a field, right-click its name in the PivotTable Fields panel and select "Rename". Type the new name and press Enter. This changes the label in the pivot table but doesn't affect the original column name in your source data.

You can also rename individual items within a field. Right-click the item name directly in the pivot table (for example, "East" in the Region column) and select "Rename". This is useful when you want to clean up inconsistent spelling or abbreviations without editing the source data.

Adjusting column widths and formatting

Pivot tables often have narrow columns that cut off text. To auto-fit a single column, position your cursor on the border between two column headers until it becomes a resize arrow, then double-click. To auto-fit all columns at once, click anywhere in the pivot table, go to the Design tab, and look for a "Format" or "AutoFit" option in the ribbon.

You can also manually drag column borders to widen them. Right-click any cell in the pivot table and select "Format Cells" to change font, color, number format, or alignment for that cell or range. Changes to formatting persist when you refresh the table.

Frequently Asked Questions

Can I undo changes to a pivot table?

Yes. Press Ctrl+Z to undo your last change, just as you would in any Excel sheet. You can undo multiple steps by pressing Ctrl+Z repeatedly. However, if you've already saved and closed the file, you cannot undo changes from a previous session.

What if the PivotTable Fields panel won't open?

Click anywhere inside the pivot table to set up it, then right-click and select "Show Field List". If that option doesn't appear, you may have clicked outside the pivot table. Make sure your cursor is on a cell that's part of the table itself, not on an empty cell nearby.

How do I add a new field to a pivot table after I've created it?

If the new field is in your source data, open the PivotTable Fields panel, and the new field appears in the list at the top. Drag it into the Rows, Columns, or Values zone. If the field doesn't appear, refresh the pivot table or expand the data source range as described in the refresh section.

Can I sort a pivot table by a specific column?

Yes. Click any cell in the column you want to sort by, then go to the Data tab and click "Sort A to Z" or "Sort Z to A". You can also right-click a cell in that column and select "Sort" from the menu. The entire pivot table reorganizes based on that column's values.

What happens if I delete a row or column from the pivot table?

You cannot delete individual rows or columns from a pivot table the way you can from a regular sheet. Instead, use the filter dropdown next to the field name to hide unwanted items, or drag the field out of the PivotTable Fields panel to remove it entirely.