Table of Contents
Multi-level Pivot Table
You can drag more than one field to an area in a pivot table. We’ll look at an example of multiple row fields multiple value fields and multiple report filter fields.
Remember our data set consists of 213 records and 6 fields. Order ID Product Category Amount Date and Country.
Multiple Row Fields
First insert a pivot table. Next drag the following fields to the different areas.
1. Category field and Country field to the Rows area.
2. Amount field to the Values area.
Below you can find the multi-level pivot table.
Multiple Value Fields
First insert a pivot table. Next drag the following fields to the different areas.
1. Country field to the Rows area.
2. Amount field to the Values area (2x).
Note: if you drag the Amount field to the Values area for the second time Excel also populates the Columns area.
Pivot table:
3. Next click any cell inside the Sum of Amount2 column.
4. Right click and click on Value Field Settings.
5. Enter Percentage for Custom Name.
6. On the Show Values As tab select % of Grand Total.
7. Click OK.
Result:
Multiple Report Filter Fields
First insert a pivot table. Next drag the following fields to the 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.
4. Next select United Kingdom from the first filter drop-down and Broccoli from the second filter drop-down.
The pivot table shows all the ‘Broccoli’ orders to the United Kingdom.
