How to Setup Row Labels on a Pivot Table

You may have a similar data set as shown below, but the problem is that the current way it is set up makes the data hard to work with. To solve this, we would like one column for the state and one column for the city instead of the current sub-columns from state to city.

This kind of setup is widely used and called tabular form. Tabular form is helpful for using cells in a pivot for other formulas or Excel functions.

A PivotTable without tabular form.

To get the pivot into the desired setup we need to set up the mentioned tabular form. The tabular form will make a separate column for both city and state.

To get to this format, go to the design tab under pivot tools and change the report layout as shown below.

PivotTable Tools>Design>Report Layout>Show in Tabular Form

 

The resulting pivot will look like this.

PivotTable with tabular form.

This is great, but we would also like the state to be shown next to each county instead of a blank.

In order to repeat all item labels, go to the design tab under pivot tools again, and select the option in the report layout option as shown below.

The resulting Pivot Table will look like this.

PivotTable with tabular form and repeating item labels.

This data is ready to be used in formulas such as IF VLOOKUP.

summary

  • Tabular form can separate row labels
  • Tabular can help make a pivot more understandable
  • Repeating row labels in tabular form makes them easier to use in formulas