Contextures

Excel Pivot Table Errors

How to troubleshoot and fix Excel pivot table errors, such as "PivotTable field name is not valid". Find the problem, and fix it.

Introduction

Usually, things go smoothly when you when you try to create a pivot table. However, occasionally you might see a pivot table error, such as "PivotTable field name is not valid". See how to troubleshoot and fix those errors.

Field Name Not Valid

Sometimes, if you try to create or refresh a pivot table, an error message appears:

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

pivot table error field name is not valid

Pivot Table Source Data

If we check the source data for this pivot table, everything looks okay. There are 7 columns of data, and no blank columns or rows in the list.

pivot table source data

Fix the Source Data

The pivot table error, "field name is not valid", usually appears because one or more of the heading cells in the source data is blank. To create a pivot table, you need a heading for each column.

Tip: If you create an Excel Table from your data, column headings are automatically added to columns with blank heading cells, and you can avoid this error.

To find the problem, try these steps:

  • In the Create PivotTable dialog box, check the Table/Range selection to make sure you haven’t selected blank columns beside the data table.
  • Check for hidden columns in the source data range, and add headings if they’re missing.
  • If there are any merged cells in the heading row, unmerge them, and add a heading in each separate cell.
  • Select each heading cell and check its contents in the formula bar; text from one heading may overlap a blank cell beside it. In this example, the Product Name heading overlapped the empty heading cell beside it.

missing heading in source data

NOTE:

  • If there are no blank heading cells, and you are using Excel 2003 or earlier, check for long headings – there is a limit of 255 characters in those versions

Troubleshoot Pivot Table Errors

If the steps above don't help you find and fix the problem, use the “List All Pivot Table – Headings” macro to see the details for all pivot tables.

Copy the code from that page, and paste it into a regular code module, then run the macro.

The macro lists each pivot table in the file, with the following information:

  • Worksheet name
  • Pivot Table name
  • Pivot Cache index number
  • Source Data name or range address

Also, if the source data is a list in the same Excel workbook, it shows details about the source data:

  • Number of records
  • Number of columns
  • Number of heading cells that contain values
  • Fix — an X if number of columns does not match number of headings
  • Latest refresh date for the pivot cache

basic pivot list macro

Download the Sample File

To see the pivot table error message, download the Pivot Table Errors sample file. The zipped file is in xlsx format, and does not contain macros.

More Pivot Table Tutorials

How to Plan and Set Up a Pivot Table

FAQs - Pivot Tables

List All Pivot Table – Headings

Pivot Table Introduction

Clear Old Items in Pivot Table

go to top

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: January 1, 2019 8:18 PM
Contextures RSS Feed