Excel Box Plot Explained: Create and Read One

An Excel box plot, also called a box-and-whisker chart, summarizes how a set of numbers is distributed: its box marks the middle half of the data, the line in the box marks the median, and whiskers show the spread beyond the box. In Excel for Microsoft 365 on Windows, select your data and choose Insert > Insert Statistic Chart > Box and Whisker to create one.

What an Excel box plot shows

A box plot compresses a group of numeric observations into a visual summary. Instead of displaying every value as a separate bar, it highlights the center, spread and possible unusually distant values. This makes it useful for comparing groups, such as test scores across classes, delivery times by carrier or monthly measurements from different sites.

The main parts are:

Median: The line inside the box divides the ordered observations into lower and upper halves. Half the values are at or below it, and half are at or above it.
First quartile (Q1): The lower edge of the box. It marks the 25th percentile under the chart’s quartile calculation.
Third quartile (Q3): The upper edge of the box. It marks the 75th percentile.
Interquartile range (IQR): The distance from Q1 to Q3. It describes the spread of the middle 50% of observations.
Whiskers: Lines extending from the box to the most extreme values that are not classified as outliers by the chart’s rule.
Outlier points: Individual values beyond the whiskers, when the chart is set to show them.

The box’s length is meaningful: a taller box in a vertical chart, or a wider box in a horizontal chart, indicates a larger IQR. A median that sits off-center in the box can suggest an uneven distribution, though a box plot alone does not reveal every detail of the data’s shape.

How quartiles and whiskers work

To calculate quartiles, Excel orders the observations and finds values marking the lower quarter, midpoint and upper quarter. With an even number of observations, the median is typically between the two central values. With an odd number, the median is the central observation. Quartile calculations can differ slightly between statistical conventions, especially for small samples, so two tools may draw different boxes from the same data.

Excel’s Box and Whisker chart offers two choices under quartile calculation: Inclusive median and Exclusive median. Inclusive calculation includes the median when dividing an odd-sized set into lower and upper portions; exclusive calculation leaves it out. For an even-sized set, the two methods can also produce different quartile positions. When comparing charts, use the same method throughout and state it when the distinction matters.

Excel uses the familiar 1.5-times-IQR rule to identify potential outliers. The lower fence is Q1 minus 1.5 times the IQR, and the upper fence is Q3 plus 1.5 times the IQR. These fences are thresholds, not necessarily whisker endpoints: each whisker reaches the most extreme observed value still inside its fence. A point outside a fence is plotted separately when outlier points are displayed.

For example, consider the nine values 2, 3, 4, 5, 6, 7, 8, 9 and 20. With the inclusive method, Q1 is 4, the median is 6 and Q3 is 8. The IQR is 4, so the upper fence is 14. The value 20 is beyond that fence and appears as a potential outlier; the upper whisker reaches 9, the greatest observation still within the fence.

You can check those summary values with worksheet functions. With the nine measurements in A2:A10, =QUARTILE.INC(A2:A10,1) returns 4. With the same values in A2:A10, =MEDIAN(A2:A10) returns 6. With the same values in A2:A10, =QUARTILE.INC(A2:A10,3) returns 8. These formulas use the inclusive quartile convention; a chart set to exclusive calculation may not match the first and third results.

How to create a box plot in Excel

For the clearest setup, put each group in its own column, with a group name in the first row and observations underneath. For example, columns might be headed North, South and West, with one measurement per row. Groups do not need to contain the same number of observations; leave unused cells blank rather than filling them with zeroes.

Select the cells containing the group headings and numeric observations.
On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker.
Check that each box represents the intended group. If Excel has treated rows as series instead of columns, choose Chart Design > Switch Row/Column.
Add a descriptive chart title and, if useful, axis titles. Use Chart Design > Add Chart Element to find chart title and axis-title options.
Right-click a box and choose Format Data Series to adjust the quartile method and display options.

The exact ribbon wording can vary slightly between Excel builds and operating systems, but the chart is available in current desktop versions of Microsoft 365 and in Excel 2016 or later. Excel for Mac has a Box and Whisker chart as well, although ribbon placement and pane appearance may differ. If the statistical-chart option is missing in Excel for the web or an older installation, use a supported desktop version or another charting tool rather than assuming a standard bar chart is equivalent.

Formatting and reading the chart

Right-clicking a plotted series and opening Format Data Series reveals options that control how much detail appears. Depending on the chart and Excel build, you can show outlier points, inner points, mean markers or a mean line, and choose the quartile calculation. Inner points display observations within the whiskers, which can help readers see sample size and clustering when a group is not too large.

The mean and median answer different questions. The median is the midpoint of ordered values and is less affected by a very large or very small observation. The mean is the arithmetic average and can shift toward extreme values. If you turn on both mean and median indicators, label or explain them clearly so readers do not mistake one for the other.

Compare boxes by looking at both their centers and their spreads. A higher median indicates a higher typical value, while a larger IQR means the middle half of that group is more dispersed. Whisker lengths can show asymmetry in the values outside the box, and separate points identify observations that meet Excel’s outlier rule. A point flagged as an outlier is not automatically an error; it may be a valid rare event worth investigating.

Prepare data and avoid common problems

Box plots depend on meaningful numeric inputs. Before inserting one, check that the selected range contains observations rather than subtotals, averages or text labels mixed into the data. Keep headings in a single header row and ensure each column represents a clearly defined group measured on the same scale. A chart comparing temperatures in Celsius with temperatures in Fahrenheit, for example, would be misleading even if both columns contain numbers.

Do not use zero as a missing-value marker. Zero is a real observation and changes quartiles and the median. Leave unavailable measurements blank or handle them consistently before charting.
Check for accidental text-formatted numbers. A value stored as text may not be included as expected. Convert it to a number and confirm the chart updates.
Review unusually distant points. Verify the original record, units and data-entry process before deciding whether to remove anything. Keep valid extremes unless the analysis has a defensible reason to exclude them.
Check the category orientation. If the chart shows one box per row when you wanted one per column, switch rows and columns or rearrange the source range.
Use consistent scales when comparing separate charts. Different axis ranges can make similar distributions look very different.

A box plot can also hide sample-size differences. A box based on 12 observations and one based on 1,200 observations may look equally prominent, even though their evidential strength differs. Include the number of observations in labels, a nearby table or the accompanying text when that context matters. For small groups, individual points may be more informative than a summary alone.

Box plots compared with other charts

A box plot is strong at summarizing and comparing distributions, but it does not show every observation unless inner points are enabled. A histogram gives a more detailed view of distribution shape and can reveal clusters or gaps, but its appearance depends on the chosen bin widths. A dot plot or strip plot shows individual observations directly and can be easier to interpret for small datasets. A bar chart of averages, by contrast, can conceal variation and extreme values, so it is not a substitute when spread is central to the question.

Choose the chart that matches the reader’s task. Use a box plot when the goal is to compare medians, middle spreads and potential extremes across groups. Use a histogram to inspect one group’s shape, or show individual points when the dataset is small enough that the raw values remain legible. In a report, pairing a box plot with sample sizes or a brief explanation often provides more context than adding extra decoration.

When a box plot needs extra explanation

Box plots are compact, but some readers may not know what quartiles or outlier rules mean. Label the axes with units, identify each group clearly and explain the quartile method if the results will be compared with another source. If outliers are visible, say that they are flagged by a statistical rule rather than automatically removed or judged to be mistakes.

Also consider whether the data are comparable. Measurements collected at different times, under different conditions or from very different population sizes may need separate explanation. A box plot describes the observed values; it does not establish why groups differ, prove that a difference is important, or show uncertainty in the way a confidence interval might. Use it as a descriptive chart, then bring in the appropriate analysis for any broader conclusion.

Frequently Asked Questions

What does the box mean in an Excel box plot?
The box spans Q1 to Q3 and therefore contains the middle half of the observations under the selected quartile method. Its line marks the median.

What do the whiskers mean in Excel?
They extend to the most distant observed values that are not beyond Excel’s 1.5-IQR outlier fences. They do not necessarily reach the dataset’s absolute minimum and maximum.

Why does Excel show a dot outside the whiskers?
It is an observation classified as a potential outlier by the chart’s rule. Check the underlying value and its context; the marker alone does not show that the data are wrong.

Can an Excel box plot compare several groups?
Yes. Arrange each group in a separate column with a heading, select the full range and insert a Box and Whisker chart. Confirm that Excel assigns one box to each group.

Why do my Excel quartiles differ from another program?
Programs can use different quartile conventions, and Excel lets you choose inclusive or exclusive median calculation. Check the method and use the same convention when making comparisons.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.