Excel Box and Whisker Plot Outliers: How They Work

In an Excel box-and-whisker chart, an outlier is a data point below Q1 − 1.5 times the interquartile range or above Q3 + 1.5 times the interquartile range. Excel displays those values as individual points beyond the whiskers; the whiskers usually reach the most extreme values that are not outliers.

What an outlier means in a box-and-whisker plot

A box-and-whisker plot summarizes the distribution of numeric data. The line inside the box marks the median, while the bottom and top of the box mark the first and third quartiles, Q1 and Q3. The interquartile range, or IQR, is the distance between those quartiles: IQR = Q3 − Q1. It describes the spread of the middle half of the observations.

The familiar 1.5 × IQR rule sets two boundaries, often called fences. A value is flagged when it falls below the lower fence or above the upper fence. The fences are thresholds for identifying points, not necessarily values that appear as whisker endpoints. The whiskers typically extend from the box to the lowest and highest observed values that remain within the fences. Values outside them are plotted individually.

This is a statistical convention, not proof that a measurement is wrong. An unusual point could be a data-entry error, a rare but valid event, or evidence that the data contain more than one group. The plot identifies values worth investigating; it does not explain why they are unusual.

How Excel calculates and displays outliers

Excel’s Box and Whisker chart uses quartiles to determine the IQR and the 1.5 × IQR rule to identify outliers. The quartile calculation can affect the result, especially with small datasets. In Microsoft 365 for Windows, select the chart’s data series, open Format Data Series, and look under Series Options for Quartile Calculation. The choices are inclusive or exclusive median. Inclusive includes the overall median when splitting an odd-sized dataset into lower and upper halves; exclusive leaves it out.

Excel also provides options to show or hide inner points and outlier points. These options change what the chart displays, not the underlying observations. If outlier points are hidden, a chart can appear to have none even when values lie beyond the fences. Check Show outlier points in the series options before concluding that no outliers exist. Inner points, when shown, are individual observations that are not classified as outliers.

Quartile methods differ across statistical tools, and results can differ for the same small sample. For a chart intended for comparison or reporting, note which quartile method is selected and use it consistently. Do not assume two plots made with different quartile conventions will flag exactly the same observations.

A worked example of the 1.5 × IQR rule

Consider these ten values: 2, 3, 4, 5, 6, 7, 8, 9, 10, and 30. With Excel’s inclusive quartile calculation, Q1 is 4.25 and Q3 is 8.75. The IQR is 8.75 − 4.25 = 4.5. Multiplying the IQR by 1.5 gives 6.75, so the lower fence is 4.25 − 6.75 = −2.5 and the upper fence is 8.75 + 6.75 = 15.5.

Only 30 lies beyond those fences, so it is the outlier in this example. The whiskers reach 2 and 10, the smallest and largest observations still inside the fences. Notice that 30 is not discarded from the data: it remains an observed value and appears as a separate marker if outlier points are shown. The chart’s vertical scale must also accommodate it, which can make the box and the rest of the distribution look compressed.

Create and inspect the chart in Excel

In Excel for Microsoft 365 on Windows, arrange each group of numeric observations in its own column, with a descriptive heading in the first row. Select the data, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel creates a box for each data series. If you have just one group, put its observations in one column; if you are comparing groups, use one column per group and label each column.

To inspect or change the display, click a box, right-click the data series, and choose Format Data Series. In Series Options, review the quartile calculation and the checkboxes for showing inner points, outlier points, and the mean marker. The mean marker is different from the median line. You can also use chart titles, axis labels, and number formatting to make units and group names clear.

On a Mac, the Box and Whisker chart is also available in current Excel versions, though ribbon layout and pane wording can vary by release. Look under the Insert tab’s statistical chart options, then use the chart formatting pane to find series settings. Excel for the web may offer a more limited editing experience than the desktop app; if a setting is missing, open the workbook in the desktop version.

Check suspected outliers with worksheet formulas

A chart is useful for spotting unusual points, but worksheet calculations make it easier to document the thresholds and identify the original values. Suppose the ten example observations are in B2:B11, sorted or unsorted. The following formulas use Excel’s inclusive quartiles. Place each result in the specified cell:

With the observations in B2:B11, enter =QUARTILE.INC(B2:B11,1) in D2. It returns Q1 = 4.25.
With the same observations in B2:B11, enter =QUARTILE.INC(B2:B11,3) in D3. It returns Q3 = 8.75.
With Q1 in D2 and Q3 in D3, enter =D3-D2 in D4. It returns an IQR of 4.5.
With Q1 in D2 and the IQR in D4, enter =D2-1.5*D4 in D5. It returns the lower fence, −2.5.
With Q3 in D3 and the IQR in D4, enter =D3+1.5*D4 in D6. It returns the upper fence, 15.5.

To label each observation, use a helper column. With the data in B2:B11 and the lower and upper fences in D5 and D6, enter =OR(B2<$D$5,B2>$D$6) in C2 and fill it down through C11. It returns TRUE for 30 and FALSE for the other nine values. The dollar signs keep the fence references fixed as the formula is copied. If you choose the exclusive quartile setting for the chart, use the corresponding exclusive quartile calculation in your worksheet rather than comparing unlike methods.

Common reasons outlier results seem unexpected

Outlier markers are switched off. Open Format Data Series and check Show outlier points. Hiding markers does not remove values from the source.
The quartile method differs. Inclusive and exclusive median calculations can produce different fences, particularly for short lists. Check the selected chart option before comparing it with a hand calculation.
Text or blank cells affect the input. Confirm that the intended observations are numeric and that the selected range does not include totals, notes, or unrelated columns.
The axis makes a value hard to see. A distant point can stretch the scale and flatten the box visually. Review the axis bounds or inspect the source values; changing the display scale does not change the outlier calculation.
A point is mistaken for an outlier. When inner points are displayed, ordinary observations may appear individually. Check whether the marker is beyond a whisker and whether Show outlier points is enabled.

Also verify that every group uses the same units and comparable measurement rules. A value recorded in dollars among values recorded in cents, for example, may appear extreme because of inconsistent data rather than a genuine unusual observation.

What an outlier does—and does not—tell you

Box plots are especially helpful for comparing medians, spread, and possible unusual values across several groups. They do not show the exact shape of every distribution as clearly as a histogram, and a single outlier marker does not reveal whether the observation is a typo, a valid extreme, or part of a second population. Check the original record and the context before deciding how to handle it.

Do not automatically delete a flagged value or replace it with a fence value. If it is a recording error, correct it using the source record. If it is valid, retaining it may be essential, especially when the analysis concerns rare events or risk. If you present results with and without a valid extreme point, explain why and show both views rather than quietly removing the observation.

Frequently Asked Questions

What is the outlier rule for an Excel box plot?
Excel uses the 1.5 × IQR rule: a point is an outlier below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR. The whiskers generally end at the most extreme observations that remain inside those limits.

Why does my Excel box plot show no outliers?
There may be no values beyond the fences, or the chart may have Show outlier points turned off. Check the data series settings and calculate the fences using the chart’s selected quartile method.

Can I change how Excel calculates quartiles?
Yes. Select the series, open Format Data Series, and choose the inclusive or exclusive median option under Series Options. The setting can change the quartiles and fences, so keep it consistent when comparing charts.

Are values beyond the whiskers always errors?
No. They are statistically unusual under the plot’s rule, not automatically incorrect. Check the source and context before correcting, excluding, or otherwise treating an observation differently.

Does Excel remove outliers from the chart data?
No. The observations remain in the source data. Excel represents flagged values as separate points when outlier display is enabled; hiding those markers does not delete the values.

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.