[Fix] The Pivot Table Name Is Not Valid (7 Causes with Solutions)

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.

dataset


Download Practice Workbook


7 Cases and Solutions Where “The Pivot Table Name Is Not Valid” Error Occurs

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.

insert pivot table

To do that,

➤ Go to the Insert tab and select Pivot Table from Tables ribbon.

pivot table

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.

the pivot table name is not valid

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.”

the pivot table name is not valid

➤ 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.

the pivot table name is not valid

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.

the pivot table name is not valid

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.

the pivot table name is not valid

After clicking OK in the PivotTable from table or range window you will see a Pivot Table will be created in your selected location.

the pivot table name is not valid

Read more: Pivot Table is Not Picking up Data in Excel


2. 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 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.

the pivot table name is not valid

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.

the pivot table name is not valid

As I’ve deleted a column header, “The Pivot Table field name is not valid” error message box will appear.

the pivot table name is not valid

To solve this problem,

➤ Type the text which got deleted from the column header in cell D4.

the pivot table name is not valid

Now, you can refresh the Pivot Table. This time the error message won’t be shown.

the pivot table name is not valid

Read more: [Fixed!] Pivot Table Field Name Already Exists


3. Selecting Entire 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.

data

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.

field

As a result, a Microsoft Excel error box stating that “Data source reference is not valid” will appear.

error box

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.

pivot box

➤ Click OK.

This time no error box will appear and a sheet will be added containing the Pivot Table.

pivot table

Read more: How to Refresh Pivot Table in Excel


4. The Pivot Table Name is Not Valid 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.

refresh

All of a sudden, the “The Pivot Table field name is not valid” error box appears.

error

This can happen when the dataset of the Pivot Table or the sheet containing the dataset is deleted.

empty

To solve this,

➤ Insert the dataset in the same location where it was before creating the Pivot Table.

data

Now, you refresh the Pivot Table and the “The Pivot Table field name is not valid” error message box won’t pop up anymore.

refresh

Read more: How to Refresh All Pivot Tables in Excel


Similar Readings


5. Blank Column in the Data for Pivot Table Field

Let’s say, you have a dataset with a blank column.

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.

the pivot table name is not valid

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.

the pivot table name is not valid

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.

delete

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.

the pivot table name is not valid

This time, the error box won’t appear and the Pivot Table will be created.

pivot table

Read more: How to Group Columns in Excel Pivot Table


6. Hidden Column Causing the Pivot Table Name is Not Valid Error

If your dataset has hidden columns and any of the hidden columns doesn’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.

dataset

You want to create a Pivot Table with this dataset and so you have opened the Pivot Table from table and range window.

the pivot table name is not valid

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.

the pivot table name is not valid

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 dropdown menu will appear

➤ Click on Unhide from this menu.

delete

As a result, all the hidden columns will be unhidden. To see more ways to unhide columns click here.

column

Now,

➤ Add the missing column header in cell D4,

the pivot table name is not valid

Now, you can create a Pivot Table with your dataset. The error box, “The Pivot Table field name is not valid” won’t appear.

the pivot table name is not valid


7.  The Pivot Table Name is Not Valid for 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.

the pivot table name is not valid

Suppose, we have the following dataset where column D and column E are merged.

the pivot table name is not valid

Now, if we try to create a Pivot Table with this dataset, “The Pivot Table field name is not valid” error box will appear.

unmerge cells

To solve this, you have to unmerge the merged cells.

➤ Select the merged cells and go to Home > Merge and Center > Unmerge Cells.

empty column

It will unmerge the merged cells of your dataset. Now, you will see there is an empty column in your dataset.

delete column

➤ 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.

the pivot table name is not valid

This time you will be able to create a Pivot Table from this dataset.

the pivot table name is not valid


Things to Remember 

🚩 There must be no empty column in the selected data range. Every column must have a column header.

🚩 Only the non empty dataset must be selected as cell reference, not the total dataset.

🚩 While refreshing the Pivot Table, the dataset used to create the Pivot Table must exist.


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.


Further Readings

Tags:

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo