Pivot table field name not valid

If you encountered the error with the very long text.

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

Replicating the error

This error can be replicated in the following way.

Use the following example.

Copy the following table and paste it inside Excel.

First Name Last Name Location Location Age
John Doe USA New York 54
Mark Snow Poland Warsaw 34
Jack Shadow Germany Berlin 26

In order to replicate this error, select cells C1 and D1. Now, click Home >> Alignment >> Merge & Center. If a new message appears, click OK.

Cells C1 and D1 and now merged into a single header.

Let’s try to insert the pivot table. Select cells from A1 to E4 and click Insert >> Tables >> Pivot Table.

But despite selecting the whole table, Excel avoid selecting header because one of the cells is merged there.

You can force application to select it anyway, by changing the selection.

Now, if you press OK, you will get the error.

But there is another way. Hide columns C and D and click any column inside the table.

If you try to insert the pivot table, Excel will select all the visible cells. Now, if you click OK, it will result in the error.

Fixing the error

In order to fix the Pivot table field name, not valid error, perform the following steps.

  1. Select all columns inside the table
  2. Right-click them and then click Unhide.

  1. Check for merged cells and unmerge them by clicking Home >> Alignment >> Merge & Center.