Excel Pivot Table with nested rows

A Pivot table is one of the powerful features in excel for any data analyst. The feature allows you to extract data from a large dataset. You can also create a multi-level Pivot table to analyze data.

To help you understand how to create a nested PivotTable, we will use a data set consisting of six fields: Product, Customer, Qtr11, Qtr2, Qtr3, and Qtr4.

Excel Pivot Table Image

Steps

     1. Insert your pivot table. Click Insert Menu, under Tables group choose PivotTable.

Excel Pivot Table Image

2. Once you create your pivot table, add all the fields you need to analyze data.

How to add the fields

  • Select the checkbox on each field name you desire in the field section. The selected fields are added to the Row Labels area in the layout section.
  • You can drag a field you want from the field section to an area in the layout section.
  • You can also right-click on a certain field then choose an appropriate command. E.g. Add to Report Filter, Add to Column Label, Add to Row Label or Add to Values.

Fields in PivotTable Report

  1. Report Filter: It filters your entire report based on selected items.
  2. Column Labels: It displays fields to be added in the column area of the report
  3. Row labels: Displays row fields in the report. The rows can be nested within one another to group data in a certain manner.
  4. Values: Used to display numeric data summary.

Excel Pivot Table Image

The pivot table below shows the layout of the selected field.

Excel Pivot Table Image

Nested Pivot Table

If you select more than one field in the PivotTable area, you form a nested Pivot table. The layout of the PivotTable depends on the order in you place the fields.

Excel Pivot Table Image

The table on the left shows a multi-level pivot table with the results of the selected fields. This table shows summarized data with customers' names and the products bought.

Excel Pivot Table Image

Adding fields to the row label automatically groups your PivotTable based on customers' names as Level one followed by products grouped as level 2.

If you don't need any field added, you can delete it by dragging the field out of the PivotTable fields right click and then select delete field.