Excel Pivot Tables - Nesting

If you have more than one field in any of the PivotTable areas, then the PivotTable layout depends on the order you place the fields in that area. This is called the Nesting Order.

If you know how your data is structured, you can place the fields in the required order. If you are not sure about the structure of the data, you can change the order of the fields that instantly changes the layout of the PivotTable.

In this chapter, you will understand the nesting order of the fields and how you can change the nesting order.

Nesting Order of the Fields

Consider the sales data example, where you have placed the fields in the following order −

As you can see, in the rows area there are two fields – salesperson and region in that order. This order of the fields is called nesting order i.e. Salesperson first and Region next.

In the PivotTable, the values in the rows will be displayed based on this order, as given below.

As you can observe, the values of the second field in the nesting order are embedded under each of the values of the first field.

In your data, each salesperson is associated with only one region, whereas most of the regions are associated with more than one salesperson. Hence, there is a possibility that if you reverse the nesting order, your PivotTable will look more meaningful.

Changing the Nesting Order

To change the nesting order of the fields in an area, just click the field and drag it to the position you want.

Click on the field Salesperson in the ROWS area, and drag it to below the field Region. Thus, you have changed the nesting order to – Region first and Salesperson next, as follows −

The resulting PivotTable will be as given below −

You can clearly observe that the Layout with the nesting order – Region and then Salesperson yields a better and compact report than the one with the nesting order – Salesperson and then Region.

In case a Salesperson represents more than one area and you need to summarize the sales by Salesperson, then the previous Layout would have been a better option.