Tanjima Hossain
If you are facing the Pivot Table field name is not valid error while creating a Pivot Table and looking for the solutions, then you are in the right place. In this article, you will get to know the causes of this error and the ways to fix this problem easily.
Here, we will discuss the causes and the solution techniques of the Pivot Table field name is not valid error. For this purpose, we are using the following table (some alterations may occur later for different sections) to demonstrate the issues of this error.
You can follow the article “Creating a Pivot Table Automatically” to create a Pivot Table.
Microsoft Excel 365 version has been used here; you can use any other version according to your convenience.
1. Having Pivot Table Field Name Is Not Valid Error Due to the Selection of the Entire Sheet
Here, we will demonstrate the cause of this error due to the selection of the entire sheet instead of selecting the correct range.
Problem:
➤ Go to Insert Tab >> PivotTable option.
Then the PivotTable from table or range dialog box will appear.
➤ We have selected here the entire sheet in the Table/Range option and then pressed OK.
After that, the following message box will appear and it is saying that The PivotTable field name is not valid.
For selecting the entire worksheet instead of the correct range we are having this error here.
Solution:
To solve this problem, we have to select the correct range of the table $B$4:$E$13 of the selection sheet.
After giving input the correct range, we have been able to create the Pivot Table correctly in a new sheet.
Read more: [Fix] The Pivot Table Name Is Not Valid
2. Facing Pivot Table Field Name Is Not Valid Error Due to Hiding Column
We have hidden the Salesperson column of the following table here and due to this reason, we will have the error while creating the Pivot Table.
Problem:
After selecting the Pivot Table option, the PivotTable from table or range dialog box will appear
➤ Select the range in the Table/Range option and press OK
Now, you are getting an error message box and it is saying that The PivotTable field name is not valid.
You can see, we are having this error here because of hiding the column.
Solution:
To solve the problem, at first select the entire table range.
➤ Go to Home Tab >> Format Group >> Hide & Unhide Dropdown >> Unhide Columns option
In this way, we will unhide the hidden column.
Now, select the range in the Table/Range option and press OK
After unhiding the hidden column, you will be able to create the Pivot Table correctly in a new sheet.
Read more: Pivot Table is Not Picking up Data in Excel
3. Having an Empty Column Can Cause the Pivot Table Field Name Is Not Valid Error
Here, we have an empty column inside our data table and with this table, we will try to create a Pivot Table in this section.
Problem:
After selecting the Pivot Table option, the PivotTable from table or range dialog box will appear
➤ Select the range in the Table/Range option and press OK
After that, the following message box will appear and it is saying that The PivotTable field name is not valid.
So, we have found that the empty column in the source range can cause this error.
Solution:
To resolve this error, you have to delete the empty column before selecting the Pivot Table option.
➤ Select the empty column and go to Home Tab >> Delete Drop down >> Delete Sheet Columns Option.
In this way, you will be able to remove the empty column.
Then, select the correct range and press OK to create the Pivot Table.
Finally, you will be able to create the Pivot Table in a new sheet.
4. Missing Header of a Column
In the following table, we have a column with an empty header inside our data table, and with this table, we will try to create a Pivot Table in this section.
Problem:
After selecting the Pivot Table option, the PivotTable from table or range dialog box will appear
➤ Select the range in the Table/Range option and press OK.
Now, you are getting an error message box and it is saying that The PivotTable field name is not valid.
Due to the empty header, it is causing this error here.
Solution:
To solve this issue, we have to assign a header value in cell D4 (the header row of the column with an empty header).
Then, try again with the Pivot Table option and you will have the following wizard.
➤ Select the correct range and press OK.
Now, you will be successful in creating the Pivot Table in a new sheet.
5. Unmerging the Merged Header of a Column
In this section, we will discuss the problem of having a merged header like below in a table.
After selecting the Pivot Table option, the PivotTable from table or range dialog box will appear
➤ Select the range in the Table/Range option and press OK.
After that, the following message box will appear and it is saying that The PivotTable field name is not valid.
The merged header row of the Product column is causing the error here.
Solution:
To sort out this matter, we have to unmerge the header at first.
➤ Select the merger header, and go to Home Tab >> Merge & Center Group >> Unmerge Cells Option.
After that, the merged header will be unmerged now and then you have to delete the empty column besides the Product column.
Now, our data table is completely ready and it is time to select the Pivot Table option.
➤ Select the data range in the Table/Range option and press OK.
Finally, you will be able to create the Pivot Table in a new sheet.
Similar Readings
- Excel Table Name: All You Need to Know
- How to Edit a Pivot Table in Excel (5 Methods)
- Update Pivot Table Range (5 Suitable Methods)
- How to Refresh All Pivot Tables in Excel (3 Ways)
6. Facing Pivot Table Field Name Is Not Valid Error Due to Overlapped Header
We can see that we have a header of the first column overlapped with the second column and it will be the source of the Pivot Table field name is not valid error. So, in this section, we will try to solve this issue.
Problem:
After selecting the Pivot Table option, the PivotTable from table or range dialog box will appear
➤ Select the range in the Table/Range option and press OK.
Now, we are having an error message box and it is saying that The PivotTable field name is not valid.
The overlapped header is the cause of the error here.
Solution:
To resolve this issue, we have to enlarge the column width so that the header name can be fitted in this width.
➤ Hover your mouse around Column B and drag the indicated sign to the right.
In this way, you will be able to enlarge the column width and fit the header in this width.
➤ Type the header name of the second column which was blank.
After that, we will proceed with the Pivot Table option.
➤ Select the data range of your table in the Table/Range option and press OK.
Afterward, you will be able to create the Pivot Table in a new sheet.
7. Deleting the Entire Data Range After Creating the Pivot Table
Here, we have the following data table which is correct from all types of previous issues and so using this table we can create a Pivot Table easily. But if you delete your source data range after creating Pivot Table then you can face this error.
Problem:
After creating the Pivot Table, we will get the following Pivot Table in a new sheet.
➤ Drag down the Product field to the Rows area and Sales field to the Values area.
In this way, we will get our desired table.
Now, go to the source range and select the range.
➤ Go to Home Tab >> Delete Drop down >> Delete Sheet Rows Option
Then, the source data range will be deleted.
Now, go to the sheet having the Pivot Table.
➤ Go to PivotTable Analyze Tab >> Data Group >> Refresh Option
Now, we are having an error message box and it is saying that The PivotTable field name is not valid.
So, for deleting the entire source range we are having this error here.
Quick Note:
Keep remembering that, you cannot delete the source range even after creating the Pivot Table.
8. Deleting Column Header After Creating Pivot Table
Here, we have the following data table which is correct from all types of previous issues and so using this table we can create a Pivot Table easily. But if you delete any header from your source data range after creating Pivot Table then you can face this error.
Problem:
After creating the Pivot Table, we will get the following Pivot Table in a new sheet.
➤ Drag down the Product field to the Rows area and Sales field to the Values area.
In this way, we will get our desired table.
Now, go to the source range and select the range.
➤ Delete the assigned header Salesperson of Column D
After that, go to the sheet having the Pivot Table.
➤ Follow PivotTable Analyze Tab >> Data Group >> Refresh Option
After that, you will have an error message box and it is saying that The PivotTable field name is not valid.
So, for deleting the header of a column in the source range after creating the Pivot Table we are having this error here.
Quick Note:
You have to be aware of the fact that you cannot delete any header from any column of your source range even after creating the Pivot Table.
9. Changing Field Name Within a Pivot Table Can Cause Pivot Table Field Name Is Not Valid Error
Sometimes, you may be able to create a Pivot Table correctly without an error but after creating this table you may need to change any Field name and due to not following the proper steps you may find the PivotTable field name is not valid error.
In this section, we will introduce you to the easiest way to change any field name correctly.
Here, we have the following Pivot Table where we have the Product field in the Rows area and the Sales field in the Values area.
Now, we want to change the field name Product into Item.
➤ Select the Field name which you want to change.
➤ Follow PivotTable Analyze Tab >> Active Field Group >> write the name Item in the Active Field Box
In this way, you will be able to rename your desired Field name easily.
Read more: [Fixed!] Pivot Table Field Name Already Exists
Things to Notice
🔺 While selecting the data range for a Pivot Table, you have to be careful about the selection of the correct range, not the whole worksheet.
🔺 In the selected data range, there cannot be any empty column.
🔺 We should assign header values for all of the columns of our data set.
🔺 You cannot hide any column before selecting the range.
🔺 We have to unmerge all of the headers of the columns before selecting the range.
Download Practice Workbook
Conclusion
In this article, we tried to demonstrate some of the causes and solutions of the Pivot Table field name is not valid error. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
