Nested Fields in Excel PivotTable

In this post, you’ll learn about Nested fields in Excel Pivot table and how you can use it in your Excel worksheet.

Nested Fields in Excel PivotTable

When you have more than one field of data in an excel sheet, you can nest them or combine them into a meaningful cluster in the Pivot table which gives a meaningful report at the final stage. The PivotTable layout works depending on the order you place the fields in that area. This process is called nesting, and the fields in it are called nested fields.

Let’s understand the Nested fields’ concept in detail with the help of an example.

Take a look at the data given below:

Nested Fields in Excel PivotTable

It is a sales report of firm for the Year 2017-2019.

Now, let’s insert the pivot table in a new sheet.

  • From the field list, select the fields that you need for your report
  • Drag and place them in your required area.
Nested Fields in Excel PivotTable

Here I have selected the Year of sale, Area Covered and Name of the salesman for the row field and the rest in the values field.

Nested Fields in Excel PivotTable

Now click on the ‘+’ icon near each Year of sale.

Nested Fields in Excel PivotTable

Areas covered during that Year of sale are listed inside the “Year of sale” field.

Nested Fields in Excel PivotTable

Click on the ‘+’ icon near the ‘Areas Covered’ and you can find the names of the Salesperson for each area during that particular Year of sale.

So the Name of the Sales person is nested inside the ‘Areas Covered field. And then the Areas Covered field is nested inside the “Year of sale” field.

If we alter the position of row labels, then the report will change. For example, let’s move the ‘Name of the Sales person’ field between ‘Year of Sale’ and ‘Area Covered’.

Nested Fields in Excel PivotTable

Now the report looks like this.

When we click the year field, it shows the Name of the Sales persons working in that period of time.

Nested Fields in Excel PivotTable

And when click the sales person’s name, it then shows the area covered by them.

Nested Fields in Excel PivotTable

Therefore, when you understand your raw data, it is pretty easy to nest the fields inside other fields, making your report meaningful.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Hey, wait!Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.