You might want to see how spread out a column of numbers is. Google Sheets got a real box and whisker chart for this on 22 October 2026. You select a column of numbers, and Sheets works out the minimum, the quartiles, the median, the maximum, the mean and the outliers by itself. It also asks you to pick inclusive or exclusive quartiles. On a short list, this one choice changes the size of the box and decides which values are drawn as outliers.
Last updated 28 October 2026 · by Inam Ul Haq, data analyst and automation engineer · about the author
Until last week, you had to fake a box plot in Google Sheets if you wanted one. People usually used a candlestick chart fed by a helper table, and you had to work out every quartile yourself. Now there is a proper chart type, and it reads your raw numbers directly. Most search results still describe the old trick because the new chart is only a few days old.
The new chart has one setting the old trick never explained. This is the quartile method. Google’s announcement says you can choose inclusive or exclusive, but it does not say how the two differ or which one is the default. So I worked through both on the same small set of numbers, and the result is why I am writing this.
Pick one quartile method, check the box with QUARTILE.INC or QUARTILE.EXC before you share the chart, and if the file will be opened in Excel, use exclusive so both programs draw the same box.
Same eleven numbers, two methods, and one order changes from outlier to normal.
Does Google Sheets have a box and whisker chart now?
Google announced this on its Workspace Updates blog on 22 October 2026. You select a range of numbers, and Sheets calculates the minimum, the first quartile, the median, the third quartile, the maximum, the mean and the outliers with no helper formulas. You can choose to show or hide the inner points, the outliers, the mean markers and the mean line.
The feature reaches accounts gradually. Rapid Release domains started getting it on 22 October 2026, and Scheduled Release domains start on 6 November 2026. Google says it can take up to 15 days to show up in either case. It is available for every Google Workspace customer and for personal Google accounts, and there is no admin switch to turn it off.
The same post fixes an old annoyance with Excel files. Before this, a box and whisker chart in an Excel file was simply lost when you opened the file in Sheets. Google says the chart is now kept with its data references, calculations and formatting. A chart made in Sheets also goes back to Excel as an editable box and whisker chart.
I wrote this on 28 October 2026 based on Google’s post and the function help pages, and I worked the numbers below out myself. I have not seen the chart on my own account yet. If your Chart type list does not show it, give the rollout a few more days.
What does a box and whisker chart actually show?
A box and whisker chart squeezes a whole column of numbers into one shape so you can see how spread out they are. Think of the delivery times for a month of orders. The average gives you one number, but it does not tell you if most orders arrive in four or five days while a few take two weeks. It also does not tell you if your times are just all over the place.
The box holds the middle half of your values. Its left edge is the first quartile, or Q1, which is a quarter of the way up your sorted list, and its right edge is the third quartile, or Q3, which is three quarters of the way up. The line inside is the median, or the middle value. The whiskers reach out to the smallest and largest values that are still considered normal, and anything further out is drawn as a separate dot called an outlier.
The important part is how an outlier gets decided. Excel’s chart uses a cut off of one and a half box heights past each end of the box, and the box height is Q3 minus Q1. Microsoft’s own help page does not print that rule, but Ken Puls rebuilt Excel’s chart from formulas in Proving Excel’s Box and Whisker Chart Calculations and the outliers match it exactly. This means the box size does not just change how the chart looks. It also moves the line that decides which of your orders get called unusual.
What is the difference between inclusive and exclusive quartiles?
There is more than one accepted way to find the point a quarter of the way up a list. The difference is in how the middle value is treated when the list has an odd number of values. Microsoft’s Create a box and whisker chart page puts it simply. With inclusive median, the median is included in the calculation when the count is odd, and with exclusive median it is left out.
These two methods match two functions that both Sheets and Excel have. QUARTILE.INC gives you the inclusive answer, and QUARTILE.EXC gives you the exclusive one. Ken Puls showed that Excel’s chart draws the box at exactly the QUARTILE.EXC values when left at its default.
I will show you a small example. Say eleven orders took 2, 3, 3, 4, 4, 5, 5, 6, 7, 9 and 12 days to arrive. Both methods agree the median is 5 days, but they disagree on the edges of the box. This difference is enough to change the verdict on the slowest order.
| Inclusive (QUARTILE.INC) | Exclusive (QUARTILE.EXC) | |
|---|---|---|
| Q1, left edge of the box | 3.5 days | 3 days |
| Median | 5 days | 5 days |
| Q3, right edge of the box | 6.5 days | 7 days |
| Box height, Q3 minus Q1 | 3 days | 4 days |
| Upper cut off | 11 days | 13 days |
| The 12 day order is | an outlier | normal, the end of the whisker |
Picture showing this chart to a client. With inclusive quartiles, the 12 day order sits alone as a dot, and the first question in the meeting is what went wrong with it. With exclusive quartiles, the same order is just the end of the whisker and nobody asks about it. Neither chart is wrong, but they tell two different stories about the same eleven orders.
This gap shrinks as the list gets longer. I ran the same two methods on 41 values, which were the numbers 1 to 40 plus one 60. Q1 came out at 11 and 10.5, and neither method called the 60 an outlier. So on a few hundred rows you will hardly see the difference, but on a dozen rows it can flip a result.
Should I pick inclusive or exclusive in Google Sheets?
There is no single right answer, so I decide based on where the chart is going and what it needs to agree with. This is the table I use to choose.
| Your situation | Pick | Why |
|---|---|---|
| The file goes back and forth with Excel users | Exclusive | Excel draws its box with QUARTILE.EXC unless someone changed it |
| Your sheet already uses QUARTILE or QUARTILE.INC | Inclusive | The chart then agrees with your own numbers |
| A report that must match an earlier one | Whatever the earlier one used | Changing method changes the outliers |
| Hundreds of rows | Either, but write it down | The difference becomes small |
| A dozen rows shown to a client | Exclusive, and say so | Fewer points flagged for a small sample |
The second row catches people. In Excel, the older plain QUARTILE function gives the same answers as QUARTILE.INC. Google’s help page for QUARTILE in Sheets does not say which way it works. So if you built a summary table with QUARTILE last year and now draw the new chart in exclusive mode, your table and your chart can show two different boxes for the same column, so I suggest you put QUARTILE and QUARTILE.INC side by side on your own column once.
The last row is my own preference and not a strict rule. Every value has a big effect on the quartiles in a small sample, and the inclusive method flags more points. I would rather not send a client a chart that makes one ordinary order look like a problem. Whichever method you pick, put a short note under the chart that says which one it is.
How do I check the box and whisker chart with formulas?
A chart that draws without an error is not proof that it drew what you meant. I treat it the same way I treat a total that an AI tool gives me, and I explain that in how to check the numbers an AI put in your spreadsheet. Four formulas next to the data are enough, and they work the same way in both Sheets and Excel.
- 01Work out the two quartilesWith the values in A2:A12, type =QUARTILE.EXC(A2:A12,1) and =QUARTILE.EXC(A2:A12,3). Use QUARTILE.INC instead if the chart is set to inclusive.
- 02Work out the box heightSubtract the first result from the second. That is Q3 minus Q1, the height of the box.
- 03Work out the two cut offsUpper cut off is Q3 plus 1.5 times the box height. Lower cut off is Q1 minus 1.5 times the box height.
- 04Count the values outside themUse =COUNTIF(A2:A12,">"&upper) and =COUNTIF(A2:A12,"<"&lower), where upper and lower are the cells holding your cut offs. The total should match the number of outlier dots on the chart.
If the counts match, you know which method the chart is using and you can trust the dots. If they do not match, switch the quartile setting on the chart and run it again. For the whisker ends, the formula =MAXIFS(A2:A12,A2:A12,"<="&upper) gives you the top of the upper whisker, and this is the largest value that is still inside the cut off.
What happens when I open the chart in Excel?
Google says a box and whisker chart made in Sheets exports to Excel as an editable box and whisker chart, and an Excel one now comes into Sheets with its calculations kept. I have not taken a file through that round trip myself yet. So after any move between the two programs, I would open the chart settings and look at the quartile method before trusting it.
In Excel, that setting is on the right click menu of a box. Choose Format Data Series and then Series Options, and the Quartile Calculation choice shows Inclusive median or Exclusive median. Google’s post does not say where the choice sits in Sheets, so you should look for it in the chart editor next to the other box and whisker options. If both programs show the same method, they will draw the same box. Google made the same kind of round trip fix for pivot tables six days later, and I went through what that changes in compact form pivot tables in Google Sheets. Stock charts got the same Excel fix in October, and their new column order is a trap of its own, which I explain in the Google Sheets stock chart and its column order.
One more thing is worth doing before you send the file. A box plot is good at showing spread, but it hides how many values sit behind it, so a box drawn from eleven orders looks just as solid as one drawn from eleven thousand. I write the count next to the chart title, because a chart should be proof under a plain sentence, as I describe in how to turn a table of data into a story people follow. If your sheet is set to manual recalculation, press recalculate before you trust the chart at all, as I explain in Google Sheets manual calculation.
Google Sheets now draws a proper box and whisker chart from raw numbers, and this rolls out from 22 October 2026 to every account. It asks for inclusive or exclusive quartiles. On a small list, that choice can move the box and turn a normal value into an outlier. On the eleven example orders, the slowest one was an outlier with inclusive quartiles and just the end of the whisker with exclusive ones.
Pick one method and write it under the chart. Check the box with QUARTILE.EXC or QUARTILE.INC and COUNTIF before you share it, and use exclusive when the file is going to Excel users.
Sources: Google Workspace Updates, Create box and whisker charts in Google Sheets (22 October 2026); Google Docs Editors Help, QUARTILE.EXC and QUARTILE.INC; Microsoft Support, Create a box and whisker chart; Ken Puls, Proving Excel’s Box and Whisker Chart Calculations (Excelguru). All read on 28 October 2026. The delivery times are an example, and the quartiles were worked out for this article.