Summary

A PivotTable does not read your data directly — it reads a cached snapshot taken when the report was built or last refreshed. That one fact explains almost every PivotTable complaint. This article covers refreshing, getting new rows to appear, clearing out values that no longer exist, repairing a broken source, and stopping formatting from resetting every time the report reloads.

Symptoms

• You edited the source data, but the PivotTable still shows the old totals.
• You added rows to the bottom of the source sheet, clicked Refresh, and the new rows are nowhere in the report.
• Filter dropdowns list customers, products or staff names that were deleted from the source weeks ago.
• The PivotTable cells show #REF!, or refreshing raises Data source reference is not valid.
• Right-clicking a date and choosing Group returns Cannot group that selection.
• Column widths snap back and currency formatting disappears every time you refresh.
• Inserting the report gives The PivotTable field name is not valid.

In this guide

• 1. Refresh the PivotTable
• 2. Make New Rows Appear by Fixing the Source Range
• 3. Refresh Automatically When the File Opens
• 4. Clear Deleted Names Out of the Filter Dropdowns
• 5. Repair a Broken Data Source
• 6. Fix Grouping Errors on a Date Column
• 7. Stop Formatting Resetting on Every Refresh

Before You Start

• Excel with an existing PivotTable. If you are building one for the first time, start with How to Create a PivotTable in Excel.
• Edit permission on the workbook, and access to the sheet or file holding the source data.
• No admin rights required.

Note: The ribbon tab is named PivotTable Analyze in Microsoft 365 and Excel 2021, and Analyze in Excel 2016 and 2019. In Excel for the web it is PivotTable. Every instruction below works on all three — the tab is in the same place, at the right-hand end of the ribbon, and it only appears while a cell inside the PivotTable is selected.

Instructions

1. Refresh the PivotTable

Start here, because an out-of-date report is far more common than a broken one.

1. Click any cell inside the PivotTable. The PivotTable Analyze and Design tabs appear on the ribbon.
2. On the PivotTable Analyze tab, in the Data group, click Refresh.
3. The totals redraw. On a large source you see the status bar at the bottom-left of the window read Reading data... for a moment.

Two faster routes to the same place:

• Right-click any cell in the PivotTable and choose Refresh from the shortcut menu.
• Press Alt + F5.

Refresh updates the selected PivotTable only. To update everything in the workbook at once, go to the Data tab and click Refresh All, or press Ctrl + Alt + F5. That covers every PivotTable, every Power Query, and every external connection in the file — worth using when a workbook holds four reports built from one source sheet and you need them telling the same story.

Note: A PivotTable never updates on its own, no matter how the source changes. When you create one, Excel copies the source rows into a hidden store called the pivot cache and the report reads from that copy. Editing the source sheet does not touch the cache. This is deliberate — it is what lets a PivotTable re-sort a million rows instantly — but it means Refresh is a step you own.

If the totals redrew and the report is still wrong, the symptom names the section to go to: new rows missing, Step 2; deleted names still listed in the filter dropdowns, Step 4; #REF! or Data source reference is not valid, Step 5; Cannot group that selection on a date field, Step 6; number formats and column widths resetting, Step 7.

2. Make New Rows Appear by Fixing the Source Range

If Refresh ran cleanly and the new rows are still missing, the report is refreshing a range that stops above them.

Check what the PivotTable is actually reading:

1. Click any cell in the PivotTable.
2. Go to PivotTable Analyze > Change Data Source. The Change PivotTable Data Source dialog opens and Excel jumps to the source sheet with a marching border around the range it is using.
3. Read the Table/Range box. A fixed reference such as Sales!$A$1:$E$500 explains everything — row 501 onwards does not exist as far as the report is concerned.

The permanent fix is to convert the source into an Excel Table, which grows on its own as rows are added.

1. Go to the source sheet and click any cell inside the data.
2. Press Ctrl + T. The Create Table dialog opens with the range already selected.
3. Confirm the range covers all your data and that My table has headers is checked. Click OK. The data gains banded row shading and a Table Design tab appears on the ribbon.
4. On the Table Design tab, read the Table Name box at the far left. Give it something meaningful — type SalesData and press Enter.
5. Click back into the PivotTable and go to PivotTable Analyze > Change Data Source.
6. Clear the Table/Range box and type the table name: SalesData.
7. Click OK, then click Refresh.

From this point on, any row typed directly beneath the last row of the table is absorbed into it, and the PivotTable picks it up on the next refresh.

Verification: Type a test row at the bottom of the source table. The table's banded shading extends to cover it as soon as you press Enter — that is the visible confirmation the range grew. Refresh the PivotTable and the test value appears in the report. Delete the test row afterwards and refresh again.

If you cannot convert the source to a table — a shared file that other formulas depend on, for instance — widen the range by hand instead:

1. Click the PivotTable and go to PivotTable Analyze > Change Data Source.
2. In the Table/Range box, edit the last row number upward, for example from $E$500 to $E$5000.
3. Click OK and refresh.

That works, but it has to be repeated every time the data outgrows the range, and blank rows inside a padded range show up as a (blank) row in the report.

Warning: Pasting rows below a table with an empty row in between does not extend the table — Excel treats the gap as the end of the data. Paste into the first empty row directly under the last row of the table, with nothing skipped.

3. Refresh Automatically When the File Opens

Turn this on for any report that other people open, so nobody reads yesterday's numbers.

1. Right-click any cell in the PivotTable and choose PivotTable Options. The PivotTable Options dialog opens.
2. Click the Data tab.
3. Under PivotTable Data, check Refresh data when opening the file.
4. Click OK.
5. Save the workbook. The next time anyone opens it, the report rebuilds from the current source before it is shown.

Two limits worth knowing:

• The setting applies to one PivotTable, not the whole workbook. A file with three reports needs the box checked three times.
• It fires at open only. If a colleague edits the source while your copy stays open, you still refresh manually.

Note: PivotTable Options is a desktop feature. In Excel for the web you refresh from the PivotTable tab each time; the automatic setting carries over if it was switched on in the desktop app, so set it there once and the web copy honours it.

4. Clear Deleted Names Out of the Filter Dropdowns

A customer left, their rows came out of the source, you refreshed — and their name is still sitting in the filter list.

The pivot cache keeps a record of every value a field has ever held, so old entries linger in dropdowns and slicers with no data behind them. Change what the cache retains:

1. Right-click any cell in the PivotTable and choose PivotTable Options.
2. Click the Data tab.
3. Under Retain items deleted from the data source, open the Number of items to retain per field dropdown. It reads Automatic by default.
4. Select None.
5. Click OK.
6. Click Refresh. The dropdown now lists only values that exist in the source right now.

If the stale names survive that refresh, the cache needs a full rebuild: save the workbook, close it, reopen it, and refresh once more.

Tip: Do this on every report that feeds a slicer. A slicer button for a discontinued product is the kind of detail that gets a report queried in a meeting, and the fix takes fifteen seconds.

5. Repair a Broken Data Source

#REF! across the report, or Data source reference is not valid on refresh, means the PivotTable is pointing at something that has moved, been renamed, or been deleted.

1. Click any cell in the PivotTable.
2. Go to PivotTable Analyze > Change Data Source. The Table/Range box shows the reference exactly as the report holds it, including the old sheet name or the full path to another workbook.
3. Fix the reference:
   • Sheet renamed — retype the reference with the current sheet name, or clear the box and drag across the source data with the mouse.
   • Source moved to a new file — type the full path in the form 'C:\Reports\[Sales2026.xlsx]Data'!$A$1:$E$900, or open both workbooks and select the range with the mouse.
   • Named range deleted — go to Formulas > Name Manager, recreate the name over the correct cells, then type that name into Table/Range.
4. Click OK, then click Refresh.

If Change Data Source is greyed out, the PivotTable is built on the workbook Data Model rather than a plain range. Go to Data > Queries & Connections, right-click the connection behind the report, and choose Properties to repoint it there.

Tip: The PivotTable field name is not valid on creation is a different problem with a one-line fix: a column in the source has no header. Put a word in every cell of row 1 across the full width of the data and try again.

6. Fix Grouping Errors on a Date Column

Cannot group that selection almost always means the column you are grouping is not entirely dates.

One blank cell, one date typed as text, or one stray TBD is enough — Excel classifies the whole field as text and grouping by month or year becomes unavailable.

1. Go to the source sheet and click the column letter above your date column to select it.
2. Look at how the values sit in their cells. Real dates align to the right by default; text aligns to the left. A column with a mix of both alignments has found your problem for you.
3. Turn on filtering if it is off — select the header row and press Ctrl + Shift + L — then open the filter dropdown on the date column and look for (Blanks) at the bottom of the list. Fill or delete those rows.
4. Convert the text entries to real dates. Select the column, go to Data > Text to Columns, click Next, click Next again, select Date under Column data format, choose the order that matches your data from the dropdown beside it (DMY or MDY), and click Finish. The converted values jump to the right-hand edge of their cells.
5. Click the PivotTable and click Refresh.
6. Right-click any date in the PivotTable and choose Group. The Grouping dialog opens. Select Months and Years, then click OK.

The same rule governs numeric grouping: a blank or a text entry anywhere in a number column blocks grouping into ranges.

Note: In Microsoft 365, dropping a genuine date field into Rows auto-groups it into Years, Quarters and Months without being asked. When that does not happen, treat it as the signal that the column is not being read as dates and go through the steps above before troubleshooting anything else.

7. Stop Formatting Resetting on Every Refresh

Currency formats and column widths that vanish on refresh are two separate settings, both in the same dialog.

1. Right-click any cell in the PivotTable and choose PivotTable Options.
2. On the Layout & Format tab, under Format, check Preserve cell formatting on update.
3. Clear the Autofit column widths on update checkbox directly above it. This is what snaps your carefully sized columns back on every refresh.
4. Click OK.

Number formatting needs one more move, because a format applied from the Home tab belongs to the cells rather than to the field, and the cells are rebuilt on refresh. Attach the format to the field instead:

1. Right-click any value inside the Values area of the report and choose Number Format. The Format Cells dialog opens with only the Number tab.
2. Select Currency, set Decimal places to 2, and choose your symbol.
3. Click OK.

The format now travels with the field and survives every refresh, every filter change, and every rearrangement of the layout.

Verification: Refresh the report twice in a row. Currency symbols stay put and column widths hold. If widths still move, Autofit column widths on update was left checked in step 3.

Troubleshooting

WARNING

If the source data has been deleted, do not refresh. The PivotTable still holds every source row in its cache, and refreshing against a source that is gone wipes it. Recover the rows first: double-click the Grand Total cell at the bottom-right of the report and Excel writes the entire cached dataset onto a new sheet. Save that sheet, then rebuild the report from it.

Symptom / Error	Potential Cause	Solution
Totals unchanged after editing the source	PivotTable not refreshed	Click inside the report and press Alt + F5, or use PivotTable Analyze > Refresh (Step 1).
New rows missing after a clean refresh	Source range stops above the new rows	Convert the source to an Excel Table with Ctrl + T and repoint the report with Change Data Source (Step 2).
#REF! throughout the report	Source sheet renamed, moved or deleted	Repoint the reference in Change Data Source (Step 5). Recover cached rows first by double-clicking the Grand Total cell.
Deleted names still listed in filters and slicers	Pivot cache retaining old items	Set Number of items to retain per field to None, then refresh (Step 4).
Cannot group that selection	Blanks or text in the date column	Clear the blanks, convert text dates with Text to Columns, refresh, then group (Step 6).
Currency formats and column widths reset on refresh	Formatting applied to cells, not the field	Check Preserve cell formatting on update, clear Autofit column widths on update, and set the format via Number Format on the value field (Step 7).
Refresh is greyed out	Cell in edit mode, or the sheet is protected	Press Esc to leave edit mode. If the sheet is protected, unprotect it — see How to Protect a Sheet or Workbook in Excel.

Related Articles

• How to Create a PivotTable in Excel
• How to Sort and Filter Data in Excel
• How to Fix Common Excel Formula Errors
• How to Stop Excel Changing Numbers into Dates
• How to Create and Customize Charts in Excel
• How to Use the Analyze Data Feature in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?