Free tool

Box and whisker plot maker

Paste a list of numbers. The sheet works out the five-number summary and the outlier fences, and draws the box plot from them.

fx=QUARTILE.INC(A2:A501,1)
ABC
1ValuesSummary
26Minimum6
37Q1 (25%)25.5
415Median40
536Q3 (75%)42.5
639Maximum49
740IQR17
841Lower fence0
942Upper fence68
1043Lower whisker6
1147Upper whisker49
1249Outliersnone
616.7527.538.2549 Q1 25.5median 40Q3 42.5

Blue cells are inputs: type a new value and the sheet recomputes. Click a result to see its formula.

The formulas

How it works, step by step

=QUARTILE.INC(A2:A501,1)
The five-number summary. A box plot is drawn from five numbers: the minimum, the first quartile (Q1), the median, the third quartile (Q3) and the maximum. QUARTILE.INC with 1 gives Q1 and with 3 gives Q3; MEDIAN, MIN and MAX give the rest. Empty cells in the range are ignored.
=C5-C3
The box. The box runs from Q1 to Q3, with a line at the median. Its length is the interquartile range (IQR): the spread of the middle half of the data. Here 42.5 − 25.5 = 17.
=C3-1.5*C7
The outlier fences. Points more than 1.5 × IQR below Q1 or above Q3 count as outliers (Tukey's rule). The fences here are 0 and 68, so no value in the sample is an outlier.
=MINIFS(A2:A501,A2:A501,">="&C8)
The whiskers. Each whisker reaches the most extreme value that is still inside its fence. Values beyond the fences are drawn as separate dots, and FILTER lists them.

Functions used here

QUARTILE.INCQuartile 0 to 4 of a data setQUARTILE.EXCQuartile 1 to 3, ends excludedMEDIANMiddle valueMINIFSSmallest value where all criteria matchMAXIFSLargest value where all criteria matchFILTERRows or columns that meet a conditionTEXTJOINJoin texts with a delimiter

More free tools

Percentage calculatorWork out a percent of a number, what percent one number is of another, and the percent change between two values, with the spreadsheet formula for each.Credit card interest calculatorSee the interest on a credit card balance this month, how many months your payment takes to clear it, and the total interest, with the formulas behind each number.Cubic feet calculatorCalculate cubic feet from length, width and height in feet or inches, convert to cubic yards and cubic meters, and get the volume of a cylinder, with the formulas.Find duplicates in a listPaste a list to flag every duplicate, count the distinct values and list the ones that repeat, with the COUNTIF and UNIQUE formulas that do it in any spreadsheet.Multiply columns and add them upMultiply quantity by price on each row, sum the column, and get the same total in one SUMPRODUCT formula.Profit margin calculatorCalculate gross profit margin and markup from cost and price, or the selling price for a target margin or markup, with the spreadsheet formula for each.CAGR calculatorWork out the compound annual growth rate between a start and end value over any number of years, check it with RRI, and project future values with the same rate.
FAQ

Questions

How do you make a box and whisker plot?

Sort out five numbers: minimum, Q1, median, Q3 and maximum. Draw a box from Q1 to Q3 with a line at the median, then whiskers out to the smallest and largest values that are not outliers. Paste your numbers above and the plot is drawn for you.

How do I find the quartiles?

Q1 is the median of the lower half of the data and Q3 the median of the upper half. In a spreadsheet, =QUARTILE.INC(A2:A12,1) and =QUARTILE.INC(A2:A12,3) return them. For 6, 7, 15, 36, 39, 40, 41, 42, 43, 47, 49 they are 25.5 and 42.5.

Why do different tools give different quartiles?

There are several accepted methods. QUARTILE.INC includes the median when splitting the data; QUARTILE.EXC leaves it out and gives slightly wider quartiles on small samples. Textbooks often use the exclusive method, so check which one your course expects.

How do I find outliers?

Work out the IQR (Q3 − Q1). Anything below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR is an outlier. In 1 to 9 plus 100, the upper fence is 14.5, so 100 is an outlier.

What does a box plot show?

Where the middle half of the values lies (the box), the median (the line), how far the rest spreads (the whiskers) and which values are unusual (the dots). It is a quick way to compare groups side by side.

Run these formulas on live data

Sheets is a spreadsheet with 491 functions that reads your store, accounting or project data live, builds dashboards and e-mails reports on a schedule.

See the integrationsAll free tools