Charts & Visual Data
Break Chart Axis Formula: Piecewise Outlier Compression & Visual Gap Scaling
Prevent a single 10x outlier bar from crushing normal data series to unreadable slivers by building a piecewise Broken Y-Axis helper series with explicit lower-band fidelity, break-gap buffer, and compressed upper-band slope.
Display $12k–$45k regional branches AND a $480k headquarters outlier clearly without hiding data!
When 9 regional offices generate $12,000–$48,000 and Headquarters generates $480,000, a linear Y-axis compresses all 9 regional differences into the bottom 10% of the chart, while hard-clipping the Y-axis at $60,000 hides the magnitude of outliers if multiple spikes exist ($320k vs $480k look identical when clipped)! Spreadsheets lack a one-click "broken Y-axis" toggle because deceptive un-annotated breaks distort visual ratios — the transparent analytical solution is a Piecewise Linear Axis-Break Transform accompanied by a visible break-band marker and true-value data labels!
Choose Lower Uncompressed Ceiling B_low (e.g., 60,000), Visual Break Buffer Gap G (e.g., 5,000), Upper Resume Threshold B_high (e.g., 300,000), and Upper Compression Factor k (e.g., 0.10 so every $10 above $300k advances $1 on the plotted axis): any normal value x <= B_low plots untouched at y = x, while any extreme outlier x >= B_high plots at y = B_low + G + k * (x - B_high), preserving exact rank and spacing among multiple outliers!
| Series Observation | True Raw Value ($) | Piecewise Formula Branch (B_low=60k, Gap=5k, B_high=300k, k=0.10) | Plotted Height |
|---|---|---|---|
| Austin Branch (Normal) | $42,500 | x <= 60,000 -> Uncompressed x | 42,500 |
| Denver Branch (Normal) | $54,000 | x <= 60,000 -> Uncompressed x | 54,000 |
| Flagship West Outlier | $350,000 | 60,000 + 5,000 + 0.10 * (350,000 - 300,000) | 70,000 |
| Headquarters Outlier | $480,000 | 60,000 + 5,000 + 0.10 * (480,000 - 300,000) | 83,000 |
Piecewise Broken-Axis Plotted Coordinate Formula
Leaves values <= B_low (B3) 1:1 uncompressed, flags values falling inside the excised dead-zone (B_low, B_high) so no point vanishes into the break slash, and compresses outliers >= B_high (B5) by slope k (B6) above B_low + Gap (B4).
=IF(OR(B3 <= 0, B4 < 0, B5 <= B3, B6 <= 0, B6 >= 1), "Invalid Break Thresholds or Compression Slope k", IF(B2 <= B3, ROUND(B2, 2), IF(B2 < B5, "Value Inside Excised Break Zone — Lower B_high or Raise B_low", ROUND(B3 + B4 + B6 * (B2 - B5), 2))))Visual Space Saved (%) Relative to Unbroken Full-Scale Axis
Quantifies how much empty mid-range vertical whitespace is eliminated: 100 * (1 - Plotted_Max / Raw_Max).
=IF(OR(B2 <= B5, B5 <= B3, B6 <= 0, B6 >= 1), 0, ROUND(100 * (1 - (B3 + B4 + B6 * (B2 - B5)) / B2), 2))Try the example
Use the original values or change them to test your understanding.