How to Fix Pivot Table Field Name Not Valid Error in Excel and Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on March 15, 2023

This tutorial demonstrates how to fix a pivot table field name that is not valid in Excel and Google Sheets.

 

pivoterror intro

 

Invalid Pivot Table Field Name

To create a pivot table in Excel, the data in your worksheet must be setup in a certain format. Excel does not like blank column names, or blank columns within its data! This tutorial troubleshoots some of the reasons for this error occurring.

Missing Column Names

To create a pivot table, you need a row of column headers in the first row of your data. If any of the columns in the first row of your data is blank, you get an error message.

 

pivoterror blank column names

 

  1. Make sure you have column names in each cell of the first row.

 

pivoterror insert column names

 

  1. Then try to create the pivot table again.

Blank Columns

Hidden blank columns can also cause the field name error.

 

pivot error blank hidden columns

 

Select the columns on either side of the hidden column or columns, right-click with the mouse, and then click Unhide to show the hidden columns.

 

pivoterror blank unhide columns

 

Once the column is visible, right-click the column(s) and click Delete to remove the column.

 

pivoterror delete column

 

You can then try to insert your pivot table once again.

Merged Cells

If you have a heading with merged cells, you can get the field name error.

 

pivoterror merged header

 

In the above example, although the Sales Person heading has been merged across Columns B and C, the actual names are separated into two columns.

To fix this, either:
Unmerge B3 and type in First Name and Surname;
OR
Concatenate the names together into one column and remove the merged header.

 

pivoterror merged header fixed

 

Then you can successfully insert a pivot table using the data.

Pivot Table Field Name Error in Google Sheets

Google Sheets is not as fussy as Excel when there are blank column names or hidden columns in a sheet. It creates the pivot table anyway. Then it is up to you as the user to make sure that the rows, columns, and values you insert into the pivot table return the correct data.

  1. In the following example, the data is laid out in a format that is compatible with creating a pivot table. However, there is no heading in cell E2.

 

pivoterror gs no heading

 

  1. When you create a pivot table with this data, Google Sheets just sets a field name (here, Column E) for the unnamed column; the other fields show up with their Row 2 headings.

 

pivoterror gs column e

 

  1. You can use the data in your pivot from the unnamed column as you would with a named column.

 

pivoterror gs use column e

  1. The only “error” that occurs is that the column name is missing; the field shows only SUM of. To solve this problem, type your own name into the pivot field’s value header.

pivoterror gs renamed column

 

Tip: Try using some shortcuts when you’re working with pivot tables.

AI Formula Generator

Try for Free

See all How-To Articles