Google Sheets box plot formula builder

Google Sheets has a real Box and whisker chart now (Insert → Chart → Chart type → Statistical). It reads one layout: a column of group names next to a column of numbers, one row per number. If your table has one column per group, side by side, the chart takes the first column as labels and draws nonsense. One formula stacks the columns into the right shape. This page writes it for your range.

Your table
Optional: check it on your data

The formula from the video composed on the page, no account, nothing stored 

=QUERY(ARRAYFORMULA(SPLIT(FLATTEN(A1:C1&"|"&A2:C13),"|")),"where Col2 is not null")

Then make the chart

  1. Type Class in E1 and Score in F1. The formula's output has no header row of its own, and the chart needs one.
  2. Click E2 and paste the formula. It fills two columns, up to 36 rows.
  3. Select E1:F37, then Insert → Chart.
  4. In the Chart editor, open Chart type and pick Box and whisker chart, under Statistical.

Reading the formula

  1. A1:C1&"|"&A2:C13 glues each value to its column's header, so every number carries its group's name, like "Class A|72".
  2. FLATTEN turns that grid into one long column, reading across each row before moving down.
  3. SPLIT cuts each entry at the | back into two cells — the group, and the number, which comes back as a real number.
  4. ARRAYFORMULA lets the whole thing work on the range at once.
  5. QUERY(…, "where Col2 is not null") drops the rows left by blank cells, so columns of different lengths are fine.

Reading the box plot

Sheets draws one box per group and works out the numbers itself. The line across a box is the median: half the values are above it. The box holds the middle half. The whiskers reach out to the rest, and a value far enough outside them is drawn as a separate dot, an outlier, with the whisker stopping before it. The X is the average. When one outlier drags the X away from the line, the median is the number to trust.

Under Customize → Series you can change the quartile calculation (it starts on Exclusive Median) and switch the outlier dots, the mean markers, a mean line and the inner points on or off.

Seeing it done

The channel makes one on camera in How to Make a Box Plot in Google Sheets.