"PivotTable Field Name Is Not Valid": How to Fix It

"The PivotTable field name is not valid" means at least one column in your source data has no header. A PivotTable turns every column header into a field, the name you drag into Rows, Columns or Values, so a column without a header has no name and Excel stops. This guide shows the usual causes of the pivot table field name is not valid error and how to fix each one quickly, whether it appears when you create the pivot or when you refresh it.

Why Excel says the PivotTable field name is not valid#

Excel needs your data organised as a list: one header row on top, one record per row below it, and a label above every column. When any cell in the header row of the selected range is blank, Excel can't name that field, so it shows the message and doesn't create the PivotTable.

Look at this sales data. The header in D1 is missing:

Select A1:E5, choose Insert > PivotTable, and Excel shows the error. Type Channel into D1 and the PivotTable is created without a problem.

The common causes, in order of how often they happen:

1. A blank header cell somewhere in the first row.
2. Merged header cells.
3. A source range that includes an empty column or starts on the wrong row.
4. A header that was deleted after the PivotTable was built, which shows up on refresh.

Fix 1: fill in blank header cells#

The fastest fix is to find the empty header and type a name. In a wide sheet, a blank header is easy to miss, so let a formula find it for you:

=COUNTBLANK(A1:E1)

On the data above, the result is 1. Anything above 0 means a header is missing. Select the header row and scan it, or use Home > Find & Select > Go To Special > Blanks to select the empty cells directly.

Watch out for headers that look blank but aren't, and the other way round. A header that holds only a space looks empty but counts as text. A header that holds a formula returning "" also looks empty. Give every column a short, clear name such as Channel, Amount or Order Date.

Fix 2: unmerge merged header cells#

Merged cells cause the same error because only the top-left cell of a merge keeps its text. If "Sales" is merged across D1:E1, Excel sees "Sales" in D1 and a blank in E1.

1. Select the header row.
2. Choose Home > Merge & Center to unmerge the cells.
3. Type a separate header into every cell, for example "Sales Online" and "Sales Store".

Two header rows, such as a group title above the column names, cause a similar problem. Keep only one header row directly above the data. If you need the group title for printing, put it higher up with an empty row between it and the data, and don't include it in the PivotTable range.

Fix 3: check the source data range#

Sometimes the headers are fine but the range is wrong. If the selected range includes an empty column on the right, or starts one row too low, Excel finds a blank "header" and stops.

For a new PivotTable, check the Table/Range box in the Create PivotTable dialog before you click OK. Selecting a single cell inside the data and letting Excel pick the range is usually safest, as long as the data has no fully empty rows or columns in the middle.

For an existing PivotTable:

1. Click inside the PivotTable.
2. Go to PivotTable Analyze > Change Data Source.
3. Look at the range. Make sure it starts at the header row and ends at the last real column.
4. Fix the range and click OK.

Fix 4: the error appears when you refresh#

If the PivotTable worked yesterday and now fails on refresh, something in the source changed. Usually someone deleted or cleared a header, inserted a new column without a name, or pasted new data over the header row. Fill in the header and refresh again (Windows: Alt+F5; or right-click the PivotTable and choose Refresh).

The guide to refreshing a pivot table explains Refresh versus Refresh All, and why new rows sometimes don't appear.

How to stop the error coming back#

The best long-term fix is to turn the source data into an Excel table before you build the PivotTable. Click inside the data and press Ctrl+T (Mac: ⌘T).

An Excel table always gives every column a header. If you add a column, it gets a default name like Column1 instead of a blank. The table also grows when you add rows, and a PivotTable built on it picks up the new rows on the next refresh. The article on tables versus PivotTables explains how the two work together.

A few more habits help:

• One header row, one record per row, no blank rows or columns in the middle.
• Unique header names. Excel allows two columns called "Amount" and renames the second one Amount2, but clear names make the field list much easier to use.
• No headers that are only numbers or dates if you can avoid it. They work, but text labels are easier to drag and read.

Related PivotTable messages#

A similar-looking message appears when you rename a field inside the PivotTable to a name the source already uses, for example renaming "Sum of Amount" to "Amount". Excel says the name already exists. Add a space at the end (Amount ) or use a different name such as "Total Amount".

If you're new to PivotTables, start with the beginner's guide to pivot tables. Once your pivot works, you can count rows with a pivot table or add a calculated field for things like profit or margin.

Practise it#

Practise building PivotTables in a live spreadsheet: you'll create a PivotTable from clean data, drag fields into Rows and Values, and change Sum to Count, with instant feedback at each step. Warm up with the header check exercise on this page.