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.”
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.
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.
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.
If one were to summarize values by Sum, the result is shown on the right. This is also incorrect.
Using CA as an example and looking at the data, we want the result to say 1 HOA as stated by our data set.
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.