What a box and whisker plot shows you

A box and whisker plot displays the spread of your data in five numbers: the lowest value, the 25th percentile, the median (50th percentile), the 75th percentile, and the highest value. The box holds the middle half of your data, the line inside the box marks the median, and the whiskers extend to show the full range. Excel does not have a built-in box plot tool in older versions, but Excel 2016 and later include one under the Insert menu, and you can also build one manually using a stacked bar chart if you need it in an earlier version.

Use a box plot when you want to compare how spread out different groups are, or when you want to spot outliers — values that sit far outside the normal range. A single histogram shows you the shape of one dataset. A box plot lets you line up three or four datasets side by side and see at a glance which one has more variation, where the middle values cluster, and whether any values look suspiciously high or low.

Key Takeaways

  • Excel 2016 and later have a built-in box plot option under Insert > Charts > Box and Whisker.
  • Your data should be arranged in columns, with each column representing one group or category you want to compare.
  • The plot automatically calculates the five-number summary and marks any outliers as individual points beyond the whiskers.
  • If you use an older version of Excel, you can build a box plot manually using a stacked bar chart and calculated percentile values.

Preparing your data for a box plot

Arrange your data so that each column contains one group. If you are comparing test scores from three classrooms, put classroom A scores in column A, classroom B in column B, and classroom C in column C. Each value goes in its own cell, one per row. You do not need to sort the data — Excel will do that for you — but it helps to add a header row with the group name at the top of each column so the chart labels itself correctly.

If your data is in rows instead of columns (for example, one row per person with their scores across multiple tests), you will need to transpose it first. Select all your data, copy it, right-click on an empty area, choose Paste Special, and check the Transpose box. This flips rows to columns so the chart will read it correctly.

Remove any cells that are completely empty within your data range. If you have missing values, leave them blank rather than entering zero, because zero is a real number and will skew your plot. Excel ignores truly empty cells when calculating percentiles.

Creating the plot in Excel 2016 or later

Select all your data, including headers. Click the Insert tab at the top. In the Charts group, click the dropdown arrow next to the chart icons and look for Box and Whisker (it may be under a "Statistical" or "All Charts" submenu depending on your Excel version). Click it, and Excel creates a default plot when ready.

The plot shows one box for each column of data. The box spans from the 25th percentile to the 75th percentile. The orange line inside each box is the median. The whiskers (thin lines extending left and right) reach to the lowest and highest values, unless those values are outliers. Any point that sits more than 1.5 times the interquartile range beyond the box appears as a dot instead of being included in the whisker — this is how Excel flags unusual values.

To change the plot's appearance, right-click on it and choose Format Chart Area. You can change colors, add a title, adjust the axis labels, and remove the legend if you only have one group. Double-click the chart to enter edit mode, then click once on any element (a box, a whisker, the background) to select just that part and format it separately.

Building a box plot manually in older Excel versions

If you have Excel 2013 or earlier, you can create a box plot using a stacked bar chart and calculated percentile values. First, calculate the five numbers for each group using formulas. In a new area of your spreadsheet, create a row for each percentile: minimum, 25th percentile, median, 75th percentile, and maximum.

For the minimum, use =MIN(A:A) where A is your data column. For the 25th percentile, use =QUARTILE(A:A,1). For the median, use =MEDIAN(A:A). For the 75th percentile, use =QUARTILE(A:A,3). For the maximum, use =MAX(A:A). Repeat these formulas for each group in separate columns.

Next, create a helper table that converts these five numbers into four segments: the distance from minimum to the 25th percentile, the distance from 25th to median, the distance from median to 75th, and the distance from 75th to maximum. For example, if your minimum is 10 and your 25th percentile is 20, the first segment is 10. If your median is 35, the second segment is 15. Build this table for each group.

Select this helper table and insert a stacked bar chart. The chart will show four colored bars stacked horizontally, one for each segment. The visual effect approximates a box plot: the middle two segments form the box, and the outer two form the whiskers. This method is more work than using the built-in tool, but it works in any Excel version.

Interpreting what the plot tells you

A wide box means the middle half of the data is spread out. A narrow box means the middle half is clustered tightly around the median. If the median line is off to one side of the box rather than in the center, your data is skewed — more values bunch up on one end than the other.

Long whiskers mean your data has a wide range from lowest to highest. Short whiskers mean most values cluster near the middle. Dots beyond the whiskers are outliers — values that are unusually high or low compared to the rest. These are worth investigating: they might be data entry errors, or they might be real but exceptional cases.

When comparing multiple groups side by side, look for differences in box width (variation), median position (central tendency), and whisker length (range). If one group's box is much wider than another's, that group has more variability. If the medians line up but the boxes are different sizes, the groups have similar central values but different spreads.

Common mistakes and how to fix them

The most common mistake is arranging data in rows instead of columns. If your chart shows only one box when you expected three, transpose your data so each group is in its own column. Another frequent error is including text or blank cells in the middle of your data range. Excel will either skip those columns or treat them as separate groups, creating unexpected boxes.

If your chart looks squashed or the whiskers are invisible, the scale of your axes might be wrong. Right-click on the axis numbers and choose Format Axis. Adjust the minimum and maximum values so the full range of your data is visible. If you have one or two extreme outliers that make the rest of the plot hard to read, consider whether those values are errors. If they are real, you may need to use a logarithmic scale instead of a linear one — right-click the axis and choose Format Axis, then select Logarithmic Scale.

If you are building a manual box plot in an older version and the segments do not add up correctly, double-check your percentile formulas. The QUARTILE function sometimes behaves differently depending on your Excel version. Test it on a small dataset where you can calculate the percentiles by hand and verify the formula is working.

When to use a box plot instead of other charts

A box plot is better than a histogram when you want to compare multiple groups at once. A histogram shows the shape of one distribution in detail; a box plot summarizes five groups in a single glance. A box plot is better than a bar chart when you care about spread and variation, not just the average. A bar chart might show that two groups have the same average but hide the fact that one group is much more consistent than the other.

A box plot is worse than a scatter plot if you want to see the actual individual data points, or if you have fewer than five values per group (percentiles become meaningless with tiny samples). A box plot is also less useful than a line chart if your data has a time sequence — if you are tracking how something changes month by month, a line chart shows the trend more clearly than a box plot does.

Frequently Asked Questions

What is the difference between the whiskers and the outlier dots?

The whiskers extend to the highest and lowest values that fall within 1.5 times the interquartile range of the box. Any value beyond that distance appears as a dot instead. This visual separation helps you spot unusual values without letting a single extreme outlier stretch the whiskers so far that you cannot see the rest of the data.

Can I change which percentiles the box shows?

The standard box plot always shows the 25th, 50th, and 75th percentiles. Excel's built-in tool does not let you change these. If you need different percentiles (for example, the 10th and 90th instead of 25th and 75th), you must build the plot manually using a stacked bar chart and your own calculated values.

What should I do if I have missing data in one group?

Leave those cells blank. Excel's percentile functions ignore empty cells and calculate based only on the values that are present. If you enter zero instead, it will be treated as a real data point and will lower your median and percentiles.

How do I add a title and axis labels to my box plot?

Double-click the chart to enter edit mode. Right-click on the chart and choose Add Chart Element. Select Chart Title and type your title. Select Axis Titles to add labels to the vertical and horizontal axes. You can also right-click directly on any axis number to format how those labels appear.

Can I make a box plot with data from different sheets?

Yes. When you select your data, you can hold Ctrl and click ranges from different sheets. Excel will treat each range as a separate group. Make sure each range has the same number of rows, or Excel may misalign them.