Sometimes you may face the error box saying that “The Pivot Table field name is not valid” when you are creating a Pivot Table. In this article, I’ll show you 7 cases when the error- The Pivot Table field name is not valid occurs. I’ll also show you how you can solve this problem and create a Pivot Table.
Let’s say you have the following dataset. Now, using this dataset I’ll show you when the error occurs and how to solve it.
1. Missing Data in Any Cell of the Header Row
Suppose, one of the cells of the header row in your dataset doesn’t have any data. You want to create a Pivot Table with this dataset.
To do that,
➤ Go to the Insert tab and select Pivot Table from the Tables ribbon.
It will open the PivotTable from table or range window.
➤ Select Existing Worksheet and insert a cell reference in the Location box.
If you want to create the Pivot Table in a new sheet, you can select New Worksheet.
After clicking OK, you will see a Microsoft Excel error message box will appear stating the message,
“The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.”
➤ Press OK on this box.
Now, to solve the problem you have to find which cell in the header row is empty.
In our dataset, cell D4 is empty.
To solve “The Pivot Table field name is not valid” error,
➤ Type the text which you want to give as the column header in cell D4.
Now, if you click OK in the PivotTable from table or range window, the “The Pivot Table field name is not valid” – error won’t appear at this time.
After clicking OK in the PivotTable from table or range window you will see a Pivot Table will be created in your selected location.
2. Facing Pivot Table Field Name Is Not Valid Error Due to Deleted Header Column
If one or more of your column headers is deleted after creating the Pivot Table and you try to refresh the Pivot Table, the “The Pivot Table field name is not valid” error box will be popped up.
Suppose we have already created a Pivot Table and one of the column headers got deleted.
Now, if you refresh the Pivot Table you will get the “The Pivot Table field name is not valid” error message.
To refresh the Pivot Table,
➤ Select any cell on your Pivot Table and right-click on it.
As a result, a dropdown menu will appear.
➤ Click on Refresh from this menu.
As I’ve deleted a column header, the “The Pivot Table field name is not valid” error message box will appear.
To solve this problem,
➤ Type the text which got deleted from the column header in cell D4.
Now, you can refresh the Pivot Table. This time the error message won’t be shown.
3. Selecting Entire Sheet Triggering the Pivot Table Name is Not Valid
If you try to create a Pivot Table by selecting the entire sheet, another Pivot Table error will happen.
Suppose, you have selected the entire sheet to create the Pivot Table.
To create the Pivot Table,
➤ Go to Insert and select PivotTable.
It will open the PivotTable from table or range box.
This time we will try to create the Pivot Table in a new sheet. So,
➤ Select New Worksheet.
If you observe you can see this time the Select a table or range box is empty. This is because you have selected the entire sheet.
➤ Click on OK.
As a result, a Microsoft Excel error box stating that “Data source reference is not valid” will appear.
To solve this,
➤ Select only the data cells of your worksheet, not the entire worksheet.
Now, if you open the PivotTable from table or range window, you will see that the Select a table or range box is not empty anymore. It is showing the cell reference of your data cells.
➤ Click OK.
This time no error box will appear and a sheet will be added containing the Pivot Table.
4. Having The Pivot Table Name Is Not Valid Error for Deleted Dataset
Suppose, we have a Pivot Table in a worksheet and the dataset of the Pivot Table in another sheet. Now, we are trying to refresh the pivot table by right-clicking on a cell of the Pivot Table.
All of a sudden, the “The Pivot Table field name is not valid” error box appears.
This can happen when the dataset of the Pivot Table or the sheet containing the dataset is deleted.
To solve this,
➤ Insert the dataset in the same location where it was before creating the Pivot Table.
Now, you refresh the Pivot Table and the “The Pivot Table field name is not valid” error message box won’t pop up anymore.
5. Having Blank Column in the Data for Pivot Table Field
Let’s say, you have a dataset with a blank column.
You want to create a Pivot Table with this dataset. So, you have selected the cells and opened the PivotTable from table or range window.
If you click OK in this window, you will see “The Pivot Table field name is not valid” error message box will appear. This is happening because of the blank column of your dataset.
To create a Pivot Table, you have to delete the blank column.
➤ Select the blank column by clicking on column number D.
➤ Right-click on any of the cells of this column.
A dropdown menu will appear.
➤ Click on Delete on this dropdown menu.
As a result, the selected column will be deleted. Now,
➤ Select the dataset, open the PivotTable from table or range window, and click on OK.
This time, the error box won’t appear and the Pivot Table will be created.
6. Hidden Column Causing the Pivot Table Name Is Not Valid Error
If your dataset has hidden columns and any of the hidden columns don’t have a column header then the Pivot Table name is not valid- error can occur.
Suppose, you have the following dataset with hidden columns.
You want to create a Pivot Table with this dataset and so you have opened the Pivot Table from table and range window.
But after clicking OK on this window, you have found that “The Pivot Table field name is not valid” error box appeared. That means one of the hidden columns of your dataset must be missing the column header.
To solve this you have to unhide the hidden columns.
➤ Right-click on the triple bar in the column number row.
This triple bar indicates there are hidden cells. A drop down menu will appear
➤ Click on Unhide from this menu.
As a result, all the hidden columns will be unhidden.
Now,
➤ Add the missing column header in cell D4,
Now, you can create a Pivot Table with your dataset. The error box, “The Pivot Table field name is not valid” won’t appear.
7. Getting The Pivot Table Name is Not Valid Error Due to Having Merged Cells
If your dataset has any merged cells, you must have to unmerge the cells to create a Pivot Table. Otherwise the error box “The Pivot Table field name is not valid” will pop up.
Suppose, we have the following dataset where column D and column E are merged.
Now, if we try to create a Pivot Table with this dataset, the “The Pivot Table field name is not valid” error box will appear.
To solve this, you have to unmerge the merged cells.
➤ Select the merged cells and go to Home > Merge and Center > Unmerge Cells.
It will unmerge the merged cells of your dataset. Now, you will see there is an empty column in your dataset.
➤ Delete the empty column by clicking on Delete from the dropdown menu which appears after selecting and right clicking on the column.
So, now there is no empty column in your dataset and every column has a column header.
This time you will be able to create a Pivot Table from this dataset.
Things to Remember
🚩 There must be no empty column in the selected data range. Every column must have a column header.
🚩 Only the nonempty dataset must be selected as a cell reference, not the total dataset.
🚩 While refreshing the Pivot Table, the dataset used to create the Pivot Table must exist.
Download Practice Workbook
Conclusion
I hope now you know when you may face the error “The Pivot Table field name is not valid” and how to solve this error. If you have any confusion, please feel free to leave a comment.
