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.
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.
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.
This data is ready to be used in formulas such as IF VLOOKUP.