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.

FieldNames01

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.”

FieldNames02

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.

FieldNames03

Tip: If you create an Excel Table from your data, column headings are automatically added to columns with blank heading cells.

_________________

This entry was posted in Excel Pivot Table. Bookmark the permalink.

38 Responses to Pivot Table Error: Excel Field Names Not Valid

  1. Dennis says:

    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!

  2. Nicole says:

    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

  3. Skee says:

    It worked.

  4. Kate says:

    THANK YOU!!! I had a hidden column I didn’t even realize was there. This helped!

  5. Fern says:

    Thanks, I didn’t realize that I have blank heading ^^
    It’s work ^_____^

  6. Tim says:

    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.

  7. Tim says:

    I think it’s 200 characters with spacing.

  8. Vineet Kushwaha says:

    Thanks It worked….

  9. Michael says:

    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

  10. Debbie says:

    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.

  11. Magdy Goda says:

    Thaaaaaaaaaaaaaaaanks
    it worked 🙂

  12. Suzi says:

    @Michael, I am having ther same problem, has anyone created something to debug the whole workbook and locate the error?

    • Joe D says:

      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

    • Jason says:

      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.

      • Jason says:

        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.

  13. sudheer K says:

    its really useful info… thanks ..

  14. India Pajeau says:

    Thank you! Thank you! Thank you!

  15. Tom Trigg says:

    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

  16. Max Keefe says:

    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…

  17. jojo says:

    Many thanks – well explained.

  18. Harrison Spain says:

    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…

  19. PeerlessContrivance says:

    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

  20. Zacq says:

    Thank you!!! I was wondering what went wrong until I discovered a merged heading. It helped with a great deal.

  21. Martina says:

    Thank you so much!!!!!!!!!!!!!!!!!!!!!!

  22. Ara says:

    Thank you!!

  23. Shah says:

    Thanks dear

  24. James H. Copeland says:

    blank cell … blank TITLE on Row 1, top of column

  25. Carmen says:

    Thanks! It works now.

  26. Jojo says:

    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???

  27. raja says:

    its worked.. many thxs

  28. Cynthia Snider says:

    Thank you very much, that was driving me insane! Generic error messages suck!

  29. Lori says:

    Oh, THANK YOU!!! Found out I had a hidden column.

Leave a Reply

Your email address will not be published. Required fields are marked *