How to Make a Box Plot in
Google Sheets (2025 Guide)
Unlike Excel, Google Sheets does not have a built-in box-and-whisker plot. But don't worryβwe'll show you the "Candlestick Chart" hack to create one, plus a faster way to get professional results instantly.
π The "Too Long; Didn't Read" Version
Google Sheets requires complex data reformatting to fake a box plot using a Candlestick chart. The faster way? Paste your data into PlotNerd, generate the chart instantly, and copy the image into your Sheet.
π Create Box Plot Instantly with PlotNerd1. Method 1: The Candlestick Chart Hack
Since Google Sheets lacks a "Box and Whisker" chart type, we have to use a Candlestick Chart (normally used for stock prices) to mimic one. It requires a very specific data structure.
Step 1: Calculate the 5-Number Summary
You cannot just select raw data. You must calculate the five statistics first using these formulas:
| Statistic | Google Sheets Formula |
|---|---|
| Minimum | =MIN(A:A) |
| Q1 (First Quartile) | =QUARTILE.INC(A:A, 1) |
| Q3 (Third Quartile) | =QUARTILE.INC(A:A, 3) |
| Maximum | =MAX(A:A) |
Note: We skip the Median for now because Candlestick charts only accept 4 values (Open, High, Low, Close). This is a major limitationβyou won't see the median line inside the box!
Step 2: Arrange Data for Candlestick Chart
This is the tricky part. Candlestick charts expect data in this exact order columns:
- Label: Name of the group (e.g., "Class A")
- Low: Minimum value
- Open: Q1 (First Quartile)
- Close: Q3 (Third Quartile)
- High: Maximum value
Step 3: Insert the Chart
- Select the 5 columns you just created.
- Go to Insert > Chart.
- In the Chart Editor, change "Chart type" to Candlestick chart.
You now have a basic box plot! The "wicks" are the whiskers, and the "body" is the box (IQR).
2. Method 2: The PlotNerd Way (Faster & Better)
The Google Sheets method has flaws: no median line, no outlier dots, and complex setup. Use PlotNerd to generate a statistically accurate chart in seconds.
Why use PlotNerd?
- β Shows the Median line (Crucial!)
- β Automatically detects Outliers
- β Supports Tukey & R-7 algorithms
- β Free & Privacy-focused
1. Copy data from Sheets:
92
78
...
2. Paste into PlotNerd & Click Calculate
3. Right-click chart > "Copy Image"
4. Paste back into Google Sheets!
3. Limitations of Google Sheets Box Plots
No Median Line
The Candlestick chart uses Open/Close for the box edges, leaving no way to draw a line for the Median inside the box.
No Outliers
It cannot plot individual points for outliers. The whiskers just extend to Min/Max, hiding extreme values.
Complex Setup
Requires manual formula entry and specific column ordering. One mistake breaks the chart.
β Frequently Asked Questions
Q: Does Google Sheets have a box plot template?
A: No, Google Sheets does not have a native box plot template. You must use the Candlestick chart workaround or an external tool like PlotNerd.
Q: How do I show outliers in Google Sheets?
A: The native Candlestick method cannot show outliers. To show outliers, you need to use a dedicated statistical tool like PlotNerd and paste the result image into Sheets.