Box Plot in Excel: Show Outliers and Read the Chart

Yes. In Excel for Microsoft 365 on Windows, select your data, choose Insert > Insert Statistic Chart > Box and Whisker, then right-click a box and select Format Data Series to turn on Show outlier points. Excel marks values beyond the whisker fences as individual points; whether a value is an outlier is a statistical flag, not proof that the data is wrong.

What a box plot shows

A box plot, also called a box-and-whisker plot, summarizes the distribution of numeric data. It is useful when you want to compare the center, spread, and unusually high or low observations across several groups without displaying every value as a separate bar.

The box runs from the first quartile (Q1) to the third quartile (Q3), so its height represents the interquartile range, or IQR: Q3 minus Q1. A line inside the box marks the median. Whiskers extend from the box toward the low and high ends of the non-outlier data. Values beyond those whiskers can appear as separate outlier points when that display option is enabled.

For example, a box plot can compare delivery times for four regions. The medians show which regions typically take longer, the box heights show how variable the middle half of delivery times is, and detached points can draw attention to unusually long or short deliveries. The chart condenses the distribution; it does not show every detail that a list or histogram can.

Prepare the data before charting

Arrange each group in its own column, with a descriptive header in the first row. Put numeric observations underneath the relevant header. For example, columns named North, South, East, and West can hold one region’s delivery times each. Groups can contain different numbers of observations; leave unused cells blank rather than entering zero, because zero is a real numeric value and can change the distribution.

Check that values Excel should analyze are genuinely numeric. Imported numbers stored as text, notes mixed into a column, or totals included among observations can lead to confusing results. Remove or correct those entries before creating the chart. If a blank means a missing measurement, keep it blank; do not replace it with an estimated value unless that choice is appropriate for the analysis.

For one group, a single column of observations is enough. Include the header when selecting the range so Excel can use it as a series label. For multiple groups, select the full rectangular range, including headers. If Excel puts groups on the wrong axis or treats rows as groups instead of columns, select the chart and use Chart Design > Switch Row/Column. The exact available controls can vary with the chart selection and Excel build.

Create a Box and Whisker chart

Select the data range, including the group names at the top.
On the Windows ribbon, choose Insert > Insert Statistic Chart > Box and Whisker.
Select the chart, then right-click one of the boxes and choose Format Data Series.
In the Format Data Series pane, open Series Options and select Show outlier points if it is not already selected.
Use the other series options, if useful, to show inner points, mean markers, or a mean line. Add a clear title and axis labels so readers know what the groups and measurements represent.

The option applies to the selected data series, which may include several groups depending on how the source range was arranged. If you have multiple series and their settings differ, inspect each one. You can also use the chart’s plus button or Chart Design tab to add titles and other chart elements.

These ribbon steps are for desktop Excel for Microsoft 365 on Windows. Excel for Mac has a similar Box and Whisker chart type, though ribbon placement and pane layout can differ; look under Insert for the statistical chart options and use Format Data Series for series settings. If Excel for the web does not expose the formatting option you need, open the workbook in desktop Excel.

How Excel identifies outlier points

The familiar box-plot rule uses the IQR to set two fences. The lower fence is Q1 minus 1.5 times the IQR; the upper fence is Q3 plus 1.5 times the IQR. Observations below the lower fence or above the upper fence are commonly designated outliers. The whiskers reach to the most extreme observed values that remain within those fences, rather than necessarily extending to the fences themselves. Points outside the whiskers are plotted separately when outlier points are shown.

Consider the sorted values 2, 3, 4, 5, 6, 7, 8, 9, 10, and 30. Using Excel’s inclusive quartile calculation, Q1 is 4.25 and Q3 is 8.75. The IQR is 4.5, so the upper fence is 8.75 + (1.5 × 4.5), or 15.5. The value 30 lies above that fence and qualifies as an outlier under this rule. The value is still part of the data; the chart simply places it beyond the whisker.

“Outlier” does not automatically mean a mistake. A point may reflect a data-entry error, a faulty sensor, a special event, or a genuine but rare case. Check the source and context before correcting or excluding it. Removing a valid extreme value simply to make the chart look neater can hide important information.

Choose a quartile calculation

Excel’s Box and Whisker series options include a Quartile Calculation setting, typically offering Inclusive median and Exclusive median. These choices use different conventions when dividing ordered observations into quartiles. With small samples, or data with repeated values, the resulting box boundaries and outlier classifications can differ. Neither option is universally correct for every purpose; the important thing is to use a convention appropriate to your analysis and report it when results need to be reproducible.

To inspect the setting, right-click a box, choose Format Data Series, and look in Series Options for Quartile Calculation. If you calculate quartiles separately, match the calculation to the chart setting. Excel’s QUARTILE.INC function uses the inclusive convention, while QUARTILE.EXC uses the exclusive convention. Their results need not match the other setting, particularly for small data sets. Do not assume two charts or a chart and a formula are inconsistent until you have checked their quartile methods.

Also distinguish the median from the mean. The line inside the box is the median; selecting Show mean markers or Show mean line adds a separate display for the arithmetic mean. The mean can be pulled toward extreme values, while the median is less sensitive to them. Showing both can help explain a skewed distribution, but label the chart or explain its symbols so readers do not confuse them.

Read the chart without overinterpreting it

Compare medians to assess typical values, and compare box heights to see how widely the middle half of each group is spread. A longer box indicates a larger IQR. Whisker lengths can suggest how the values outside the middle half are distributed, while isolated points identify observations beyond the chart’s outlier threshold. These features describe the sample, not the cause of the pattern.

A box plot does not reveal the exact value of every observation at a glance, and two groups with similar boxes can still have different shapes. For example, a group with two clusters may look deceptively ordinary in a box plot. Showing inner points can help with a small sample, but many observations may overlap and become hard to read. A histogram, dot plot, or a table of source values can provide useful context when shape or individual observations matter.

Outlier points can also affect how the chart appears. One extreme value may stretch the axis and make the boxes look compressed. That is not a reason to hide it automatically. Consider adding a companion chart, splitting groups when that is substantively justified, or using a clearly labeled axis scale that preserves the values. Avoid changing the axis in a way that makes meaningful differences hard to see.

Check outliers with a worksheet formula

If you want a row-by-row flag to review against the chart, use the same quartile method as the chart. For a simple illustration, assume the ten numeric observations listed above are in B2:B11, with 30 in B11; enter the formula below in C2 and fill it down through C11. It uses inclusive quartiles and labels a value outside the 1.5-IQR fences.

With the values 2, 3, 4, 5, 6, 7, 8, 9, 10, and 30 in B2:B11, the formula in C2 is =IF(OR(B2<QUARTILE.INC($B$2:$B$11,1)-1.5*(QUARTILE.INC($B$2:$B$11,3)-QUARTILE.INC($B$2:$B$11,1)),B2>QUARTILE.INC($B$2:$B$11,3)+1.5*(QUARTILE.INC($B$2:$B$11,3)-QUARTILE.INC($B$2:$B$11,1))),"Outlier","Within fences"). With that example, C11 returns Outlier because 30 exceeds the upper fence of 15.5; the other values return Within fences. This formula is a review aid, not a substitute for checking whether the observation is valid. For a different chart quartile setting, use the corresponding quartile convention in your calculation.

Common problems and fixes

No outlier dots appear: There may be no observations beyond the fences, or Show outlier points may be turned off. Check the series option and verify the data range.
The chart has unexpected zeros or gaps: Look for zeros entered in place of missing values, numbers stored as text, and blank cells. Correct the source rather than relying on a chart to infer what the entries mean.
The chart looks different from another tool: Compare the quartile method, included observations, and treatment of missing values. Different conventions can produce different quartiles and flagged points.
Groups appear in the wrong orientation: Review the selection and try Chart Design > Switch Row/Column. Make sure headers are included and each intended group is arranged consistently.
A point looks extreme but is not marked: Visual distance alone does not determine the rule. Check its value against the calculated fences and confirm that the correct series and quartile setting are selected.

Frequently Asked Questions

Does Excel automatically show outliers on a box plot?
Excel provides a Show outlier points option in Format Data Series. Check that it is selected. If no data values fall beyond the whisker fences, there will be no outlier points to display.

Can I make a box plot with only one data column?
Yes. Select the column of numeric observations and its header, then insert a Box and Whisker chart. The chart summarizes that one group; additional columns let you compare groups.

Why does Excel mark a value as an outlier?
Under the common 1.5-IQR rule, a value is flagged when it falls below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR. Check the chart’s quartile setting because the quartiles, and therefore the fences, depend on the chosen method.

Can I hide outlier points without deleting the data?
Yes. In Format Data Series, clear Show outlier points. This changes what the chart displays; it does not remove values from the worksheet. If the chart is used to communicate results, make the display choice clear and retain the underlying data.

Should I delete an outlier from my data?
Not solely because Excel flags it. Verify the original record and decide whether it is an error or a legitimate observation. Document any correction or exclusion, since removing a valid value can change the summary and conclusions.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.