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.