Excel Box and Whisker Plot Not Working? Fixes to Try

If an Excel box-and-whisker plot is missing, blank, or arranged incorrectly, first check that you are using a supported Excel version and that each group contains numeric observations arranged in rows or columns. In Microsoft 365 for Windows, select the data and choose Insert > Insert Statistic Chart > Box and Whisker; then check the chart’s data orientation and series options.

How a box-and-whisker plot works

A box plot summarizes the distribution of a set of values. The box represents the lower and upper quartiles, with a line marking the median. The interquartile range, or IQR, is the upper quartile minus the lower quartile. Whiskers extend to the most extreme data points that are not considered outliers under the chart’s rule; points beyond 1.5 times the IQR from a quartile can be displayed separately as outliers.

That summary explains why a box plot needs a group of observations, not just one number for each category. For example, a column of five test scores for Class A and another column of scores for Class B can produce a useful comparison. A table containing only one average per class cannot show the spread, quartiles, or outliers within each class.

Excel also offers display choices that can make a chart look different without changing the source data. You can show individual inner points, outlier points, mean markers, or a mean line. A mean marker is not the median: the median is the line inside the box. Knowing this distinction helps identify whether the chart is wrong or simply displaying a different statistic than expected.

Check Excel version and chart availability

The built-in Box and Whisker chart is available in modern desktop Excel, including Excel 2016 and later editions such as Microsoft 365. It is not a standard chart type in older releases such as Excel 2013. If the command is missing, check the application version and whether the workbook is open in desktop Excel rather than assuming the data is faulty.

On Windows, the usual path is Insert > Insert Statistic Chart > Box and Whisker. If you are working in Excel for the web or on a Mac and do not see the same command or chart options, open the workbook in an up-to-date desktop version of Excel. Ribbon labels and the range of editing options can differ by platform and release. A chart created in a newer version may also be displayed differently when opened in an older one.

Confirm that the file is an editable Excel workbook, not a preview or a read-only copy. If the chart is present but its controls are unavailable, select the chart and look for the Chart Design and Format tabs. If those tabs do not appear, you may have selected a cell or an image of a chart rather than the chart object itself.

Arrange the source data for comparison

For the simplest layout, put one group in each column, with a descriptive heading in the first row and one numeric observation per row beneath it. For example, columns named Morning and Afternoon could contain measurements from each time period. Select the headings and values together before inserting the chart. Excel can then use the headings to label the groups.

Rows can also represent groups. If Excel interprets the rows and columns the wrong way, select the chart and use Chart Design > Switch Row/Column. This changes how Excel groups the selected values; it does not transpose or alter the worksheet data. Check the axis labels afterward to confirm that each box represents the intended category.

Keep each group’s observations together in a single row or column, and use a clear heading for each group.
Include only the data and its headers in the selection. Extra totals, notes, or a second unrelated table can become unintended series.
Use actual numbers for observations. A value that looks numeric but is stored as text may not be plotted as expected.
Leave missing observations blank rather than entering words such as none or not recorded in the measurement range.
Avoid merged cells in the selected data because they can make the range difficult for Excel to interpret consistently.

Different groups do not have to contain exactly the same number of observations. A shorter group can have blank cells below its last value. However, a group with very few observations may produce a collapsed or uninformative box because there is little or no variation from which to show quartiles and spread.

Rebuild or repair the chart’s data range

If an existing chart is empty or omits recent measurements, the chart may point to the wrong range. Select the chart and choose Chart Design > Select Data. Review the chart data range and the listed series, then correct the selection so it includes the intended numeric observations and headers. If the source table has grown, add the new rows or columns to the chart range; inserting data beside a chart does not always make the chart include it.

If the chart shows one box where you expected several, the issue is often orientation or selection. Try Chart Design > Switch Row/Column, then inspect the labels. If the chart has many boxes for individual records instead of one box per group, check whether Excel treated each row or column as a separate series. Rebuilding from a clean, rectangular range can be quicker than editing a complicated selection.

When a chart is blank, inspect the cells themselves. Imported measurements may contain leading apostrophes, spaces, or other characters that make apparent numbers text. Check a few cells by selecting them and looking at the formula bar, and use Excel’s number-format or conversion prompts where available. Do not rely only on how a cell looks: formatting can make text resemble a number.

Also check for a range that contains only headers, formulas returning empty text, or formulas that currently return errors. A chart cannot plot a meaningful distribution from labels alone. If the source values are formulas, verify that the formulas return numeric results for the relevant records and that calculation is up to date.

Understand quartile and display settings

To adjust a box plot in Microsoft 365 for Windows, right-click a box in the chart and choose Format Data Series. Under Series Options, review the available quartile calculation and display checkboxes. Excel provides inclusive-median and exclusive-median quartile calculation choices. These methods can produce different quartile boundaries, especially in small samples, so two charts based on the same values may not have identical boxes if their settings differ.

Try enabling Show outlier points if unusual values seem to have disappeared. Turning that option off hides the plotted outlier markers; it does not mean the observations have been deleted from the worksheet. Show inner points reveals observations within the whisker limits as well. Mean markers and a mean line can help compare averages, but they may make a chart busy when there are many groups.

A box that looks like a thin line is not necessarily a rendering error. If many measurements are identical or tightly clustered, the quartiles can coincide or be very close together. A large outlier can also make the main boxes look compressed relative to the vertical axis. Check the source values and axis scale before changing the chart type. In particular, confirm that one unusually large value is a genuine observation rather than a misplaced total or data-entry mistake.

Common symptoms and practical fixes

The chart type is missing: Check that you are using Excel 2016 or later in a version that supports creating the chart. Try current desktop Excel if the web or Mac interface does not show the expected command.
The chart is blank: Confirm that the selected range includes numeric observations, not just headings, text, or formula results that are empty or errors.
There is only one box: Check the selected range and use Switch Row/Column to test the other grouping direction.
There are too many boxes: Excel may be treating individual records as separate groups. Reorganize the data so each group’s measurements share a row or column.
Outliers are not visible: In Format Data Series, check Show outlier points. Also verify that the suspected value is outside the chart’s outlier threshold.
The boxes look flat: Look for repeated values, a narrow spread, or an axis scale dominated by an extreme value. A flat box can accurately describe the data.
Recent records do not appear: Use Select Data to inspect and expand the chart’s source range.

For a concrete example, imagine two columns of daily response times. If one column contains 18 numeric times and the other contains 17 times plus a blank, Excel can still compare the groups. If the last entry in the second column is the text value 240 seconds rather than the number 240, it may be excluded from the numeric distribution. If 240 is a valid but much larger response time, it may instead appear as an outlier. Those are different problems: convert text to a number when appropriate, but do not remove a legitimate outlier merely to make the boxes look balanced.

When a different chart or method is better

A box plot is useful for comparing distributions, but it does not show every detail. It emphasizes quartiles and unusual values while making the shape between those points less visible. If you need to see individual observations, enable inner points or use a dot plot or scatter chart where the data structure permits. If the audience needs a quick count or frequency pattern, a histogram may be easier to interpret.

You can also calculate summary statistics in worksheet cells and build a custom chart, but that takes more setup and requires you to define the quartile and outlier rules consistently. This can be useful when you need a particular statistical convention or a chart that must work in older Excel versions. For routine comparisons in supported Excel versions, the built-in Box and Whisker chart is usually the simplest option. Whichever method you use, label the groups and explain the quartile convention if readers will compare the results with another tool.

Frequently Asked Questions

Why can’t I find Box and Whisker in Excel?
Check that you are using a version with the built-in statistical chart, such as Excel 2016 or later. In Windows desktop Excel, look under Insert > Insert Statistic Chart. If the command is unavailable in your current platform or interface, try opening the file in updated desktop Excel.

Why does my box plot show only one box?
Excel may be reading all selected values as one group, or your selection may contain only one group. Arrange each category’s observations in its own row or column, include the headers, and try Chart Design > Switch Row/Column.

Why are some values missing from my box-and-whisker chart?
Check whether the values are stored as numbers rather than text, and whether the selected chart range includes them. Blank cells, text labels, and formula results that are empty are not numeric observations to plot.

Why is my box plot just a line?
The quartiles may be identical or very close because the observations are repeated or tightly grouped. A broad axis range caused by an extreme value can also make the box look compressed. Review the values and scale before deciding that the chart is broken.

How do I show outliers in an Excel box plot?
Select the chart, right-click a box, choose Format Data Series, and enable Show outlier points under Series Options. The setting changes whether outlier markers are displayed; it does not remove observations from the source data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.