Like many people, I have a love-hate relationship with software. It’s great when it works, but it’s so frustrating when you get a cryptic error message. And these messages seem to appear when you’re short on time and patience. This happened to me recently when I needed to produce some stats, and Microsoft Excel was telling me my PivotTable field name is not valid. I finally got the issue resolved with these 2 steps.
I was doing some editing on the spreadsheet I used for my Excel pivot table tutorial. Except instead of seeing my results, I got the error below. Most times, I can quickly figure these scenarios out. Or, in the case of Excel formula errors, I use formula auditing. However, this one took some digging.
If you can’t read the Excel error, it reads, “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.“
My solution came down to 2 separate problems.
Solution 1: Unhide Excel Columns
The spreadsheet I was using was extensive. It had 121 columns, and I hid many of them because I wasn’t using them. To see if this was the only issue causing the error, I decided to unhide all the columns.
- Click the Select All button to the left of cell A1.
- Right-click any column heading and select Unhide.
- Try your Pivot Table again.
In my case, I still got the error.
Solution 2: Delete Empty Excel Columns
The next fix took a bit longer and did require I have all columns visible. It seems this spreadsheet had some columns that had headers but no values in any of the cells. Yes, that’s why I hid them.
In my case, I thought it would be easier to make a copy of my spreadsheet and then delete these columns. This way, I still had the original file.
When I created my Pivot Table on the smaller working file, it worked.