Excel Bar and Whisker Chart: How to Create and Read One

An Excel bar and whisker chart, more commonly called a box-and-whisker chart or box plot, summarizes the distribution of numeric data using quartiles, a median, and whiskers. In Excel for Microsoft 365 on Windows, select your grouped data, then choose Insert > Insert Statistic Chart > Box and Whisker.

What a box-and-whisker chart shows

Despite the phrase bar and whisker, this chart is not a standard bar chart with a bar for each value. Each box represents a group of observations. The box spans the middle half of the data, a line inside it marks the median, and the whiskers extend toward the lower and higher values. Excel can also show individual outliers, the mean, and inner data points.

The chart is useful when you want to compare distributions rather than just totals or averages. For example, a teacher might compare test scores across several classes, or a shop might compare delivery times across regions. A box plot makes differences in typical values and spread visible even when each group contains many observations.

It compresses data, so it does not show every value by default. Two groups can have similar boxes but very different patterns among their individual observations. Use it as a summary, not a replacement for checking the underlying data when individual cases matter.

Prepare the data in a chart-friendly layout

Put each group in its own column, with a descriptive heading in the first row and numeric observations below it. For example, columns named North, South, and West could contain delivery times for those regions. The columns do not need to contain the same number of observations, though blank cells and nonnumeric entries should be reviewed so the chart reflects the intended data.

Use one row per observation. Do not pre-calculate a mean, median, or quartiles and expect Excel to build the same chart from those summaries: a box-and-whisker chart needs the individual data values to calculate the distribution. Keep units consistent within a group, and avoid mixing text labels into the numeric range.

Include clear headers so Excel can label each distribution.
Keep each observation as a number in a single group column.
Check for accidental blanks, text-formatted numbers, and values recorded in different units.
If your groups are arranged in rows instead, select the data and use Chart Design > Switch Row/Column if Excel assigns the series in the wrong direction.

For instance, a small sample table might have a Product A heading above its measured lifetimes and a Product B heading above its own. Each column becomes one box in the chart, letting readers compare the two groups on the same scale.

Create the chart in Excel for Microsoft 365

On Windows, select the data range, including the group headings. Open the Insert tab, choose Insert Statistic Chart in the Charts area, and select Box and Whisker. Excel inserts a chart with a separate distribution for each recognized series. If you do not see the expected grouping, check whether the data is arranged by columns or rows and try Chart Design > Switch Row/Column.

Select the complete table, including headers and numeric observations.
Choose Insert > Insert Statistic Chart > Box and Whisker.
Check that each box represents the intended group and that the axis labels and units make sense.
Use Chart Design to add a chart title, axis titles, or other chart elements, and use Format to change appearance.

On Excel for Mac, the chart is also available from the Insert tab in supported desktop versions, although the chart menu labels and ribbon arrangement can vary slightly. If the command is not visible, look among the statistical chart types or check that Excel is up to date. Excel for the web may have different chart-creation and formatting capabilities from the desktop app; a workbook containing the chart can still be opened there, but use desktop Excel if a needed option is unavailable.

Read the median, box, whiskers, and outliers

The line inside the box is the median: half of the observations fall at or below it and half at or above it. The bottom and top edges of the box mark the first and third quartiles. The difference between those quartiles is the interquartile range, or IQR, covering the central 50 percent of the observations. A taller box means that central half is more spread out.

Excel commonly draws whiskers to the lowest and highest observations that are not classified as outliers under the 1.5-IQR rule. Values beyond the fences—1.5 times the IQR below the first quartile or above the third—can appear as individual points when outlier points are shown. The whiskers therefore do not necessarily reach the actual minimum and maximum. Check the chart’s options before describing their endpoints.

Consider delivery times of 10, 12, 13, 14, 15, 17, 18, 20, 22, and 60 minutes. The median is 16 minutes, while the mean is 20.1 minutes because the 60-minute delay pulls the average upward. With the inclusive quartile calculation, the first quartile is 12.25 and the third is 19.5; the upper 1.5-IQR fence is 30.375. The 60-minute observation is beyond that fence, so it can be shown as an outlier while the upper whisker ends at 22. That point signals an unusual observation, not automatically a data-entry mistake.

When comparing groups, look at several features together. A higher median suggests a higher typical value; a wider box indicates more variability in the central half; and whiskers or isolated points reveal more of the tails. Overlapping boxes do not, by themselves, establish whether a difference is statistically significant.

Quartile settings and the meaning of whiskers

Quartiles are not calculated identically by every software package or every statistical convention, especially for small samples. Excel provides an inclusive or exclusive median calculation for its box-and-whisker chart. The choice can change the box edges and the IQR, which may also change whether a value qualifies as an outlier. For a large dataset the visual difference is often modest, but for a small group it can matter.

To inspect the options, select a box in the chart, right-click, and choose Format Data Series. In the Format Data Series pane, look for Series Options. Depending on the available controls, you can change the quartile calculation and choose whether to show outlier points, inner points, a mean marker, or a mean line. The precise labels can vary by Excel build.

Do not treat the outlier rule as a universal definition of an error. It is a visual convention for flagging values distant from the central spread. A long delivery time might represent a genuine disruption; a very high measurement might be valid for a particular subgroup. Investigate the source and context before removing or correcting a point.

Format the chart for clear comparisons

Start with a meaningful chart title and label the value axis with its units, such as minutes, dollars, or millimeters. If groups have long names, make the chart wide enough for the category labels to remain readable. Keep the same scale when comparing charts side by side; independently scaled axes can make similar distributions look very different.

In Excel, select the chart and use Chart Design > Add Chart Element to add available titles or other elements. To change the series settings, select a box and open Format Data Series. Use restrained colors: a distinct fill for each group can help, but excessive color can distract from medians and spread. If the mean is important, enable its marker and explain that it is the average, not the median.

You can also turn on inner points to reveal observations inside the whiskers. This can be helpful with a small dataset, where showing the raw values gives readers a better sense of clustering. With a large dataset, many points may overlap, so an uncluttered box with outliers visible may communicate more clearly.

Common problems and how to fix them

If Excel creates one box instead of one per group, first verify the selection includes the headings and all group columns. Then use Chart Design > Switch Row/Column if the groups are being interpreted in the wrong direction. If category names appear as values or the chart looks nonsensical, confirm that observations are numeric and that headers are in the first row or column of the selected range.

If a box or whisker appears missing, the group may have very few observations or little variation. A group with identical values has no visible spread, and a very small sample gives limited information about the distribution. Check the source values and the quartile setting before concluding that the chart is broken.

Unexpected outlier dots may be legitimate values, may result from a different quartile convention than expected, or may reveal a data problem. Compare the plotted group with the source cells and confirm whether outlier points are enabled. If labels are missing, add or correct the header cells and review the chart’s selected data range. Avoid deleting points just to make the chart look neater; document any exclusion and its reason.

When to use this chart instead of another chart

A box plot is a strong choice for comparing spread and medians across several categories, especially when each category contains many observations. A column or bar chart is usually better for comparing totals, counts, or a small set of means. A histogram is better when the shape of one distribution—such as multiple peaks or skew—is the main question. A dot plot can be clearer for small samples because it shows each observation directly.

Box plots can hide details. Different datasets can share the same median and quartiles but have different clusters or gaps. If the audience needs to understand the shape, combine the box plot with a histogram or show individual points. If the goal is to compare averages, decide whether the mean is the right summary and show it explicitly rather than assuming the median line represents it.

Frequently Asked Questions

Is a bar and whisker chart the same as a box-and-whisker chart?
Usually, yes. Box-and-whisker chart is the standard name for the statistical chart Excel provides. It is different from a conventional bar chart, which uses bar lengths to compare values such as totals or averages.

How do I show outliers in an Excel box plot?
Select a box, right-click, choose Format Data Series, and look under Series Options for Show outlier points. When enabled, observations beyond the chart’s outlier fences appear as separate points; they are not automatically removed from the source data.

Why does my Excel box plot not show the minimum and maximum?
The whiskers generally extend to the most extreme observations within the 1.5-IQR fences, not necessarily to the overall minimum and maximum. More extreme values may be plotted separately as outliers. Check the series settings and show outlier points if you want to see those values.

Can I make a box-and-whisker chart from summary statistics?
Excel’s built-in chart calculates its boxes from individual observations. A table containing only a minimum, quartiles, median, and maximum is not the same input as raw data for this chart. If you only have summary statistics, use a charting method designed for those values rather than treating the summaries as observations.

Why do Excel and another program show different quartiles?
Programs can use different quartile conventions, and Excel lets you choose inclusive or exclusive median calculations. Small samples are especially sensitive to that choice. Compare the setting and method before interpreting small differences in box edges or outlier flags.

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.