How to Format a Box and Whisker Plot in Microsoft Excel

To format a box and whisker plot in Excel, select the chart, right-click a box, and choose Format Data Series. From the pane, adjust quartile calculation, show or hide points and mean markers, and change the series fill. Then format the title, axes, labels, and colors so the chart is easy to interpret. Start with numeric observations arranged in columns, one column per group.

What You’ll Need

Excel for Microsoft 365 on Windows. The instructions below use its desktop ribbon; Excel for Mac has similar chart tools, though ribbon placement can vary. Excel for the web may offer fewer box-and-whisker formatting controls, so switch to the desktop app if an option is missing.
A table of numeric observations, with a separate column for each group you want to compare and a clear header above each column. Groups can have different numbers of observations.
Optional: category names, units, and a short description of what the measurements represent. These help you add meaningful titles and axis labels.

Step-by-Step

Arrange and check the source data. Put each group in its own column, with the group name in the first row. For example, use columns called Before, After, and Control, with one measurement per cell below each header. Keep the actual observations numeric; do not include words such as cm or seconds in the data cells. Put units in an axis title instead. Remove accidental spaces, notes, and totals from the numeric range. Blank cells are acceptable when groups have different sample sizes, but avoid entering zero to represent a missing observation: Excel will treat zero as real data and it can change the quartiles and whiskers.
Select the complete data range. Include the headers and all observations in the columns you want to compare. If the data are separated by unrelated columns, select the groups one at a time or copy them into a clean adjoining range first. Including headers helps Excel label each box; including a title row above the headers can sometimes confuse the chart’s category labels.
Insert the chart. On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker. Excel creates a chart with a box for each data series. If it plots your groups in the wrong direction, select the chart and choose Chart Design > Switch Row/Column. The goal is one box per group, not one box per observation.
Give the chart a useful title. Select the chart title and replace the default wording with a short description, such as Comparison of Delivery Times by Route. If the title is missing, use Chart Design > Add Chart Element > Chart Title > Above Chart. A good title identifies what is measured and, when helpful, the groups or time period. Avoid relying on a file name or a vague title such as Results.
Open the series formatting pane. Right-click one of the boxes and choose Format Data Series. If the first click selects an individual point rather than the full series, click the box again or use the chart’s Format tab and its Current Selection list to select the data series. The pane contains the options for box-and-whisker statistics as well as appearance.
Choose a quartile calculation deliberately. In Series Options, find Quartile Calculation and choose Inclusive Median or Exclusive Median. These methods can produce different quartile boundaries, especially with small data sets. Inclusive and exclusive refer to how the median is handled when calculating quartiles; neither setting is automatically right for every analysis. Use the convention required by your class, report, or organization, and apply the same method to every group in the chart. If you are comparing your result with another program, check that it uses the same quartile convention.
Decide which points and summary markers to show. In the same pane, turn on or off Show inner points, Show outlier points, Show mean markers, and Show mean line, as appropriate. Inner points show observations within the whisker range; outlier points show observations beyond it. A mean marker adds the average, which is different from the median line inside the box. Showing every point can help readers see sample size and spread, but a large data set may look crowded. You can leave the mean line off if a single marker is clearer.
Adjust the box spacing and fill. In Series Options, adjust Gap Width if the boxes look too narrow or too crowded. A smaller gap generally makes boxes wider; a larger gap gives more space between groups. To change the appearance, use the pane’s Fill & Line controls. A restrained solid fill can distinguish groups, while a clear outline keeps the box edges and median visible. If all groups share one series, changing the series fill may apply the same color to every box. To emphasize only one group, select that individual data point and format it separately.
Format the value axis. Right-click the vertical value axis and choose Format Axis. Set bounds or units only when they improve readability and do not hide relevant data. For example, a sensible minimum can reduce empty space when values do not approach zero, but it can also exaggerate apparent differences. Check that all whiskers and outlier markers remain within the displayed range. Use a number format that matches the data, such as a suitable number of decimal places, and keep units consistent with the source values.
Add labels and simplify the layout. To label the axes, choose Chart Design > Add Chart Element > Axis Titles and select the primary horizontal or vertical title. Name the measured quantity and include its unit, such as Response time (ms). Keep group names short enough to read; if they are lengthy, widen the chart or adjust the source labels. Remove a redundant legend by selecting it and pressing Delete. Use gridlines sparingly, and make sure they do not overpower the boxes. For color and font refinements, use Chart Design > Change Colors or the formatting pane, then inspect the chart at the size at which it will be presented.
Review what the chart communicates. Compare each box’s median, interquartile range, whiskers, and any displayed points with the source data. In a standard box-and-whisker chart, the box represents the middle half of the data, the line inside it is the median, and whiskers extend to the most extreme values within the chart’s outlier rule. Points beyond the whiskers are shown as outliers when that option is enabled. They are not automatically errors. Confirm that the quartile method, group names, units, and display options are clear before sharing or printing the workbook.

Common Mistakes

Putting all groups in one column. Excel needs to know which observations belong to each group. A single column containing values from several categories may produce one box rather than a useful comparison. Put each group in a separate column and use a header for each.

Using zeros for missing observations. A zero is a valid numeric value, not a blank. It can pull down a group’s distribution and change the box, median, and whiskers. Leave missing entries blank, and document how missing data were handled if the chart is part of a report.

Assuming whiskers always show the minimum and maximum. In Excel’s box-and-whisker chart, the whiskers generally reach the most extreme observations that are not classified as outliers under the chart’s rule. Outlying values may appear as separate points. Do not describe the whiskers as the full range unless the plotted data support that interpretation.

Changing quartile methods without noting it. Inclusive and exclusive calculations can move the box edges, so charts made with different settings may not match even when their source observations are the same. Choose a method consistently and mention it when the convention matters.

Over-formatting the chart. Too many colors, heavy gridlines, and unnecessary legends make it harder to compare distributions. Prioritize readable category names, a descriptive title, and a clear value scale over decorative effects.

Tips

Keep the raw observations in the workbook rather than typing summary statistics into a chart. Excel uses the observations to calculate the distribution.
Use the same value-axis scale when comparing separate charts. Different scales can make similar distributions appear dramatically different.
Use one color for all groups if the categories have no meaningful color coding. Reserve distinct colors for groups that readers need to track, and make sure the boxes remain distinguishable in grayscale.
For a small sample, consider showing inner points so readers can see the observations rather than infer too much from a compact box. State the sample size in a nearby note if it is important to interpretation.
To reuse a polished chart, copy it and then change its source data rather than rebuilding all the formatting. Check the new chart’s quartile setting and axis bounds after changing the data.

Troubleshooting

The Box and Whisker option is missing. Confirm that you are using a desktop edition of Excel that supports this chart type, and that you selected a regular worksheet range. In Excel for the web, open the workbook in the desktop app if the statistical chart option or needed formatting controls are not available. On a Mac, look under the Insert tab for the statistical chart choices; names and ribbon layout can differ slightly by release.

Excel creates one box instead of one per group. Check that each group occupies its own column and has a header. Select the whole block, including all group columns, then use Chart Design > Switch Row/Column to try the other orientation. If Excel treats the rows as series, the switch often resolves it. If not, rebuild the chart from a clean table with one group per column.

Category names are missing or wrong. Make sure the header cells are included in the selection and contain text. If the chart is already made, right-click it and choose Select Data to review the chart’s source range and series names. Update the range or name references, then check whether the category labels now match the headers.

The boxes or whiskers look unexpected. First inspect the source values for text stored as text, unintended zeros, blank cells, or a misplaced header. Then check the quartile calculation and whether the mean, inner-point, and outlier-point options are on. Try sorting a copy of the source values to spot unusual entries, but remember that sorting is not required for the chart. A real extreme observation can produce a separate outlier marker; investigate it rather than deleting it just to make the chart look neater.

The chart is hard to read or some values disappear. Widen the chart, shorten category labels, and check the value-axis bounds in Format Axis. Reset bounds to automatic if a manual limit clips data. Increase text size if the chart will be printed or placed in a presentation, and reduce unnecessary gridlines. If the pane seems to show the wrong controls, click the chart element you intend to format and reopen the relevant formatting pane.

Frequently Asked Questions

What does a box and whisker plot show?
The box shows the middle half of the observations, with a line marking the median. Whiskers show the non-outlier extent under the chart’s rule, and separate markers can show outliers. Optional mean markers show the average, which is not the same as the median.

Can groups have different numbers of values?
Yes. Put each group in a separate column and leave unused cells blank beneath shorter groups. Do not fill those cells with zeros, because Excel treats zeros as observations.

Should I choose Inclusive Median or Exclusive Median?
Choose the method required by your reporting standard or analysis, and use it consistently. The choices can produce different quartile values, particularly for small samples. If you are comparing Excel with another tool, confirm that the other tool uses the same convention.

Can I show the actual data points?
Yes. Right-click the series, choose Format Data Series, and enable Show inner points. Enable Show outlier points to display observations classified as outliers. These options can make a chart busy when there are many observations.

Can I change the color of just one box?
Often, yes. Select the individual box or data point rather than the whole series, then use Format Data Point and its fill controls. If Excel selects the whole series, click the target box again before applying the color. Keep any special color meaningful and explain it if readers might otherwise assume it represents a separate category.

Does formatting change the underlying data?
No. Chart formatting changes how Excel displays the data, not the source observations. However, selecting a different quartile calculation changes the statistical summary shown in the chart. Keep the original data and note any analysis convention that affects interpretation.

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.