Box Plot Excel Formatting: Create and Style a Box Plot

To create a box plot in Excel for Microsoft 365 on Windows, arrange each group of numeric observations in its own column, select the data, then choose Insert > Insert Statistic Chart > Box and Whisker. To format it, right-click a box and choose Format Data Series; there you can adjust quartile calculations, show or hide points and mean markers, and style the series.

What an Excel box plot shows

A box plot, also called a box-and-whisker chart, summarizes the distribution of values in each group. The box marks the lower and upper quartiles, with a line for the median. The whiskers extend to the lowest and highest values that are not identified as outliers under the chart’s calculation method. Values beyond those whiskers can appear as individual points.

This makes a box plot useful when comparing spread and typical values across groups—for example, exam scores by class, delivery times by region, or monthly measurements from several machines. It compresses many observations into a compact graphic, but it does not show every detail of the distribution. Two groups can have similar boxes while differing in clusters, gaps, or the shape of their data.

Excel calculates the plotted statistics from the selected source data. Formatting changes how the chart looks and which features it displays; it does not change the underlying values. That distinction matters if a chart appears to disagree with a separately calculated quartile or with a box plot made in another program.

Prepare the data before inserting the chart

For the most straightforward layout, put each group in a separate column and give each column a heading. Place observations underneath the heading. For example, columns named North, Central, and South could contain the delivery times recorded for each region. The columns do not have to contain the same number of observations; leave unused cells empty rather than filling them with zeros, because zero is a real value and can affect the summary.

Use one column per group, with a clear text heading in the first row.
Keep the observations numeric. Text such as “12 days” may be treated as text rather than as a number.
Remove explanatory notes and totals from the selected range so Excel does not mistake them for observations.
Check for accidental blanks, imported text, and values that use a different unit from the rest of the column.

Select the headings and observations, then use Insert > Insert Statistic Chart > Box and Whisker. If the result treats rows as groups when you intended columns, select the chart and use Chart Design > Switch Row/Column. The chart’s category labels should identify the groups, not individual observations. On Excel for Mac, the chart type is also available from the Insert tab, though the ribbon layout and some formatting controls can look different. Excel for the web may not expose every box-plot formatting option, so use the desktop app if a needed series setting is missing.

Format the boxes, whiskers, and points

Right-click one of the boxes and choose Format Data Series to open the Format Data Series pane. The controls in this pane apply to the box-and-whisker series, so changing a setting can affect every group in the chart rather than just the one box you clicked. The available controls include the display of individual points and mean values, along with the quartile calculation method.

Show outlier points: Displays observations Excel classifies as outliers as separate markers beyond the whiskers.
Show inner points: Displays observations that are not classified as outliers as well. This can make a chart more informative for a small dataset, but crowded for a large one.
Show mean markers: Adds a marker for the arithmetic mean, which can differ noticeably from the median in a skewed dataset.
Show mean line: Adds a line representing the mean. Use it only when it is clear to readers which line is the mean and which is the median.
Quartile calculation: Choose Inclusive median or Exclusive median to control how the quartiles are calculated.

To adjust appearance, select the series and use the pane’s Fill & Line controls to change the box fill, outline, and line appearance. A restrained fill with a clearly visible outline usually keeps the quartiles legible. If you use different colors for groups, make sure the colors have enough contrast and do not rely on color alone to communicate meaning. A legend is useful only if the colors distinguish series; for a chart with one box per category, direct category labels are often clearer.

Set quartiles and understand the whiskers

The box’s lower and upper edges represent the first and third quartiles, and the line inside it represents the median. The interquartile range, or IQR, is the distance between the third and first quartiles. Excel’s box plot uses the selected inclusive or exclusive quartile calculation, so the box edges can vary slightly between methods, especially in small samples.

The inclusive and exclusive choices refer to how the median is handled when dividing a dataset to find the quartiles. In an odd-sized sample, the methods can include or leave out the overall median when calculating the lower and upper halves. Neither choice is automatically right for every purpose. If a report, course, or organization specifies a quartile convention, choose the matching option and state it when the method could affect interpretation.

Excel’s whiskers are not simply a line from the box to the minimum and maximum in every case. In the usual box-plot interpretation, they reach the most extreme observations still within 1.5 times the IQR of the relevant quartile; observations farther away are plotted as outliers when that option is enabled. For example, with the sorted values 2, 3, 4, 5, 6, 7, 8, 9, and 30, the value 30 sits well above the rest and is likely to appear as an outlier. The upper whisker then ends at the highest non-outlier value, rather than at 30. Confirm the chart’s selected quartile method before comparing its details with another calculation.

Improve readability with chart and axis formatting

Use the Chart Design and Format tabs when the chart is selected to add or edit chart elements, change its style, and adjust its layout. Chart Design > Add Chart Element provides options for elements such as a chart title and axis titles. Give the vertical axis a unit, such as Minutes or Test score, if the scale is not self-explanatory. A useful title should identify both the measure and the comparison, such as “Delivery time by region.”

For a crowded chart, select the vertical axis, right-click, and choose Format Axis. Set sensible minimum and maximum bounds or major units when the automatic scale makes differences hard to see. Avoid cutting off meaningful variation: a truncated scale can exaggerate small differences between boxes. If the data contain negative values, make sure the chosen axis bounds include them.

Consider these practical choices when polishing a chart:

Use a readable font size and shorten long category labels where possible.
Keep gridlines light so they support comparisons without overpowering the data.
Use the same axis scale when comparing separate charts; different scales can make similar groups look different.
Show inner points only when readers benefit from seeing the individual observations and the markers do not obscure the boxes.
Include a note about the quartile convention if the chart is part of a technical or formal report.

Choose between a box plot and other charts

A box plot is designed to compare medians, quartiles, spread, and possible outliers. A column chart is better for comparing totals or averages when those are the intended measures. A histogram shows how observations are distributed across numeric intervals, which can reveal clusters or gaps that a box plot hides. A scatter plot is often more suitable when each observation has a meaningful paired value, such as temperature and energy use.

These charts answer different questions. A box plot can show that one group has a wider middle spread, but it does not reveal whether the values form two separate clusters. A mean marker can add context, but it does not turn the chart into a full display of the distribution. When a decision depends on the shape or frequency of values, pair the box plot with a histogram or a table of counts rather than expecting one chart to show everything.

Common formatting problems and fixes

If Excel inserts an unexpected number of boxes or uses the wrong labels, inspect the selected range and try Chart Design > Switch Row/Column. Headings should be included once, and totals or unrelated columns should be excluded. If a category name appears as a data value, the header may not have been recognized; rebuild the chart with the heading row included or edit the source selection.

If the chart has no visible boxes, check that the selected columns contain enough valid numeric observations and that the axis scale includes their values. Text-formatted numbers may be ignored, and blank cells do not stand in for missing measurements. If a point seems to disappear, check whether outlier points or inner points are turned off, and inspect the axis bounds. A value outside the displayed axis range can be clipped from view even though it remains in the data.

If your quartiles do not match a worksheet calculation, first compare the chart’s inclusive/exclusive setting with the calculation method you used. Then check that the same observations are included, that blanks and text have not changed the sample, and that both calculations use the same units. A difference does not necessarily mean the chart is faulty: different quartile conventions can produce different results for a small dataset.

Frequently Asked Questions

Can Excel make a box plot automatically?
Yes. In Excel for Microsoft 365 on Windows, select the numeric data and headings, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel creates the chart from the selected data, and you can adjust the series settings afterward.

How do I show outliers in an Excel box plot?
Right-click a box, choose Format Data Series, and turn on Show outlier points. Excel displays qualifying observations beyond the whiskers as separate markers. If the option is on but no markers appear, the data may have no values identified as outliers under the selected quartile calculation.

Why are the whiskers not at the minimum and maximum?
In a box plot, whiskers generally end at the most extreme values that are not treated as outliers, rather than always spanning the full range. Values beyond the whiskers can appear separately as outlier points. This is why the top whisker may stop below the largest observation.

Can I change the color of one box by itself?
Excel’s standard box-and-whisker series formatting generally applies to the series, so a fill change may affect all the boxes. If individual group colors are essential, check the options available in your Excel version and consider a different chart design or carefully prepared custom chart; do not assume a series-wide setting changes only one category.

Does Excel’s box plot show the average?
It can. In Format Data Series, select Show mean markers or Show mean line. The mean is different from the median: extreme values can pull the average toward one end of the distribution, while the median marks the middle observation or midpoint of the middle values.

Check the chart before sharing it

Before publishing or sending a workbook, compare the chart categories with the source columns, confirm the axis units and scale, and make sure the intended points and mean indicators are visible. Add a short note if readers need to know the quartile method or how missing values were handled. These checks make a box plot easier to interpret and reduce the risk that a polished chart communicates the wrong comparison.

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.