How to Create Multi-level Pivot Table in Excel

by Ebubechukwu Okoli
0 comment

Yes It’s normal to drag more than one field to an area in a pivot table. We will look at an example of multiple row fields, multiple report filter fields, and multiple value fields.

Always Remember, The data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.

Pivot Table Data in Excel

Multiple Row Fields

Firstly, insert a pivot table. Then, drag the below fields to different areas.

1. Category field and Country field to the Rows area.

2. Amount field to the Values area.

PivotTable Fields Pane

The Below image shows the example of multi-level pivot table.

Multiple Row Fields

Multiple Value Fields

Firstly, insert a pivot table. Then, drag the below fields to different areas.

1. Country field to the Rows area.

2. Amount field to the Values area (2x).

PivotTable Fields Pane

Note: if you drag the Amount field to the Values area for the second time, The Excel will also populates the Columns area.

Pivot table:

Multiple Value Fields

3. Next, click any cell inside the Sum of Amount2 column.

4. Right click and click on Value Field Settings.

Value Field Settings

5. Enter Percentage for Custom Name.

6. On the Show Values As tab, select % of Grand Total.

Show Values As

7. Click OK.

Result:

% of Grand Total

Multiple Report Filter Fields

Firstly, insert a pivot table. Then, drag the below fields to different areas.

1. Order ID to the Rows area.

2. Amount field to the Values area.

3. Country field and Product field to the Filters area.

PivotTable Fields Pane

4. Next, choose the United Kingdom from the first filter drop-down and Broccoli from the second filter drop-down.

The pivot table will shows all the ‘Broccoli’ orders to the United Kingdom.

Multiple Report Filter Fields

You may also like

Leave a Comment

error: Content is protected !!