Sữa lỗi pivottable field name is not valid năm 2024

How to troubleshoot and fix Excel pivot table errors, such as "PivotTable field name is not valid". Find the pivot table problems, and fix them, to prevent the error messages.

Note: To change the way that error values show in a pivot table layout, see the Pivot Table Error Values page.

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", or "A PivotTable report cannot overlap another PivotTable report".

This video shows a couple of pivot table problems, how to fix them, and a macro that can help with troubleshooting. There are written steps for troubleshooting, below the video.

You can get the macro and workbook from the Pivot Table List Macros page, to follow along.

Field Name Not Valid

Sometimes, if you try to create or refresh a pivot table in Microsoft Excel, a PivotTable 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 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. There is a pivot table field name at the top of each column.

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 value 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
  • Unhide them, and add a header value, if any column header is 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.

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

Pivot Table Overlap Problems

Another common pivot table error message warns about pivot table overlap problems.

• “A PivotTable report cannot overlap another PivotTable report.”

You’ll see that Excel error message if pivot tables are one the same sheet, and there’s not enough blank space for one of the pivot tables to expand for new data.

Find the Problem Pivot Tables

Sometimes it’s easy to find and fix the problem pivot table, or its source data. But, in a big workbook, with lots of pivot tables, and different data sources, it can be tricky to pinpoint the problem.

To get an inventory of all the pivot tables in your workbook, with details on where they’re located, use to see the details for all pivot tables. The sample file is also available in the download section below

That macro lists each pivot table in the file, with information about its location, size, and source data.

If the source is a worksheet list or table in the same Excel workbook, the macro shows details about that source data.

Duplicate Items in Pivot Table

When you create a pivot table it groups the items from your data, and calculates a total for each group. Occasionally though, you might see duplicate items in the pivot table.

In the following sections, there are some reasons why a pivot table might show:

--1) Duplicates for items

--2) Duplicates for

1) Duplicate Text Items

In this example, there are duplicates for one of the text items in the Row area -- Boston appears 3 times, instead of just once

Even though these items look like duplicates, there is something different about them, and that’s why they’re appearing on separate rows in the pivot table.

Usually, the problem in trailing spaces – one or more space characters are at the end of some items in the data, but not all of them.

Clean Text Entries in Source Data

To get rid of the duplicates in the pivot table, you’ll need to clean up the source data. However, the filters in an Excel table ignore trailing spaces, so it’s not easy to find the problem entries.

There are steps below for 2 types of data cleanup, to help remove pivot table duplicates:

-- A)

-- B)

A) One-Time Cleanup

If the records were manually entered, and you just want to do a one-time cleanup, follow these steps:

1. Filter the table to show just the Boston records
2. Type “Boston” in the first record, to overwrite the existing City entry
3. Select that cell, and point to the fill handle, at the bottom right corner of the cell
4. Double-click on the Fill Handle, to copy the entry down to the last filtered record. (Scroll down to make sure that all the records were changed.)
5. Clear the filter on the City column
6. Refresh the pivot table, and the duplicate items will disappear.

B) Add Cleanup Formula Column

If the records were imported from another source, and you expect that the problems with trailing spaces will be ongoing, you can use a TRIM formula to clean up the data. It will remove any leading or trailing space characters, and any multiple space characters between words.

Follow these steps to add a new field:

• Insert a new column in the source data, with the heading CityName.
• In Row 2 of the new column, enter the formula =TRIM(C2).
• Copy the formula down to the last row of data in the source table.
  • If the source data is stored in an Excel Table, the formula should copy down automatically.
• Refresh the pivot table
• Remove the City field from the pivot table, and add the CityName field to replace it.

In the screen shot below, the duplicate city names no longer appear in the pivot table.

2) Duplicate Numbers

If you see duplicate numbers in the Row area, it's usually because there are small, hidden differences in the numbers, caused by the floating point precision that's used in Excel.

For example, this pivot table shows three numbers that look like duplicates, for the Carrot Bars.

To learn more about this problem, go to the Remove Duplicates Problem page.

You'll see why the duplicates are listed (floating point precision), and how to fix the problem by using the ROUND function.

Couldn't Get Data Error

A simple data change can cause a strange pivot table refresh error, if you added the pivot table data to the Data Model.

Here's an edited version of that message, with some of the text moved, so you can read all of it, in this screen shot. The first couple of lines say this:

• We couldn't get data from the Data Model.
• Here's the error message we got:
• An unexpected error occurred

Fix the Problem

This error message might appear if one of the source data headings was changed from UPPER case to Proper case (or any other type of case change). That can create a second instance of the field in the data model.

To fix this problem, if you see this Excel error message:

• In the source data table, change the heading back to its original case
• Then, refresh the pivot table
• The extra field should disappear from the PivotTable Field List

More Information

For the detailed steps on fixing this pivot table error, and ways to avoid the problem, .

There is also a sample file that you can download, with a pivot table that uses the Data Model.

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 , as Table1.

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