Usually, things go smoothly when you when you try to create a pivot table. However, occasionally you might see a pivot table error, Excel Field Names not Valid, if you try to build a new pivot table, or refresh an existing pivot table.
Updated Jan. 1, 2019 – macro to help with troubleshooting the pivot table error
Pivot Table Data
In the screen shot shown below, there’s a list of sales orders, and we’d like to create a pivot table from that data.
Everything looks okay in the source data, but when you try to insert a pivot table, a confusing (and very wide!) 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.”
The message is confusing because the source data is in a list with labeled columns, and you aren’t trying to change the name of any fields.
Fix the Field Name Problem
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.
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.
- 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
Existing Pivot Table
The “field name is not valid” error message can also appear if you try to refresh an existing pivot table, or if you click the Refresh All command in an Excel workbook.
In some cases, you might not know which pivot table is causing the problem, because the pivot table error does not show the name.
Troubleshooting With a Macro
To help identify the problem pivot table, use the “List All Pivot Table – Headings” macro from my Contextures website. 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
More Pivot Table Errors
Problem: Pivot Table Duplicate Items
_________________
Hi Sir, can I remove or replace the “(blank)” word in the following example?
Forecast item DESC Combo Qty per pack Sum of Qty Sold (Sales History)
11-065-00-01-01-001 611006 Cloth (blank) 1 872
11-065-00-01-01-002 611006 Cloth ( 1 x 10) (blank) 1 1,110
11-065-00-01-01-001 Total 1,982
Besides, I would like to copy value & layout of a finished pivot table to another worksheet, but I try several time I can’t copy the format. Please advise how to do that.
Thanks a lot!
Dennis, there are instructions here for changing the (blank) labels.
You can use the Office Clipboard to paste the pivot table formatting.
Thanks for this. I realized even though I wasn’t seeing ‘blanks’ I had some hidden columns that excel was seeing and they had blanks in them. GGRrrrr
Nicole, you’re welcome. Glad you were able to solve the problem by finding those hidden columns.
It worked.
THANK YOU!!! I had a hidden column I didn’t even realize was there. This helped!
Thanks, I didn’t realize that I have blank heading ^^
It’s work ^_____^
Due to my (painful) experience, I may also tell you that a too long text for the column label may be the cause of this error message.
Thanks Tim, and the heading length is 255 characters in Excel 2003. In Excel 2007, that limit seems to be removed, or much higher.
I think it’s 200 characters with spacing.
Thanks It worked….
Hi-
Thanks for your article.
I have 50 or more pivot tables in my Excel 2007 file (Windows 7 32 bit) and sometimes will get this error when I refresh all pivot tables but the error message does not tell me which pivot table is experiencing the error. I’m very confident that it is because of a blank heading (many of my individual worksheet data sources are pulled from external sources and sometimes they do not have headings).
Any tips on how to debug the error/file to find out _which_ pivot table(s) is(are) experiencing the error?
I end up going through each worksheet by hand to find the blank heading – very inefficient.
Thanks so much or any help you can provide.
-Michael
Thank you for this help – I was going to pull my hair out !!n I realised that indeed one of my columns had dropped it’s title (or perhaps it got deleted in error…) in any case you kept me sane.
@Debbie, you’re welcome! Thanks for letting me know that it helped you solve the problem.
Thaaaaaaaaaaaaaaaanks
it worked 🙂
@Michael, I am having ther same problem, has anyone created something to debug the whole workbook and locate the error?
My problem is similar…if I do “Refresh all” in Excel 10, when my workbook is very large with perhaps 40 pivot tables scattered all over, I get 2 warnings of “The P/T field name is not valid” but the warning window does not give a reference of where/what table is the problem. Is there a way to debug this without going to each individual table to do a “refresh” to locate it??
Thanks
Joe
I’m in the same boat as Michael, Suzy and Joe. I have no blank cells in my headers and no merged cells. My source data is correctly assigned as a name, and the dozens of pivot tables that I have use the name as their source data. So I’d imagine that any issues would be either in the source data range or in the named range, both of which look correctly setup. Oh, and another thing…this issue didn’t exist months ago…it popped up recently, but there has been no changes to the sheet that contains the source data.
I have an update for my last post. I went through my spreadsheet deleting one tab at a time (saved the file as a test file) to see which tab, if any, would make a difference. I was fortunate to find one tab that was causing the issue. Some of my tabs have multiple pivot tables and some only have one, so I wasn’t sure how this witch hunt was going to turn out.
Sometimes I’ll copy a tab with a pivot table because I want the same basic info but I want to do something different with it…and I’m too lazy to start a new tab from scratch and configure everything all over again. I believe this is what happened here, except part of the table somehow became severed (and it was in a hidden column which was really weird). So the main table that I was using on this particular tab was functioning fine, but the hidden, severed, table was the culprit! And when I say severed, I guess I mean that there was a remnant of a table, but it only occupied a single column, and when I clicked on it, it did not bring up the pivot table menus off tot he right.
So this does not address an easy way of troubleshooting the issue, but maybe this will help others figure something out.
Thanks Jason, that may indeed help someone else.
its really useful info… thanks ..
Thank you! Thank you! Thank you!
I have a large worksheet(A1 thru IA17000)which is plagued with the problem that, whenever one tries to insert or delete a column or row the error message “Excel cannot complete this task with available resources. Choose less data or close other applications.” reports. The data in this spreadsheet was copied from another workbook’s spreadsheet and pasted into this NEW workbook’s sheet1 as “VALUES” to try to get away from this problem. The problem may be related to the original workbook containing too many worksheets…as daily reports… which report in to a worksheet used as a database in the original and likely over loaded workbook. As I say, the original workbook is probably overloaded with too many columns and/or rows or just too much redundant data. The data, copied to the new workbook’s sheet1 as “VALUES” only, still persists with the error restiction. Can you offer advice as to how I can save the data from the original workbook’s “database” spreadsheet (where all the report data is collected)so that it can be used (additional columns and rows can be inserted, pivot tables built, and etc.). I should mention that another problem, likely an extension of the before error message, is that when I attempt a pivot table in either the original or the new workbook, the error message “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.” reports and no pivot table is possible, so any advice on this one also?
Thanks,Tom
Also, make sure your data tab headers are actually on row 1, not accidentally pasted starting on row 2, so that row 1 is blank. That was my error. Just oversight…
Many thanks – well explained.
THANK YOU! This was driving me crazy until I checked my named range and sure enough, it was one column too long and had a blank title…
Hi,
I am getting the error even if I don’t have blank columns. What should I do? One thing is for sure though, I have merged cells on my columns, does that affect anything?
Thanks
Thank you!!! I was wondering what went wrong until I discovered a merged heading. It helped with a great deal.
Thank you so much!!!!!!!!!!!!!!!!!!!!!!
Thank you!!
Thanks dear
blank cell … blank TITLE on Row 1, top of column
Thanks! It works now.
I created this pivot table that i have refreshed cause i’ve done a feww changes on the data source and for some reason it is not showing some years on my columns. Its still show the same grand total but the some of the years is not displaying. I’ve tried to unhide them and it seems to do nothing . “What am i missing???
its worked.. many thxs
Thank you very much, that was driving me insane! Generic error messages suck!
You’re welcome! Thanks for letting me know that it helped.
Oh, THANK YOU!!! Found out I had a hidden column.
I couldn’t create a pivot (huge data set!) until I copied and pasted the data into another worksheet, but it works now.
Thank you for saving my sanity!
Who knew a dag-nabbed empty header field could cause so much trouble?
(Well, you did.)
Thanks!
You’re welcome! Thanks for letting me know that it helped
Thank you. My sanity this close to Christmas is most precious.
My error was generated because I added a column to a master spreadsheet (~5600 rows by 18 columns) which I had already started to split into subsets (5 done, 11 to go) separated by repeated column headers and all range named to make generating and maintaining the pivot tables easy. It was only when I filtered for an original column header then looked across the sets that I found the gaps for the subsets. Problem was I’d made the change to the top set of headers but not all lower sets of headers. Doh!
Thanks for the pointers. They still work years after the original post. 🙂
Thanks Debra
Another Issue that I have come across is when your datatable contains formulas as headings in columns it can have the same impact as no heading
Thank you….. your help…Managed to solve the issue
found the blank; my harsh is mellowed
Whew! Thanks for letting me know that it helped.
Debre
Great help, TY!
There is another case I faced that the range of an existing pivot table got changed somehow and faced this error while refreshing. Solution is just go to Analyze->Change Data Source, Select the range again and Save. The error should go.
Thank you, it solved my problem 🙂