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.
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.”
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.
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:
Another common pivot table error message warns about pivot table overlap problems.
You’ll see that 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.
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 the “List All Pivot Table – Details” macro 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.
No Macros: 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.
List Pivot Tables Macros: To see how the macros work, and to get the sample code, download the Pivot Table List Macros workbook. The zipped file is in xlsm format, and contains macros. Enable macros when you open the workbook, if you want to test the macros.
Last updated: March 12, 2021 9:42 AM
Contextures RSS Feed