Pivot table not working: 7 fixes

1. "The PivotTable field name is not valid"

Every column you include in the source needs a header. Here column F holds notes like "Rush order" but has nothing in F1. Select A1:F11 and insert a PivotTable, and Excel stops with this message. Merged header cells cause the same error, because only the first cell of a merged block holds the text and the rest are empty.

=COLUMNS(A1:F1)-COUNTA(A1:F1) counts empty header cells in the range. Type a header (Note) or leave the column out of the source. The same error appears when you refresh a PivotTable whose source now has a deleted header, so check the header row first whenever it shows up.

1. Type a header in every empty cell of row 1, or unmerge merged headers (Home > Merge & Center > Unmerge Cells) and fill each one.
2. Click a cell in the data and go to Insert > PivotTable again.
3. If the PivotTable already exists, go to PivotTable Analyze > Change Data Source and check the range.

2. New rows or changed numbers don't show up

A PivotTable works from a snapshot of the data called the pivot cache. Editing the source doesn't change the pivot until you refresh: right-click inside it and choose Refresh (Alt+F5), or Data > Refresh All (Ctrl+Alt+F5) for every pivot in the file.

Refresh alone won't pick up rows added below the original range, because the source is still A1:E11. Use PivotTable Analyze > Change Data Source and select the new range. The lasting fix is to click in the data, press Ctrl+T to make it a table, and point the pivot at the table: it then grows with every row you add, and Refresh is all you need.

To stop forgetting, right-click the pivot, choose PivotTable Options > Data and tick Refresh data when opening the file.

3. It shows Count of Amount instead of Sum

Excel picks Sum for a value field when every cell in the column is a number. One blank or one text cell and it picks Count. This list has both: the South Coffee beans amount came in as the text "180", and the South Mugs amount is empty until the price is confirmed.

Switch the field back with right-click > Summarize Values By > Sum. Then fix the column, because the next pivot you build from it will pick Count again.

4. The total is quietly too low

This one does the damage, because nothing looks wrong. After you switch to Sum, a number stored as text counts as zero. The pivot shows South as 144, but South's two priced orders add up to 324: the "180" just isn't counted.

Check any pivot against a formula. SUMIFS skips text the same way, so it matches the pivot's 144. Multiplying the column forces "180" to a number, which shows what the total should be. If the two disagree, convert the column (select it, Data > Text to Columns > Finish), then Refresh.

5. "Cannot group that selection" on dates

Grouping by month, quarter or year needs every cell in the date field to be a real date. One text date or one blank cell in the column, and Group is refused or the dates stay as separate days. Here the East Mugs order has 14/10/2026 typed as text from a different regional format.

Find text dates with the formula below, convert them (the convert text to date guide covers the usual patterns), fill or remove blanks, then Refresh and group again. If you can't fill a blank, filter it out of the source with a table, because a blank date can't be placed in any month.

6. Error messages when you refresh

"A PivotTable report cannot overlap another PivotTable report" means the pivot would grow into the space of another one on the same sheet after the refresh. Move one of them (PivotTable Analyze > Move PivotTable), or put each pivot on its own sheet so they have room to grow.

"Data source reference is not valid" means the source range or table no longer exists: it was deleted, renamed, or the source workbook moved. Point Change Data Source at the current range.

"PivotTable field name already exists" appears when you rename "Sum of Amount" to plain "Amount", because a source column already has that name. Type "Amount " with a space at the end, or "Total amount".

7. Old items, odd grouping and greyed-out buttons

Deleted products still showing in a filter drop-down? The cache keeps old items. Go to PivotTable Options > Data, set Number of items to retain per field to None, and Refresh.

Grouped dates by month in one pivot and another pivot changed too? Pivots built from the same source share one cache, and grouping is stored in the cache. If you need different grouping, add a Month column to the data with =TEXT(A2,"yyyy-mm") or build the second pivot from a separate range.

Refresh or Field List greyed out? The sheet may be protected, the click may be outside the pivot, or the field list was closed: right-click inside the pivot and choose Show Field List.