The Best Way to Setup Data for Pivot Tables

When preparing a Pivot table in Excel one must ensure that the data set is not missing any headers. Missing a header will result in the following error message:

“The PivotTable field 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.”

Microsoft Excel PivotTable missing header error message.

In this case, column F data is causing this issue because cell F1 (the header) is blank. One can either delete the column or include any kind of header as shown below.

We chose to add a header to column F by just inputting an “x”. Now that all our data columns have headers, we can insert a Pivot Table.

Click here for help creating a Pivot

Always check that all data is selected when first creating the Pivot Table to avoid issues. If a Pivot table is already created, double-checking all data is selected will avoid incorrect data problems.

In order to check that all data is selected, go to the Analyze tab under PivotTable Tools at the top of the workbook. Once there, select “Change Data Source.”

Analyze>Change Data Source>Change Data Source

Once change data source is selected look to see if all data is selected in the Table/Range section as shown below. In this case, we want to make a Pivot from all cells in the B2:G252 range.

Checking all PivotTable data is selected.

Another task to consider when setting up data for Pivots is to change non-number values such as any (Yes/No) columns. This allows the pivot to read the data clearly. In the next example the end goal is to see how many properties have HOAs in each state.

As seen in the previous data set, HOA? (Y/N) was not changed and will result in the following if broken out by state. This is not correct.

Incorrect PivotTable with non-numeric fields.

If one were to summarize values by Sum, the result is shown on the right. This is also incorrect.

Incorrect PivotTable with non-numeric fields summarized by sum.

Using CA as an example and looking at the data, we want the result to say 1 HOA as stated by our data set.

Data showing California only has one HOA.

To get to our answer we can quickly filter and change all “No”s to 0s and “Yes”s to 1s. Our result is shown below after doing so and refreshing the Pivot.

Numeric field used in pivot.