Build a category hierarchy

Trace original records and weighted subtotals, then practise on the same spreadsheet. Your data stays in this browser.

Teaching · read-only · Report!A1

Step 1 / 6Read the original record grain

Inspect

Each amount belongs to one original observation. A parent and its descendants can contain the same records. Teaching is read-only; switch to Your turn to edit.

Hierarchy!F2 · (blank)
No formula applied

Preparing the actual category hierarchy worksheet…

Correct source/controls or explicitly restore edited calculation helpers. Previous valid results do not certify edited cells.

Copy this working source and calculation

Original source and field arrangementReset source and choicesRow levelsRow field 1Row field 2Row field 3Column fieldMeasureShow parent subtotals

Replace the complete source from CSV or TSV

Use Record, Month (YYYY-MM), Region, Product, Status and Amount headings, with one to fifty complete records. Replacement clears folded paths. Direct source Amount edits keep still-valid paths.

Report layout and totalsLayoutParent subtotal positionRepeat parent labels in tabular rowsShow right grand-total columnShow bottom grand-total row

Tabular subtotals stay below children. Layout changes retain branch choices and original observations. Group-label rows without a subtotal have no value to inspect.

Observed parent paths

Inspect exact values, original source rows and a report query

Parent totals overlap their children. Select a value to inspect its original records, including numeric zeroes, refunds and empty intersections.

Region	Product	Status
East	Cedar	Paid
East	Cedar Total
East	Lumen	Paid
East	Lumen	Pending
East	Lumen Total
East Total
North	Cedar	Paid
North	Cedar Total
North	Harbor	Paid
North	Harbor	Pending
North	Harbor Total
North	Lumen	Paid
North	Lumen Total
North Total
West	Cedar	Paid
West	Cedar Total
West	Lumen	Paid
West	Lumen Total
West Total
Grand total

Retrieve a visible hierarchy value

GETPIVOTDATA reads this current visible report. A hidden descendant or missing subtotal is unavailable. Include parent fields when a child label appears in several branches. Empty average intersections return zero under this query convention.

GETPIVOTDATA formula

Formula and fixed-value files retain the exact displayed paths and all source records. Native exports contain real PivotTable fields; native recalculation, refresh and edit/save are separate checks.