TL;dr: On October 22, Google added native box and whisker charts to Sheets, for all Workspace customers and personal Google accounts. You select a range of raw numbers and Sheets calculates the minimum, first quartile, median, third quartile, maximum, mean, and outliers, with no helper formulas needed. You can choose inclusive or exclusive quartile calculation, and show or hide inner points, outliers, mean markers, and mean lines. Charts imported from Excel are no longer dropped, and Sheets charts can be exported back to Excel as editable charts. Rapid Release rollout started October 22, with Scheduled Release starting November 6, so if you do not see the chart type yet, that is why.

Today, we will be looking at what shipped, how to build one step by step, how to read it in thirty seconds, and the two places where it beats reporting an average. This matters because finance practitioners live on averages that hide the real story. A budget category can average on target while swinging wildly month to month, and a portfolio can average a decent return while hiding a long tail of bad months. A box plot shows the spread, the middle, and the outliers in one picture, which is exactly what you need before deciding whether an average is safe to act on.

The problem with an average is easiest to see with two spending categories that both average about $400 a month. Groceries might run between $360 and $440 every month, a tight band you can plan around. Dining out might average the same $400 while swinging from $120 in a quiet month to $780 in a month with a trip and two celebrations. The average says these categories behave the same. The box plot says they do not, and it says it in one glance: a short box for groceries, a long box with a stray dot for dining out. One is a budget you can hold steady. The other needs a cap, or at least a conversation about what is driving the swings.

Setting up your box plot: first, lay out the data with one column per group and one row per observation. For monthly spending, that means the column headers are your categories, groceries, dining out, transport, and each row below is a month, twelve rows for a full year. Second, select the whole range including the headers. Third, go to Insert, then Chart, and in the chart editor pick the box and whisker type. Sheets builds the plot from the raw numbers, no QUARTILE or MEDIAN formulas required, which is the whole point: the chart is doing the statistics, and your sheet stays a plain table of observations that anyone can audit. Fourth, open the Customize tab and make two decisions. The quartile calculation setting is inclusive or exclusive, and in plain language the difference is whether the median value itself gets counted in both halves when Sheets splits the data. With a large sample the difference is small. With twelve monthly observations it can shift the box edges noticeably, so pick one method, use it everywhere, and write it on the sheet so the next person knows which one you used. The display toggles are show inner points, show outliers, show mean markers, and show mean lines. My default is outliers on, mean marker on, inner points off, because the mean marker sitting away from the median line is the fastest visual tell that a distribution is skewed.

How to read a box in thirty seconds: the box itself is the middle 50 percent of your observations, from the first quartile to the third quartile. The line inside the box is the median, the point where half the observations fall above and half below. The marker, usually a small shape or cross, is the mean. When the mean marker sits well away from the median line, the distribution is skewed, and the average is being pulled by a few large values. The whiskers extend to the furthest observations that are not outliers. The dots beyond the whiskers are the outliers, the months that do not belong with the rest, and those dots are usually the most interesting part of the chart, because an outlier month is almost always a real event rather than a data error.

Worked example one is the spending comparison above, and the template below has it ready to paste into, with twelve months of groceries, dining out, and transport already laid out so you can see the tight box next to the long one. Worked example two is monthly returns or department budget variances compared side by side. Take three departments and their monthly variance percentages for the year, and instead of reporting that each averaged within two percent of budget, plot the three boxes. One department might be consistently a point over, a tight box slightly above zero. Another might average the same while swinging from eight percent under to ten percent over, a long box that says the forecast process itself is the problem, not any single month. The average hides that distinction. The box plot makes it the first thing you see.

Three pitfalls, because a chart that looks authoritative can mislead faster than a table. First, small samples. A box built from four observations looks just as tidy as one built from forty, and it means almost nothing. Twelve monthly observations is a reasonable floor, and if you have fewer, say so on the sheet rather than letting the chart imply a confidence it does not have. Second, mixing quartile methods across files. If one workbook uses inclusive and another uses exclusive, the same data draws different boxes, and a comparison across the two will show differences that are not real. Standardize on one method and note it. Third, deleting outliers. It is tempting to drop the dot that ruins a clean chart, but an outlier month is usually a trip, a one-off purchase, a restatement, or a month where something genuinely broke. Investigate it before you exclude it, and if you do exclude it, write that on the sheet with the reason, because a chart with a silently deleted month is a chart nobody should trust.

One more thing worth knowing is how this sits next to the candlestick post from October 8. Candlesticks track a single series over time, which is the right tool for watching a price move month to month. A box plot compares distributions across groups, which is the right tool for asking whether groceries behave differently from dining out, or whether one department forecasts differently from another. Different job, same principle: show the shape of the numbers instead of collapsing them into one number and hoping the shape did not matter.

Start with one comparison you already suspect has a hidden spread, build the plot from twelve months of raw observations, and check whether the mean and the median agree before you quote the average to anyone. The template below has the spending and returns tabs laid out, a short note on the quartile setting, and room to add your own categories.

Template: template: Distribution comparison workbook (Google Sheets, make a copy to use it)

- Francois