Pivot Table Error: Excel Field Names Not Valid
Usually, things go smoothly when you when you try to create a pivot table. 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. 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.
Tip: If you create an Excel Table from your data, column headings are automatically added to columns with blank heading cells.
______________
For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.
_________________
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 http://www.pivot-table.com/excel-pivot-tables/changing-blank-row-labels” rel=”nofollow”>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.
I think it’s 200 characters with spacing.
Thanks Tim, and the heading length is 255 characters in Excel 2003. In Excel 2007, that limit seems to be removed, or much higher.
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.