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. Microsoft Excel was telling me my PivotTable field name is not valid. In this tutorial, I will show how to fix this with 2 easy solutions.
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.