Pivot Table Refresh Errors and Missing Fields in Excel
Fix field name is not valid, reference is not valid, fields that vanish after refresh, old items that stay, lost formatting and new rows that never appear.
A pivot table works until the data changes. After the next export it shows last month's numbers, a field is gone, or Excel refuses with a message that sounds like it is the pivot's fault. Nearly always the cause sits in the source range. This post goes through the common messages and symptoms, in the order you are likely to meet them, with the fix for each. It is a spoke of pivot tables and their SQL equivalents.
"The PivotTable field name is not valid"
You see this when creating a pivot or refreshing it. It means the source range has a column with no header text. Excel needs a name for every column, so a blank header cell stops it.
Look at the first row of the source for an empty cell, often at the far right, or for a column that was inserted and never labeled. Hidden columns count too. Fill in the header, or delete the empty column. Duplicate headers are a related trap: Excel renames the second Amount to Amount2 in the field list, which can send a layout to the wrong column.
When the source comes from code, make sure the header row is written with a distinct non-empty name for every column (making Excel exports open cleanly). A DataFrame with an unnamed index column, for instance, writes a blank first header.
"Data source reference is not valid"
This message appears when you create a pivot from a named range or a file path that Excel cannot resolve. Typical causes:
The workbook was saved under a new name or location, and the pivot's source points to a path that no longer exists.
A named range was deleted or renamed.
The source sheet was deleted, renamed or hidden and the reference is
Sheet1!A1:F500for a sheet that is gone.The file name contains a square bracket, which breaks references.
Fix it from PivotTable Analyze, Change Data Source and enter the new range or Table name. For a pivot on a workbook you received, check whether the data sits on a sheet you were not given. An external source needs its connection restored.
"Reference isn't valid" on a refresh from another file
A pivot whose source is another workbook asks for that file on refresh. If the file moved, Excel cannot find it. Open the source workbook first, or switch the source to a connection that reads the path through Power Query so a moved file is a visible path to edit.
New rows never appear
A pivot based on A1:F500 summarizes those 500 rows and nothing more. A new export of 520 rows leaves 20 outside the range, with no warning, and the totals are short.
The fix is to make the source grow. Select the data and press Ctrl+T to make it a Table, then base the pivot on the Table name (Sales). A Table expands when rows are pasted under it, and the pivot follows on refresh. Or load the data with Power Query and build the pivot on the query. If the source must stay a range, use a dynamic named range, though Tables are simpler and less error-prone.
To find out whether you are affected, compare the pivot's Count with COUNTA of a full column, or with SELECT COUNT(*) at the source. A pivot short by exactly the rows pasted since it was built is the signature (building a pivot table from a database query).
A field vanished after refresh
A pivot remembers fields by name. If the source column is renamed from Revenue to Net Revenue, the old field disappears from the layout, leaving the Values area empty or short. The new name appears in the field list unchecked.
Drag the new field into place. To prevent it, keep the names stable: alias the column in SQL, rename in Power Query before the load, or document the export contract so the producer does not change headers casually (fixing Power Query errors on changed source columns).
A field shows as Count instead of Sum
A numeric column that contains text or blanks defaults to Count, with each text cell counted as one. A column of numbers stored as text gives a count of rows, and the values never add up. The cause is in the source: a few cells with n/a, a stray apostrophe, a trailing space. Clean the column, refresh, and change the field back to Sum (fixing numbers stored as text in Excel).
Old items stay in the dropdown
After a customer or product is removed from the data, it still shows in the filter dropdown and sometimes as a row with a zero. The pivot caches items it has seen. To clear it:
Right-click the pivot, PivotTable Options, Data tab.
Set Number of items to retain per field to None.
Refresh. Set it back to Automatic if you prefer.
The cache can also grow stale after many refreshes. If a pivot behaves oddly, copying the data to a fresh workbook and rebuilding is sometimes faster than debugging.
Formatting and widths reset
Column widths jump and number formats vanish after a refresh. In PivotTable Options, Layout & Format tab:
Untick Autofit column widths on update to keep your widths.
Tick Preserve cell formatting on update to keep cell formats.
For number formats, set them through Value Field Settings, Number Format, rather than formatting cells, because the field's format follows the field when the layout changes.
"A PivotTable report cannot overlap another"
When a refresh would grow the pivot into cells that hold another pivot or data, Excel stops with this message. Move the objects apart, or leave room: put a pivot at the left with nothing to its right and below it, or give each pivot its own sheet.
Grouped dates stop working
A refresh that brings in one text date breaks the grouping and shows each date as a separate row. Repair the column so every value is a real date and regroup (grouping dates by month or quarter in a pivot table).
"The PivotTable report was saved without the underlying data"
A pivot can be saved without its cache to shrink the file. Refreshing then needs the source to be reachable. If the source is gone, the pivot shows what it last held and cannot refresh. Recover by rebuilding the source or restoring the data. Check PivotTable Options, Data, "Save source data with file" for pivots you plan to share.
Checks after any fix
Refresh, then compare the pivot grand total with a known figure from the source. Test one filter and one date group. Save, close and reopen the workbook, and refresh again to confirm nothing depended on a state in memory.
When the fix is not in the pivot
The source is a moving target. If the producer changes the file every month, the real fix is a query that reshapes it into a fixed layout, or a database view with stable names.
The refresh is slow or times out. The query behind the pivot is the problem, not the pivot (slow Excel refresh from a database).
Many people edit the source. A shared workbook with edits in the source rows will keep producing blank headers and text numbers. Move the data to a controlled store and let people read it.
The file is corrupt. If Excel reports unreadable content or the pivot cache is damaged, see Excel found unreadable content.
Microsoft's page on changing the source data for a PivotTable covers the dialog. The Microsoft page on refreshing PivotTable data lists the refresh options. For a sample extract as JSON, use the CSV to JSON converter.
The M functions that reshape a source before it reaches a pivot are listed in Microsoft's table function reference, and the Power Query documentation covers the editor.
