Charts & Visual Data
Sunburst Chart Formula: Hierarchical Ring Angles (360°), Parent-Child Rollups & Gap Audit
Prepare and audit multi-level hierarchical data for a concentric Sunburst Chart by computing inner parent-ring rollups, outer leaf-ring 360-degree arc spans, and within-parent conditional shares.
Ensure outer-ring child arcs align 1:1 with inner-ring parent wedges!
A Sunburst (Multi-Level Radial Ring) Chart generalizes a doughnut chart across nested organizational, cost-center, or product-taxonomy hierarchies: Ring 1 (inner) displays Parent Divisions, Ring 2 displays Sub-Departments, and Ring 3 (outer) displays Leaf SKUs or Teams! Every wedge’s central angle theta_i (in degrees out of 360°) MUST equal 360 * Node_Value / Grand_Total so that the sum of child arc angles equals their parent’s arc angle down to the last tenth of a degree!
The #1 cause of broken or blank wedges in hierarchical Sunburst charts is mixing subtotal rows with leaf rows or leaving blank parent labels! Auditing your flat leaf table with SUMIF(Parent_Col, Target_Parent, Value_Col) computes exact inner-ring arc spans theta_parent = 360 * Parent_Sum / Grand_Total while verifying zero orphan rows (COUNTBLANK(Parent_Col) = 0) before charting!
| Hierarchy Node ($500,000 Budget) | Rollup Amount ($) | Radial Arc Span (deg) | Share of Parent |
|---|---|---|---|
| Parent Division: Cloud R&D (Inner Ring) | $300,000 | 216.00° | 60.0% of Total |
| ├─ Leaf: Core Engine | $180,000 | 129.60° | 60.0% of Cloud R&D |
| └─ Leaf: AI Platform | $120,000 | 86.40° | 40.0% of Cloud R&D (129.6° + 86.4° = 216.0°!) |
| Parent Division: Go-To-Market (Inner Ring) | $200,000 | 144.00° | 40.0% of Total |
Inner-Ring Parent Division Radial Arc Angle (Degrees out of 360°)
Rolls up leaf values C2:C5 matching Parent Division D2 ("Cloud_RD") in A2:A5 and scales by 360 / SUM(C2:C5).
=IF(OR(SUM(C2:C5) <= 0, COUNTBLANK(A2:A5) > 0), "Invalid Total or Orphan Blank Parent Row Detected", ROUND(360 * SUMIF(A2:A5, D2, C2:C5) / SUM(C2:C5), 2))Leaf Node Conditional Share (%) Within Its Parent Ring Segment
Divides Leaf Row 2 value C2 by its Parent Division subtotal SUMIF(A2:A5, A2, C2:C5).
=IF(SUMIF(A2:A5, A2, C2:C5) <= 0, 0, ROUND(100 * C2 / SUMIF(A2:A5, A2, C2:C5), 2))Try the example
Use the original values or change them to test your understanding.