Charts & Visual Data
Bar of Pie & Pie of Pie Chart Formula: Tail-Slice Threshold, "Other" Wedge & Sub-Bar Share
Build a clean Bar-of-Pie or Pie-of-Pie breakdown table by aggregating micro-slices below a percentage threshold into a single "Other" main-pie wedge and computing both within-"Other" and grand-total shares for the secondary bar stack.
Prevent unreadable 1% pie-label collisions by splitting tail categories into a secondary bar!
Standard pie charts become illegible whenever 5–10 tail categories each hold < 5% of total revenue or expenses — slice labels overlap and 0.8% wedges vanish into thin lines! A Bar of Pie (or Pie of Pie) chart solves this two-stage composition problem: every major slice >= Split_Threshold_Pct stays in the primary pie alongside ONE consolidated "Other" wedge = SUMIF(Share_Range, "<" & Threshold, Amount_Range), while a connected secondary stacked bar expands the exact internal breakdown of that "Other" wedge!
To audit or construct a Bar-of-Pie helper table in any spreadsheet (including native pie/bar pairings), track two distinct percentages for every tail item i: its Grand-Total Share p_total,i = Amount_i / Grand_Total AND its Conditional Within-"Other" Bar Height p_bar,i = Amount_i / Other_Subtotal (which sums cleanly to 100.0% across the secondary bar!).
| Channel Category ($1,000,000 Total, 8% Split Cutoff) | Revenue ($) | Grand-Total Share | Chart Placement |
|---|---|---|---|
| Enterprise Direct | $460,000 | 46.00% | Primary Pie Slice #1 |
| Mid-Market SaaS | $280,000 | 28.00% | Primary Pie Slice #2 |
| Channel Partners | $140,000 | 14.00% | Primary Pie Slice #3 |
| Consolidated "Other" (< 8% each: Affiliates $55k + Events $40k + Print $25k) | $120,000 | 12.00% | Primary Pie "Other" Wedge -> Secondary Bar (45.8% / 33.3% / 20.8%) |
Consolidated "Other" Main-Pie Wedge Total Below Split Threshold
Sums every category in B2:B7 whose dollar value falls strictly below Split_Cutoff_Pct (B8, e.g., 0.08 for 8%) * SUM(B2:B7).
=IF(OR(SUM(B2:B7) <= 0, B8 <= 0, B8 >= 0.5), "Requires Positive Total and Split Cutoff in (0, 0.50)", ROUND(SUMIF(B2:B7, "<" & (B8 * SUM(B2:B7))), 2))Secondary Bar Conditional Share (%) of Selected Tail Slice Within "Other"
Computes 100 * B5 / Other_Subtotal for tail item Affiliates (B5) so secondary bar segments sum to 100% of the "Other" callout.
=IF(SUMIF(B2:B7, "<" & (B8 * SUM(B2:B7))) <= 0, 0, ROUND(100 * B5 / SUMIF(B2:B7, "<" & (B8 * SUM(B2:B7))), 2))Try the example
Use the original values or change them to test your understanding.