Box-and-Whisker Plot Formula: Stacked Q1 Pedestal, Median Split & Whisker Lengths

Turn a 5-number summary into stacked box heights and asymmetric whisker lengths!

To build a portable Box-and-Whisker Chart across multi-category Stacked Columns (comparing latency, salary bands, or lab assay lots side by side), plot three stacked series per column: Series 1 = Q1 (formatted with No Fill / No Border as an invisible pedestal), Series 2 = Median - Q1 (Lower Interquartile Box from 25th to 50th percentile), and Series 3 = Q3 - Median (Upper Interquartile Box from 50th to 75th percentile, meeting Series 2 along the exact Median line)!

Whiskers extend via negative error bar Lower_Whisker = Q1 - MIN(range) anchored on Series 1 (reaching down to sample Minimum or lowest non-outlier >= Q1 - 1.5 * IQR) and positive error bar Upper_Whisker = MAX(range) - Q3 anchored on Series 3 (reaching up to sample Maximum or highest non-outlier <= Q3 + 1.5 * IQR)!

Total Interquartile Box Height IQR = Upper_Box + Lower_Box = Q3 - Q1

Evaluates QUARTILE.INC(B2:B10, 3) - QUARTILE.INC(B2:B10, 1) across at least 4 numeric observations in B2:B10.

=IF(COUNT(B2:B10) < 4, "Requires At Least 4 Numeric Observations", ROUND(QUARTILE.INC(B2:B10, 3) - QUARTILE.INC(B2:B10, 1), 2))

Upper Interquartile Box Segment (Q3 - Median) & Upper Whisker Span (MAX - Q3)

Computes the stacked upper-half box height above the median line, revealing right-tail skewness whenever (Q3 - Median) > (Median - Q1).

=IF(COUNT(B2:B10) < 4, "Requires At Least 4 Numeric Observations", ROUND(QUARTILE.INC(B2:B10, 3) - MEDIAN(B2:B10), 2))

Further reading

• NIST/SEMATECH — Box Plot Exploratory Data Analysis Reference