Fixes / Excel · Checked

Excel "PivotTable field name is not valid": causes and fixes

Short answer

Excel shows this error when at least one column in the PivotTable's source range has no header. The header cell may be blank, merged, hidden, or an empty column may be included in the range. To fix it, check the first row of your source data, give every column a unique non-blank label, then create or refresh the PivotTable again.

What this message means

A PivotTable turns each column of your source data into a field and uses the column's header cell as the field's name. If Excel finds a column in the selected range with no usable header, it cannot name that field, so it stops and shows this message. The error can appear when you first insert a PivotTable, when you refresh one after the source data has changed, when you use Change Data Source, or when you rename a field inside the PivotTable and leave the name empty. It can also appear when a VBA macro builds a PivotTable. Your data is not damaged. Excel is only refusing the range it was given.

Common causes

CauseHow to tell
A header cell in the source range is blankOne cell in the top row of your data is empty. It is easy to miss when text from the header next to it spills across and makes the cell look filled. Click each header cell and look at the formula bar. A truly empty cell shows nothing there.
The range includes an empty column, or extends past the dataIn the Create PivotTable dialog (or Change Data Source), the Table/Range box covers more columns than your data. Examples are Sheet1!$A:$Z when your data ends at column F, or a whole-sheet selection. It can also include a blank separator column in the middle of the data.
Merged cells in the header rowOne header stretches across two or more columns. When you click it, the Merge & Center button on the Home tab is highlighted. Only the first cell of a merged group holds text, so every other column in the group has no header.
A hidden column with no headerThe column letters skip (for example C jumps to E), or a double line shows between two column headings. The hidden column is still inside the range, and its header cell is empty.
A header was deleted or the source data was moved after the PivotTable was builtThe PivotTable used to work and the error only appears when you click Refresh. Someone recently cleared a header, inserted a column or pasted new data over the source sheet.
A VBA macro creates the PivotTable while the source sheet is not active (Microsoft 365 Version 2504 and later)You see Run-time error 1004 with this message when running a macro. The same macro ran fine on an earlier Excel build, and the error points at the PivotCaches.Create or CreatePivotTable line.

How to fix it

1. Give every column a unique, non-blank header

  1. Go to the sheet that holds the source data.
  2. Click each cell in the header row one at a time and check the formula bar. Any cell that shows nothing there is blank, even if neighbouring text spills over it.
  3. Type a short, unique label into every blank header cell (for example Notes, Column7).
  4. If two columns have the same label, change one so each header is different.
  5. Go back to the PivotTable, right-click inside it and choose Refresh, or insert the PivotTable again.

2. Shrink the source range to just your data

  1. Click any cell in the PivotTable.
  2. On the ribbon, open PivotTable Analyze and click Change Data Source (if you are creating a new PivotTable, look at the Table/Range box in the Create PivotTable dialog instead).
  3. Check the range. If it uses whole columns (like $A:$Z) or reaches past the last column that has data, change it to the exact block, for example Sheet1!$A$1:$F$500.
  4. If there is an empty column in the middle of the data, delete it (right-click the column letter, then Delete) or give it a header.
  5. Click OK, then refresh the PivotTable.

3. Unmerge header cells

  1. Select the whole header row of your source data.
  2. On the Home tab, click the arrow next to Merge & Center and choose Unmerge Cells.
  3. Each formerly merged group now has text only in its first cell. Type a header into each cell that is now empty.
  4. If you only merged for looks, select the cells and use Format Cells (Ctrl+1 on Windows, Cmd+1 on Mac) > Alignment > Horizontal > Center Across Selection instead. It looks the same but keeps the cells separate.
  5. Refresh or recreate the PivotTable.

4. Unhide hidden columns and check their headers

  1. Click the box above row 1 and to the left of column A to select the whole sheet.
  2. Right-click any column letter and choose Unhide.
  3. Look at the header of each column that appears and add a label if it is blank.
  4. Hide the columns again if you want to. Hidden columns are fine as long as they have headers.
  5. Refresh the PivotTable.

5. Convert the source data to an Excel Table to prevent it happening again

  1. Click any cell in your source data.
  2. Press Ctrl+T (on a Mac, Cmd+T also works), or go to Insert > Table.
  3. Tick My table has headers and click OK. Excel fills any blank header with a name such as Column1, which you can rename.
  4. Point the PivotTable at the table: PivotTable Analyze > Change Data Source, then type the table name (for example Table1) and click OK.
  5. Rows you add to the table are now picked up automatically when you refresh.

6. Fix a macro that fails with Run-time error 1004

Excel for Microsoft 365 on Windows, Version 2504 and later, when creating PivotTables with VBA

  1. Open the macro in the VBA editor (Alt+F11).
  2. Just before the PivotCaches.Create line, add a line that activates the source sheet, for example Worksheets("Open Tickets").Activate.
  3. If the sheet name contains spaces, wrap it in single quotes in the SourceData string, for example "'Open Tickets'!R1C1:R500C10".
  4. Make sure the SourceData range starts at the header row and does not include empty columns.
  5. Run the macro again.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

If every header is filled and the range is correct but the error keeps appearing, copy the source data and use Paste Special > Values into a new, blank workbook, then build the PivotTable there. This removes hidden formatting, stray merged cells and broken names. Also check Formulas > Name Manager for named ranges that point to deleted sheets (they show #REF!) and delete them, because a PivotTable using that name as its source will fail. Also make sure the file name does not contain square brackets [ ], which Excel does not allow in a source reference. If the workbook is shared or managed by your company, ask the file owner or IT support to check whether the source sheet is protected or the data comes from an external connection that has changed.

Questions people ask

Why do I get this error when I refresh a PivotTable that used to work?

The source data changed after the PivotTable was built. Usually a header cell was cleared, a new column without a header was inserted, or data was pasted over the range. Put a header back in the empty cell, or use Change Data Source to point at the correct range, then refresh.

My headers all look filled in. Why does Excel still say the field name is not valid?

Text from a long header can spill into the empty cell next to it and make that cell look filled. Click each header cell and check the formula bar. Also check for hidden columns and for a range that runs past the last column of data.

Can I use merged cells in the header row of a PivotTable source?

No. Only the first cell of a merged group holds the text, so the other columns have no header. Unmerge them and give each column its own label. Use Center Across Selection if you want the same look.

Can I select whole columns like A:Z as the PivotTable source?

Only if every column in that span has a header. Any empty column inside the range triggers this error. An Excel Table is the better option because it grows with your data and never includes empty columns.

Why does my VBA macro now fail with Run-time error 1004 on this message?

Starting with Microsoft 365 Version 2504, creating a PivotTable from a visible source sheet that is not the active sheet can fail with this message. Activating the source sheet before PivotCaches.Create fixes it.

Sources

Related errors