Make a Box and Whisker Chart in Google Sheets

To make a box and whisker chart in Google Sheets, calculate each team's quartiles and extremes, then plot them as a candlestick chart. It shows differences an average can hide, but it does not display a median line or separate outlier dots.

A candlestick chart is the closest built-in option

Google Sheets can show a box-like comparison with a candlestick chart, but the result is not a full box and whisker plot. A candlestick chart takes a low value, two middle values and a high value for each category. You can supply the minimum, lower quartile, upper quartile and maximum survey scores in that order. The resulting shape makes each team's middle spread and full range easier to compare.

The distinction matters when you present the chart. A conventional box plot marks the median inside the box and may draw unusual observations as separate dots. The Sheets candlestick chart does neither when you use this method. Keep the median in a nearby summary table, and do not call an isolated low or high score an outlier based on the chart alone.

For a quick discussion about whether teams have similar spreads, this spreadsheet chart is often enough. If your report must show a median line and individual outlier marks in the same plot, use a charting tool that explicitly supports box plots instead. If you need to inspect every response interactively, an interactive chart from spreadsheet data is another route, provided you check what its chart actually displays.

Survey scores need one column per team

Put each team's numeric survey scores in its own column, with a team name at the top. For a worked example, place Team A in column B and Team B in column C, with scores starting beneath the headings. Each row is simply a plotting position: a score in Team A's row does not need to come from the same person as the score beside it in Team B's row.

Imagine that both teams have a similar average. Team A's responses cluster near the middle of the scale, while Team B has a wider mix of low and high responses. An average alone could make the teams look alike. Their quartiles and ranges would show that Team B's experience is less consistent. These are hypothetical patterns, not findings from a survey.

Check the source data before calculating anything. Keep one response per cell, use the same scoring scale for every team, and remove headings or comments from the score ranges. Blank cells are fine; text that looks like a score may not be counted as a number. Record how many numeric responses each team has, since a small group deserves more cautious interpretation. If team names come from a maintained list, a dropdown that grows with your list can help keep future entries consistent.

The summary table supplies the chart's shape

Build a separate summary table with columns for team, low, lower quartile, upper quartile and high, in exactly that order. For Team A's scores in column B, calculate the low with =MIN(B2:B), the lower quartile with =QUARTILE(B2:B,1), the upper quartile with =QUARTILE(B2:B,3), and the high with =MAX(B2:B). Use column C in the corresponding formulas for Team B. Put the team names in the first column of the summary table.

Add median and response count in columns beside the chart inputs. For Team A, use =MEDIAN(B2:B) and =COUNT(B2:B). Change B to C for Team B. These values help you interpret the result, but leave them out of the range selected for the candlestick chart. Including extra columns can cause Sheets to assign values to the wrong parts of the chart.

In the worked example, look first at whether Team B's lower and upper quartiles sit farther apart than Team A's. That gap describes the spread of the middle responses, even if the averages are similar. Then check the minimum and maximum for unusually wide ranges. A wide range may reflect genuine differences, a data entry mistake or a small number of extreme answers; the summary cannot tell you which.

The candlestick chart compares the team distributions

Select only the five-column summary table, including its headings and team rows. In Google Sheets, choose Insert, then Chart, and select Candlestick chart in the chart editor. Check that the team names identify the categories and that the value columns run from low through lower quartile, upper quartile and high. If the chart looks wrong, inspect the selected range and column order before changing its appearance.

Read each shape as a compact comparison, not as a stock price. The middle shape spans the lower and upper quartiles you supplied; the outer limits represent your minimum and maximum. Compare the teams on the same vertical score scale. In the hypothetical survey, Team B should have a taller middle span than Team A if its responses are more dispersed, despite their similar averages.

Give the chart a title that says what the scores measure, and keep the median and response count table visible beside it. Avoid presenting the candlestick's middle join as a median line: it is not one. Google says Sheets offers candlestick charts with low, open, close and high values. Using quartiles for the middle values is a practical adaptation of that chart type, so explain the adaptation to anyone who will reuse or share the chart.

A companion scatter chart shows individual extremes

Use a separate scatter chart when readers need to see individual survey scores, including possible outliers. The candlestick chart uses each team's minimum and maximum as its outer limits, so an extreme response changes the range rather than appearing as a separate dot. Its shape alone cannot show how many people gave an extreme score.

To make the companion chart, add a numeric plotting-position column beside the original team score columns. Fill it down with consecutive positions, then select that column and both team score columns. Choose Insert, Chart and Scatter chart. In the chart editor, make the plotting position the horizontal axis and the team score columns separate series. The horizontal position is only a way to spread the dots out; it does not match respondents across teams or represent time. Leave missing responses blank rather than inventing values to make the columns even.

Show the scatter chart beside the candlestick chart. A lone score far from its team's other dots is worth checking, but visual distance is not an agreed statistical outlier rule. Decide and document your outlier rule before excluding any responses. If responses feed an ongoing report, an alert when a Sheets column changes can prompt someone to review new entries; it does not validate the scores for you.

The comparison is strongest when the chart and table agree

Use the candlestick chart to spot differences in spread, then use the summary table and original responses to explain them. In the hypothetical Team A and Team B survey, similar averages would not justify saying the teams had similar experiences. A wider middle span for Team B would indicate more variation among its middle responses. Check its median to see where its center lies, and inspect individual scores before interpreting the outer limits.

Be careful with unequal response counts. A team's minimum and maximum depend partly on which responses happened to be collected, and a small group can produce an unstable-looking summary. Display the counts and describe the chart as a view of the responses received, not proof that one team's underlying experience is more variable. Do not remove an extreme score simply to make the chart look tidy.

For a one-off comparison, formulas and a built-in spreadsheet chart are simpler than automation. If the survey is repeated, keep the raw responses separate from the summary and check that every new score is numeric and on the same scale. If you collect sensitive feedback, decide who may see the underlying responses before sharing the chart or connecting other tools. A chart can hide individual rows visually without making its source sheet private.

Sources consulted

• Google Sheets Help (support.google.com)

Start a free course