Charts & Visual Data
Sales Funnel Chart Formula: Invisible Centering Spacer & Stage Drop-Off %
Construct a centered horizontal Sales Funnel on any stacked bar chart by computing the exact left-padding spacer (Top_Stage - Stage_i) / 2 alongside stage-to-stage conversion and leakage rates.
Center stacked horizontal bars symmetrically with the half-deficit spacer!
In versions or web workbooks without a dedicated Funnel chart object, a two-series Stacked Bar Chart reproduces an exact symmetrical funnel! Plot an invisible (no-fill) Left_Spacer series first and the Stage_Volume series second: for any funnel stage S_i below top-of-funnel width S_max = MAX($B$2:$B$5), the exact centering offset is Left_Spacer_i = (S_max - S_i) / 2, leaving an identical (S_max - S_i) / 2 margin on the right!
Pair the visual bars with two diagnostic ratios: Stage-to-Stage Conversion % = 100 * S_i / S_{i-1} (pinpointing the leakiest step) and Cumulative Top-to-Bottom Yield % = 100 * S_final / S_max.
| Funnel Stage | Leads (S_i) | Left Centering Spacer | Prior-Stage Pass % |
|---|---|---|---|
| 1. Landing Visitors | 4,000 | 0.0 | 100.00% |
| 2. Free Signups | 1,400 | 1,300.0 | 35.00% |
| 3. Activated Trials | 560 | 1,720.0 | 40.00% |
| 4. Paid Customers | 196 | 1,902.0 | 35.00% (4.90% overall) |
Invisible Left-Centering Bar Spacer for Stage i
Computes half the difference between top-of-funnel volume MAX(B2:B5) and current stage B3.
=IF(OR(MAX(B2:B5) <= 0, B3 < 0, B3 > MAX(B2:B5)), "Invalid Funnel Stage", ROUND((MAX(B2:B5) - B3) / 2, 1))Stage-to-Stage Conversion Rate (%) from Trials (B4) to Paid (B5)
Divides downstream stage volume B5 by immediately preceding stage B4.
=IF(OR(B4 <= 0, B5 < 0, B5 > B4), "Invalid Stage Pair", ROUND(100 * B5 / B4, 2))Try the example
Use the original values or change them to test your understanding.