Excel Box Plot Outliers: Why You Can’t Remove Them

Excel Box Plot Outliers: Why You Can't Remove Them

Yes—you can hide the outlier markers in an Excel box plot, but Excel does not provide a command to delete an individual outlier from the chart while keeping the rest of its source data unchanged. To hide the markers, select the box-plot series, open Format Data Series, and clear Show outlier points; to exclude values from the analysis, change the data range the chart uses.

What Excel means by a box plot outlier

A box-and-whisker chart summarizes a group of numerical values using quartiles, a median, and whiskers. In Excel, a point is generally plotted as an outlier when it falls beyond the whisker limits. With the common 1.5-interquartile-range rule, the lower fence is Q1 minus 1.5 times the interquartile range, and the upper fence is Q3 plus 1.5 times the interquartile range. The interquartile range, or IQR, is Q3 minus Q1.

The whiskers usually extend to the most extreme observed values still within those fences; they do not necessarily extend to the minimum and maximum of the entire dataset. Values beyond the fences appear as separate markers when outlier points are displayed. Excel calculates the chart’s quartiles and fences from the values in its source range, so the marker is not a separate object in the way an individually plotted point might be in another chart type.

For example, a box plot of measurements from several machines might show one unusually high reading. That marker signals that the value is far from the central spread under the chart’s rule. It does not establish that the reading is an error: it could reflect a recording mistake, a rare but valid event, or a meaningful difference between machines.

Hide outlier markers without changing the data

If the chart is correct but the separate markers make it hard to read, turn off their display. In Excel for Microsoft 365 on Windows:

Click the chart, then click a box or data point in the series whose markers you want to hide. Selecting the series matters; clicking the chart background opens chart-area formatting instead.
Right-click the selected series and choose Format Data Series. You can also use Ctrl+1 on Windows to open the formatting pane after selecting the series.
In the pane, open Series Options and clear Show outlier points.

This setting hides the outlier markers for the selected series. It does not erase the source values, recalculate the chart from a reduced dataset, or change the underlying quartiles. The outlying observations still contribute to the summary statistics and remain in the worksheet. If the chart contains multiple series, inspect each one: a setting applied to one series may not hide markers in all the others.

On a Mac, select the series and Control-click or right-click it to open Format Data Series; then look for Series Options and the outlier-point checkbox. Pane layout can vary by Excel release. If you cannot find the option, confirm that the selected chart is a box-and-whisker chart and try Excel for desktop rather than assuming the points are individually selectable.

Remove values from the chart’s analysis

If you truly need a value excluded, change the chart’s input data rather than just hiding its marker. Keep an untouched copy of the original observations, then create a separate analysis range that omits only values you have a defensible reason to exclude. Build a new box plot from that range, or update the existing chart’s source range through Chart Design > Select Data. That way, the chart’s quartiles, median, and whiskers are recalculated from the observations you intend to analyze.

Before editing the source, decide whether the goal is to correct an error or to present a different analytical view. If a value was mistyped and the correct value is known, correct it in the working data while retaining an audit trail. If it is a valid observation but you want a comparison without it, make a clearly labeled filtered or sensitivity-analysis chart instead of quietly deleting it. Readers should be able to tell which observations are included.

For groups arranged in separate columns, a practical approach is to make a second table with the same group headings and only the retained observations, then create the box plot from that table. If groups have different numbers of observations, leave unused cells blank and check the resulting chart carefully; confirm that blanks are not being treated as zeros and that the plotted categories match the intended data. A filter or helper range can also be useful, but verify what the chart includes after changing the worksheet.

How to identify an outlier with worksheet formulas

A helper column can make the 1.5-IQR rule explicit instead of relying only on the chart’s markers. Suppose one group’s observations are in A2:A11. For this example, the sorted values are 10, 11, 12, 12, 13, 14, 15, 16, 18, and 40. With Excel’s inclusive quartile calculation, Q1 is 12 and Q3 is 15.75, so the IQR is 3.75. The upper fence is 21.375 and the lower fence is 6.375; 40 falls above the upper fence.

With the observations in A2:A11, enter this formula in B2 to calculate Q1, then fill down if you want the same reference available beside each row:

=QUARTILE.INC($A$2:$A$11,1)

With the same values in A2:A11, calculate Q3 in C2:

=QUARTILE.INC($A$2:$A$11,3)

With Q1 in B2 and Q3 in C2, enter this formula in D2 beside the first observation to flag whether A2 is outside the 1.5-IQR fences:

=OR(A2<B2-1.5*(C2-B2),A2>C2+1.5*(C2-B2))

In this example, the formula returns FALSE for values within the fences and TRUE for 40. To apply the same test to each observation, copy the formula down while keeping B2 and C2 fixed if those cells hold the group’s quartiles. For multiple groups, calculate quartiles separately for each group; using one combined range can flag values against the wrong distribution.

This calculation is a diagnostic, not an automatic instruction to delete records. Also, Excel’s box-plot settings offer inclusive- and exclusive-median quartile calculations. The formula above specifically uses QUARTILE.INC; if the chart uses a different quartile convention, especially with a small dataset, the formula’s fences may not exactly match the chart’s classification. Match the method before comparing flags with plotted markers.

Change the whisker rule or quartile calculation

Hiding outlier points is not the only way to change how a box plot looks. Select the series and review its options in Format Data Series. Depending on the settings available in your Excel build, you can change the whisker ends between the 1.5-IQR rule and minimum/maximum, and choose an inclusive- or exclusive-median quartile calculation. These settings alter the chart’s statistical summary; they do not remove any worksheet observations.

Choosing minimum/maximum whiskers makes the whiskers reach the lowest and highest observed values, so there are no values beyond the whiskers to mark as outliers under that display. This can make the chart less visually busy, but it also changes what the whiskers communicate. It is not equivalent to excluding extreme values. Similarly, changing the quartile method can shift the box boundaries, particularly when a group contains few observations. If you compare groups or versions of a chart, use consistent settings and state them when the method matters.

Other display options can be confused with outlier controls. Show inner points displays individual observations, while Show outlier points controls the points beyond the whisker rule. Turning off inner points does not necessarily hide outliers, and turning off outlier points does not remove the box, median, or whiskers. Mean markers and mean lines are also separate options.

Why the checkbox may be missing or seem ineffective

The wrong chart element is selected. Clicking the plot area, a data label, or the chart title opens different formatting options. Click a box or point in the series, then reopen Format Data Series.
The chart is not a box-and-whisker chart. A scatter plot or another chart type may have individually formatted markers, but it will not show the same box-plot series options. In desktop Excel, box plots are available from Insert > Insert Statistic Chart > Box and Whisker.
You are changing only one group. A chart can contain several series. Select each series and check its setting if markers remain elsewhere.
You expected the source values to disappear. The checkbox changes the display only. Check the worksheet or the chart’s selected source range to confirm which observations are actually included.
The chart was created from unexpected rows or columns. Use Chart Design > Select Data to review the range and series orientation. Correct the source rather than trying to format a marker that represents data you did not intend to plot.

Excel for the web and different desktop releases can expose chart-formatting controls differently. If an option is unavailable in the interface you are using, open the workbook in the Microsoft 365 desktop app for Windows or Mac and check the series options there. Avoid deleting a worksheet row merely because a marker looks inconvenient: that can affect formulas, other charts, and analyses that depend on the same data.

When should you keep an outlier?

Keep a value when it is valid and relevant to the question the chart is meant to answer. An unusually large customer order, a rare equipment failure, or a high measurement may be the most important observation in the dataset. Removing it can make the distribution appear more typical, but it can also conceal risk or a genuine subgroup difference.

Investigate a possible outlier by checking the original record, units, entry process, and context. Ask whether the value is a typo, a measurement problem, a value from a different population, or a legitimate extreme. If you exclude it, document the reason and show how that decision affects the result where appropriate. A useful presentation may include both the full-data chart and a clearly labeled chart or analysis with the justified exclusion, rather than silently replacing one with the other.

Frequently Asked Questions

Can I delete just one outlier dot from an Excel box plot?
No—not as an independent chart marker while leaving the chart’s source data and calculations unchanged. Turn off outlier-point display for the series, or exclude the observation from a separate source range if it should not be part of the analysis.

Does hiding outliers change the median or quartiles?
No. Clearing Show outlier points hides the markers but leaves the data and calculated summary intact. Changing the source observations or the quartile and whisker settings can change the chart’s statistics or appearance.

Why does Excel call a value an outlier?
The chart identifies points beyond its whisker limits. With the 1.5-IQR rule, those limits are based on Q1 and Q3 plus or minus 1.5 times the IQR. The label describes a statistical rule, not proof that the observation is wrong.

Will changing whiskers to minimum and maximum remove outliers?
It can stop points from appearing beyond the whiskers because the whiskers extend to the data extremes. It does not remove or exclude those values; it changes the chart’s whisker definition.

Can I remove outliers from the chart but keep them in the worksheet?
Yes. Preserve the original data and create a separate chart input range that excludes the observations you have chosen to omit. Build or update the chart from that range, and label the treatment so viewers know the chart does not include every original value.

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.