Box Plot in Excel Without Outliers: Hide or Exclude?

To hide outlier markers in an Excel box plot, select the chart, right-click a data series, choose Format Data Series, and clear Show outlier points under Series Options. This hides the dots but keeps those values in the chart’s calculations; to remove their influence as well, chart a separate range that leaves them out.

What “without outliers” means in an Excel box plot

People use “without outliers” to mean two different things. The first is visual: keep the full dataset and its statistical summary, but stop drawing individual outlier dots. The second is analytical: omit selected values before calculating the box, median, and whiskers. Excel treats these as separate tasks, so choosing the right one matters.

A box-and-whisker chart summarizes a distribution. The box covers the middle half of the data, with a line for the median; whiskers extend toward the low and high ends. Values beyond the whisker limits may appear as individual points. In a conventional Tukey-style box plot, those limits are based on 1.5 times the interquartile range (IQR) below the first quartile and above the third quartile. A point outside a limit is flagged for display, but that label alone does not prove the value is an error.

In Excel, the Show outlier points option controls whether those markers appear. Turning it off does not delete values, change the quartiles, or recalculate the whiskers as if the points were absent. It is therefore suitable when the dots make a chart hard to read but the underlying observations still belong in the analysis.

Hide outlier markers: exact steps

These steps apply to Excel for Microsoft 365 on Windows. First create or select a box-and-whisker chart, then format the data series whose outlier markers you want to hide:

Select the chart. If you are creating one, select the data and choose Insert > Insert Statistic Chart > Box and Whisker.
Right-click a box or another visible part of the series and choose Format Data Series. You can also select the series and use the chart formatting controls to open the Format Data Series pane.
In the pane, select Series Options, then clear Show outlier points.
Repeat for any other series that should have its outlier markers hidden. Check the chart after changing the setting, especially when it contains several categories or groups.

The points disappear, but the chart continues to summarize the original values. You can turn the markers back on by selecting the same checkbox. If the chart contains multiple series, verify which one is selected before changing the option; formatting one series does not necessarily change every series.

On Excel for Mac, the chart type and series setting are also available, though the ribbon and formatting pane can be arranged differently from Windows. Select the series, open Format Data Series, and look for Show outlier points under Series Options. Excel for the web may offer fewer chart-formatting controls than the desktop app; if the checkbox is unavailable, open the workbook in the desktop version.

What the setting changes—and what it does not

Hiding outlier markers changes the chart’s appearance, not the data behind it. The values remain part of the series, and Excel still uses the series to calculate the box, median, and whiskers. This is a useful distinction when explaining a chart: a plot without visible dots does not necessarily represent a dataset with those observations removed.

Excel also has separate options such as Show inner points, Show mean markers, and Show mean line. These control other marks or summaries; they are not substitutes for Show outlier points. For instance, turning off inner points hides individual observations that are not classified as outliers, while turning off outlier points hides the flagged observations. If a chart still looks busy, review those settings individually rather than assuming one checkbox controls every point.

The Series Options pane can also let you choose how quartiles are calculated, including inclusive or exclusive median methods. That choice can affect the quartiles and, consequently, which values qualify as outliers. For comparisons across groups, use a consistent quartile method and document it. Otherwise, two charts may look different partly because of their settings rather than because the data distributions differ.

Hide points or exclude values: how to choose

Hide markers when the values are valid and you want to retain their influence on the summary, but do not need to display every flagged point. For example, a sensor reading may be unusually high yet still be a real observation. A clean-looking chart can be appropriate for a presentation, provided the chart is not described as having removed the value.

Exclude values only when there is a defensible reason to leave them out of the analysis, such as a confirmed data-entry error or a measurement taken under a condition outside the study’s scope. A statistical outlier is not automatically invalid. Removing a legitimate extreme observation can shift the median, quartiles, and spread, and may conceal meaningful variation.

Choose hide when the goal is a less cluttered chart and the source values should remain in the calculation.
Choose exclude when you have a documented data-quality or analysis rule that justifies omitting specific records.
Show both views when readers need context: present the full-data chart alongside a clearly labeled chart made from the approved subset.

Exclude outliers with a helper range

Excel’s outlier-marker checkbox does not automatically remove flagged observations from the calculations. A practical alternative is to calculate fences in cells, create a helper range containing only values within those fences, and build a second box plot from that range. The example below uses the inclusive quartile convention. Use a numeric-only source range with no header in A2:A11, and put the calculations in column B.

With the ten example values 10, 12, 13, 14, 15, 16, 18, 20, 22, and 100 in A2:A11, enter the first-quartile formula in B2:

=QUARTILE.INC(A2:A11,1)

B2 returns 13.25. With the same data layout, enter the third-quartile formula in B3:

=QUARTILE.INC(A2:A11,3)

B3 returns 19.5. With Q1 in B2 and Q3 in B3, calculate the IQR in B4:

=B3-B2

B4 returns 6.25. With the IQR in B4 and quartiles in B2:B3, calculate the lower fence in B5:

=B2-1.5*B4

B5 returns 3.875. With the same cells, calculate the upper fence in B6:

=B3+1.5*B4

B6 returns 28.875, so 100 is above the upper fence in this example. With the source data in A2:A11 and the fences in B5 and B6, enter this formula in D2 to return values inside the fences:

=FILTER(A2:A11,(A2:A11>=B5)*(A2:A11<=B6))

The spilled result contains nine values, from 10 through 22, and excludes 100. FILTER is available in Microsoft 365 and Excel 2021 or later. Select the helper results and insert a Box and Whisker chart to visualize that subset. If charting a spill range is awkward in your Excel version or workbook setup, copy the returned values and paste them as values into a normal range first.

This example applies a stated fence rule; it does not decide whether 100 should be removed. If you use Excel’s exclusive quartile calculation or a different outlier rule, calculate the fences using that same rule. Also keep the original data unchanged and label the chart or helper range so readers know it shows a filtered subset.

Make the chart easier to interpret

When the outlier dots are hidden, readers may not see how far the most extreme observations lie from the rest of the distribution. Add a note to the chart or its caption if that context affects interpretation. You can also keep the full values in a table or include a second chart with outlier markers visible.

For comparisons between groups, apply the same display settings to each series and use the same quartile calculation method. If you exclude data, apply the same documented rule consistently where appropriate, rather than dropping values only from groups where they look inconvenient. Record the number of observations excluded and the reason, particularly when the chart will inform a report or decision.

Remember that box plots show distributions, not the cause of unusual values. A point can arise from a typo, a valid rare event, a different population, or normal variation. Investigate the source and context before deciding whether to remove it. If the original and filtered charts tell different stories, showing both can be more informative than presenting only the cleaner version.

Common problems and fixes

The checkbox is missing. Make sure you selected a box-and-whisker data series, not the chart area, title, or an axis. Right-click a box and open Format Data Series. Some web or mobile versions may not expose all desktop chart controls.
Only some dots disappear. The setting may have been changed for just one series. Select each series and check its Series Options.
A dot still appears. It may be an inner point rather than an outlier marker, or it may belong to a different series. Check Show inner points and the selected series as well.
The chart changes after filtering. That is expected: removing values changes the data distribution and can change the quartiles, median, and whiskers. It is not the same operation as hiding markers.
The chart and manual fences disagree. Check that both use the same quartile convention and data range. Blank cells, text, and different group ranges can also affect the comparison.

Frequently Asked Questions

Can I remove outlier dots without changing the box plot?
Yes. Clear Show outlier points in Format Data Series > Series Options. The dots disappear, but the original values still contribute to the chart’s statistics.

Does Excel delete outlier values when I hide them?
No. Hiding outlier points is a display change only. The source data and the box plot calculations still include those observations.

How do I remove outliers from the box plot calculations?
Create a separate range that excludes values under a stated rule, then chart that range. Keep the source data intact and label the filtered chart so it is clear that it summarizes a subset.

Why does my Excel box plot show no outlier points?
Show outlier points may be unchecked, or the selected data may contain no values beyond the chart’s outlier limits. Check the series setting and the quartile calculation method.

Should I remove every statistical outlier?
No. An outlier is a signal to review a value, not proof that it is wrong. Remove one only when your data-quality or analysis criteria justify doing so.

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.