Lỗi the pivottable field name is not valid. to create a pivottable report

Reference Isn't Valid Error

Recently, when I opened an Excel file, an error message appeared, "Reference isn't valid".

There was a chart behind the message, and I (incorrectly) assumed that was the problem. However, the error turned out to be a pivot table problem - the source data table was broken!

I had to reformat the data as a named table, and that fixed things. See the details below, if you run into a similar problem.

Pivot Table Source Data Problem

In my workbook, a pivot table was causing the problem. The pivot table's source data was a table that no longer existed in the workbook. As soon as I fixed the table, the error message stopped appearing

Fixing the Problem - Details

To fix the problem I did these two things, and there are detailed steps below:

-- 1) Located the pivot table's data source

-- 2) Found and fixed the problem in the source data

1) Locate Pivot Table Data Source

To find the data source of a pivot table, follow these steps:

1. Select any cell in the pivot table.
2. On the Ribbon, click the PivotTable Analyze tab
3. In the Data group, click the top section of the Change Data Source command.

The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box. For my pivot table, it showed a table name - Table1

Usually, that table or range is highlighted in the background, but I could still see the pivot table in the background. So, I clicked the Cancel button, and went to the sheet where the data was stored.

2) Find Problem in Source Data

When I checked the data sheet, there was no table - just a list on the worksheet. Nobody else uses the workbook, so how did the formatted Excel table go back to being just a list?

Table Removed During Repair

• Excel had crashed earlier in the day, and the workbook was automatically repaired.
• There must have been some corruption in that table, so Excel removed the table structure
• The "We repaired your workbook" message doesn't give many details, and I hadn't noticed the missing table.

Fix Problem in Source Data

To fix the Reference isn't valid error, I formatted the list as an Excel table again.

• Note: Excel automatically named the table as Table1 again, but if it hadn't, I could have renamed the new table, as Table1.

Next, I checked the Change PivotTable Data Source dialog box again, and the table was correctly highlighted in the background.

• In the screen shot below, you can see the "marching ants" border at the top right of the table data.

I clicked Cancel, and the error message didn't appear again, when I refreshed the pivot table.

I am getting "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" error when I open my Excel sheet.

I ran a macro and found error pivot table. But strangely that sheet doesn't have any Pivot or its hidden. Is there a way I can find and Delete this Pivot ?

Tho Vu

1,2242 gold badges6 silver badges20 bronze badges

asked Aug 21, 2020 at 12:53

This error message 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.

Admitting that I do not have any further background details on your situation, you could try the following:

• Unhide Excel columns, in case you have hidden cells (you mentioned to have already completed this check)
• Delete empty Excel columns or use a name as column header
• In the Create PivotTable dialog box, check the Table/Range selection to make sure you haven’t selected blank columns beside the data table.
• If there are any merged cells in the heading row, unmerge them and add a heading in each separate cell.
• Last but not least, consider trouble shooting with a macro

answered Aug 21, 2020 at 18:39

Malte SusenMalte Susen

7062 gold badges9 silver badges33 bronze badges

2

This page has a macro that will list the pivot tables in a workbook. I'm not familiar with a way to find them from the ribbon.

answered Aug 21, 2020 at 18:55

I don't think this will help in every case, but I got this error and was only able to get rid of it by recreating the pivot table (which means the source data wasn't the problem in my case).

answered Nov 29, 2021 at 22:16

Dwayne DriskillDwayne Driskill

1,4601 gold badge13 silver badges19 bronze badges