MO-201 · Question #21
On the "Regional Sales" worksheet, modify the PivotTable to display the "Territory" rows within each region.
PivotTable: Adding a Row Field for Sub-grouping Overall Goal The task asks you to make "Territory" appear as a nested row inside each Region in a PivotTable. This creates a two-level hierarchy: Region → Territory, so you can see each territory's data grouped under its parent regi
Question
Explanation
PivotTable: Adding a Row Field for Sub-grouping
Overall Goal
The task asks you to make "Territory" appear as a nested row inside each Region in a PivotTable. This creates a two-level hierarchy: Region → Territory, so you can see each territory's data grouped under its parent region rather than as a flat list.
Why This Is the Correct Approach
PivotTables display hierarchical data by stacking row fields. When you add a second field below an existing row field, Excel automatically nests it, showing each unique value of the inner field only within its parent group. This is the standard way to drill down from a broad category (Region) to a narrower one (Territory) without writing any formulas.
Step-by-Step Procedure
1. Navigate to the "Regional Sales" worksheet and click anywhere inside the PivotTable.
This activates the PivotTable Analyze (or Options) tab and opens the PivotTable Fields pane. Clicking outside the PivotTable hides these tools entirely - nothing works without this step.
2. Locate the "Territory" field in the PivotTable Fields pane.
The field list shows all available columns from your data source. You must identify "Territory" here before you can place it.
3. Drag "Territory" into the Rows area, positioned below "Region".
Order matters critically. Fields in the Rows area are nested top-to-bottom: the first field is the outer group, the second is the inner group. If you drag Territory above Region, territories become the outer groups and regions nest inside them - the opposite of what's asked.
Alternatively: check the "Territory" checkbox (Excel usually places it in Rows automatically), then drag it into the correct position if needed.
4. Confirm the layout shows Region → Territory nesting.
The PivotTable should collapse to show each Region with its Territories indented beneath it. If it shows a flat list, Territory and Region may be in separate areas (e.g., Territory accidentally went to Columns or Filters).
What Goes Wrong If Steps Are Skipped
| Mistake | Result |
|---|---|
| Not clicking inside the PivotTable first | Fields pane doesn't appear |
| Placing Territory above Region in Rows | Hierarchy is inverted |
| Dragging Territory to Columns instead of Rows | Territories become column headers, not sub-rows |
| Dragging Territory to Filters | It becomes a report filter, not visible in the table body |
Memory Tip
Think of Rows as an outline structure: whatever is listed first is the outer heading, and everything below it indents underneath. Just like a document outline - Region is the chapter, Territory is the section.
Topics
Community Discussion
No community discussion yet for this question.