Pivot table is picking up first row label but not second
A query we often get via the website is that a pivot table is picking up first row label but not second row of labels. The reason for this is very simple.
Pivot tables require that the source data be in a specific format. One of the main requirements is that the labels above each column must be in a single cell and not spread over 2 cells or in a merged cell.
If you do have the label over 2 cells, either combine them into a single cell, or create a new row and concatenate the labels and use the new row as the first row in the pivot table data.
Other requirements for the format of a data source for a pivot table are (or watch the video on Setting up a Pivot Table):
- Every column in the data must have a label
- The label must be in a single cell as described above
- Every label must be unique (not strictly enforced in later versions of Excel but we recommend you rather use unique labels)
- Although not important for the Pivot Table, every label should be descriptive in that you should know what data is in the column by only reading the label and not having to look at the data as well
- The data should not contain any subtotals or totals. This could result in double counting if you are not careful
- The format of the data must be understood by data i.e. if a column contains dates, Excel must recognise the data as being dates. This opens up new features in a pivot table.
- Each row of data must contain information for all the columns. The video clip mentioned above explains this better.
- Although not vital, blanks in the data are not ideal and can limit some of the Pivot Table features e.g. 1 blank in a date field results in Excel not recognising the dates and you not being able to group in months (see another common issue with Pivot Tables).
- Data should be in rows and not columns (watch the video clip)
Learn more on our pivot tables courses or via the pivot table ebook