How to Create a Box Plot in Google Sheets

Key Insights

Google Sheets lacks native box plot support, but you can build accurate box plots using candlestick charts with a helper table that calculates the five-number summary
The QUARTILE function is your workhorse—use it with parameters 1 and 3 to extract Q1 and Q3, then derive all other box plot segments through subtraction
Outlier detection requires a separate calculation using the 1.5×IQR rule, displayed as an overlaid scatter series on your chart

Introduction to Box Plots

Box plots (also called box-and-whisker plots) pack an enormous amount of statistical information into a compact visual. They show you the median, spread, skewness, and outliers of a dataset at a glance—something histograms and bar charts struggle to communicate efficiently.

A box plot consists of five key components: the minimum value, first quartile (Q1), median (Q2), third quartile (Q3), and maximum value. The “box” spans from Q1 to Q3, representing the interquartile range (IQR) where 50% of your data lives. The “whiskers” extend to the minimum and maximum values, and any points beyond 1.5×IQR from the quartiles are flagged as outliers.

Data analysts use box plots to compare distributions across categories, spot outliers quickly, and understand data spread without getting lost in individual data points. Unfortunately, Google Sheets doesn’t offer a native box plot chart type. You’ll need to build one manually using a candlestick chart workaround.

Preparing Your Data

Before calculating anything, your raw data needs to be in a single column. Box plots analyze one-dimensional distributions, so each observation should occupy its own row.

Here’s a sample dataset representing exam scores for 20 students:

A1: Score
A2: 45
A3: 52
A4: 58
A5: 62
A6: 65
A7: 67
A8: 70
A9: 72
A10: 73
A11: 75
A12: 76
A13: 78
A14: 80
A15: 82
A16: 85
A17: 88
A18: 91
A19: 94
A20: 98
A21: 142

Notice that last value—142 is suspiciously high and likely an outlier. A good box plot will flag this automatically.

Clean your data before proceeding. Remove blank cells, ensure all values are numeric (not text formatted as numbers), and check for data entry errors. Use =ISNUMBER(A2) to verify cells contain actual numbers if you’re unsure.

If you’re comparing multiple groups (like exam scores across three classes), place each group in its own column. You’ll create separate box plot calculations for each.

Calculating Box Plot Statistics

The five-number summary forms the backbone of your box plot. Google Sheets provides built-in functions for each component.

Create a summary table starting in column C:

C1: Statistic       D1: Value
C2: Minimum         D2: =MIN(A2:A21)
C3: Q1              D3: =QUARTILE(A2:A21, 1)
C4: Median          D4: =MEDIAN(A2:A21)
C5: Q3              D5: =QUARTILE(A2:A21, 3)
C6: Maximum         D6: =MAX(A2:A21)
C7: IQR             D7: =D5-D3

For the sample data, these formulas return:

Minimum: 45
Q1: 66
Median: 75.5
Q3: 86.5
Maximum: 142
IQR: 20.5

The QUARTILE function accepts a second parameter: 0 for minimum, 1 for Q1, 2 for median, 3 for Q3, and 4 for maximum. Using QUARTILE(range, 2) is equivalent to MEDIAN(range).

A note on quartile calculation methods: Google Sheets uses the inclusive method (Method 1), which may produce slightly different results than Excel’s QUARTILE.EXC function. For most practical purposes, this difference is negligible.

Building the Box Plot Using a Candlestick Chart

Here’s where the workaround comes in. Google Sheets’ candlestick chart expects four values: low, open, close, and high. We’ll map these to our box plot components, then style the chart to look like a proper box plot.

First, create a helper table that transforms your five-number summary into the format candlestick charts expect:

F1: Category    G1: Low     H1: Open    I1: Close   J1: High
F2: Scores      G2: =D2     H2: =D3     I2: =D5     J2: =D6

This maps:

Low → Minimum (bottom whisker)
Open → Q1 (bottom of box)
Close → Q3 (top of box)
High → Maximum (top whisker)

Now create the chart:

Select the range F1:J2
Go to Insert → Chart
In the Chart Editor, change Chart type to “Candlestick chart”
The chart appears with your box plot structure

But wait—where’s the median line? Candlestick charts don’t natively show a median. You’ll add this through customization in the next section.

For multiple datasets (comparing three classes), extend your helper table:

F1: Category    G1: Low     H1: Open    I1: Close   J1: High
F2: Class A     G2: =MIN(A2:A21)    H2: =QUARTILE(A2:A21,1)    I2: =QUARTILE(A2:A21,3)    J2: =MAX(A2:A21)
F3: Class B     G3: =MIN(B2:B21)    H3: =QUARTILE(B2:B21,1)    I3: =QUARTILE(B2:B21,3)    J3: =MAX(B2:B21)
F4: Class C     G4: =MIN(C2:C21)    H4: =QUARTILE(C2:C21,1)    I4: =QUARTILE(C2:C21,3)    J4: =MAX(C2:C21)

Customizing Your Box Plot

The default candlestick chart looks nothing like a traditional box plot. Here’s how to fix that.

Change the colors: Click on the chart, open the Chart Editor, and navigate to Customize → Series. Set both the “Fill color” and “Stroke color” to the same value—typically a muted blue or gray. This eliminates the red/green candlestick styling.

Adjust the box width: Under Series settings, increase the “Line width” to make whiskers more visible. Unfortunately, Sheets doesn’t let you adjust box width directly.

Add the median line: This requires a workaround. Create a combo chart by adding a second series:

K1: Median
K2: =D4

Add this as a new series to your chart, then change its chart type to “Line” in the Customize → Series dropdown. Position it to overlay the box at the median value. Alternatively, add a horizontal line annotation if precision matters less than simplicity.

Format the axes: Under Customize → Vertical axis, set explicit minimum and maximum values that give your data room to breathe. For exam scores, try 0 to 150.

Remove the legend: If you’re only showing one dataset, the legend adds clutter. Disable it under Customize → Legend → Position → None.

Identifying and Displaying Outliers

A box plot without outlier detection is incomplete. The standard rule flags any point more than 1.5×IQR below Q1 or above Q3 as an outlier.

Add outlier boundary calculations to your summary table:

C8: Lower Bound     D8: =D3 - 1.5*D7
C9: Upper Bound     D9: =D5 + 1.5*D7

For our sample data:

Lower Bound: 66 - 1.5×20.5 = 35.25
Upper Bound: 86.5 + 1.5×20.5 = 117.25

Any value below 35.25 or above 117.25 is an outlier. Our suspicious 142 score clearly qualifies.

To display outliers, extract them into a separate range:

L1: Outliers
L2: =FILTER(A2:A21, (A2:A21 < D8) + (A2:A21 > D9))

This FILTER formula returns all values outside the bounds. Add this as a scatter series overlaid on your candlestick chart. In the Chart Editor, add the outlier range as a new series and change its chart type to “Scatter.”

Now modify your whiskers to stop at the outlier boundaries instead of the true min/max:

G2: =MAX(D8, D2)    // Lower whisker stops at bound or min, whichever is higher
J2: =MIN(D9, D6)    // Upper whisker stops at bound or max, whichever is lower

This gives you a statistically correct box plot where whiskers extend only to the last non-outlier values.

Conclusion and Alternatives

Building box plots in Google Sheets works, but it’s tedious. The candlestick chart workaround requires manual helper tables, doesn’t show medians natively, and struggles with outlier display. For one-off analyses, this approach suffices. For repeated use, consider alternatives.

Google Apps Script can automate the entire process. Write a custom function that accepts a data range and generates the helper table automatically, then triggers chart creation programmatically.

Third-party add-ons like “Advanced Charts” or “ChartExpo” offer native box plot support with proper median lines and outlier detection built in.

Export to specialized tools when precision matters. Copy your data to Python with Matplotlib, R with ggplot2, or even Excel (which added native box plots in 2016). These tools handle edge cases, multiple calculation methods, and publication-quality formatting that Sheets simply can’t match.

For quick data exploration, the Sheets workaround delivers. For anything you’ll present to stakeholders or publish, invest the extra minutes in a proper statistical tool.