Excel can make a box and whisker plot in a few clicks, but the steps depend on which version you have

A box and whisker plot shows the spread of your data at a glance — where the middle values cluster, how far the outliers reach, and whether your data is skewed. Excel 2016 and later versions have a built-in box and whisker chart type. If you have an older version, you can build one manually using existing chart tools. Either way, you start with the same thing: a column of numbers you want to visualize.

The plot itself has five parts: the minimum value, the first quartile (25th percentile), the median (50th percentile), the third quartile (75th percentile), and the maximum value. Excel calculates these automatically if you use the built-in chart, or you can calculate them yourself if you are working with an older version.

Key Takeaways

  • Excel 2016 and later have a box and whisker chart type built in; you select your data and insert it directly from the Charts menu.
  • Older versions of Excel require you to calculate quartiles manually using the QUARTILE function, then build the plot from a stacked column chart.
  • Your data should be in a single column with no headers mixed in, and any blank cells will cause Excel to skip that row.
  • Box and whisker plots work best with datasets of 10 or more values; smaller datasets can be misleading because quartiles are based on position, not frequency.

Using the built-in box and whisker chart in Excel 2016 and later

Open your spreadsheet and put your data in a single column. If your column has a header (like "Sales" or "Test Scores"), include it — Excel will recognize it and exclude it from the calculation. Select the entire column of numbers, including the header if you have one.

Go to the Insert tab at the top of the ribbon. Click Charts, then look for the Stock chart category (it is grouped with other statistical charts). Inside that dropdown, you will see Box and Whisker. Click it. Excel will generate the plot when ready. The chart appears as an object on your sheet; you can move it, resize it, or right-click to edit the title and axis labels.

If you do not see a Box and Whisker option, you have an older version of Excel. Skip to the next section.

Building a box and whisker plot manually in Excel 2013 and earlier

Older versions do not have a built-in box and whisker chart, but you can create one using a stacked column chart and the QUARTILE function. Start by calculating the five values the plot needs: minimum, Q1, median, Q3, and maximum.

In a new area of your spreadsheet, create labels for these five values. In the row next to each label, use these formulas (replace A2:A100 with your actual data range):

  • Minimum: =MIN(A2:A100)
  • Q1: =QUARTILE(A2:A100,1)
  • Median: =QUARTILE(A2:A100,2)
  • Q3: =QUARTILE(A2:A100,3)
  • Maximum: =MAX(A2:A100)

Excel will calculate each value. Write these results down or keep them visible on your sheet — you will reference them to build the chart.

Now create a helper table for the stacked column chart. You need two columns: one for the segment names and one for the heights. The segments are: minimum to Q1, Q1 to median, median to Q3, Q3 to maximum. Calculate the height of each segment by subtracting the lower value from the upper value. For example, if minimum is 10 and Q1 is 25, the first segment height is 15.

Select your helper table, go to Insert, choose Column Chart, and pick the Stacked Column option. The result will look like a box, but it will not have whiskers yet. To add whiskers, right-click the chart, select Edit Data, and add two more data series for the lower whisker (minimum to Q1) and upper whisker (Q3 to maximum). Format these as thin lines or very narrow columns to create the whisker effect. This method is more work, but it produces the same visual result.

What your box and whisker plot actually shows you

The box itself represents the middle 50 percent of your data — everything between Q1 and Q3. The line inside the box is the median, which is the exact middle value when all your numbers are sorted. If the median line is off-center in the box, your data is skewed toward one end.

The whiskers extend from the box to the minimum and maximum values. If you have extreme outliers, some versions of Excel will plot them as individual dots instead of extending the whisker all the way — this is a visual choice to keep the chart readable. The whiskers tell you the full range of your data, while the box tells you where most of it clusters.

Common mistakes that break the plot

The most common error is including text or blank cells in your data range. If your column has a mix of numbers and text, Excel will either skip the text rows or throw an error. Clean your data first: remove any rows that are not pure numbers, and delete any blank cells within your range.

Another mistake is selecting too small a dataset. Box and whisker plots rely on quartiles, which are position-based. With fewer than 10 values, the quartiles can be misleading because they are calculated from the position of values, not their frequency. A dataset of 5 numbers will still produce a plot, but it may not tell you much.

If you are building the plot manually and your whiskers look wrong, double-check your QUARTILE formulas. The second argument in QUARTILE should be 1 for Q1, 2 for median, and 3 for Q3. Using 0 or 4 will give you the minimum and maximum instead, which is not what you want in the middle of the calculation.

Customizing the appearance of your plot

Once your chart exists, right-click it and select Edit Chart (or Format Chart Area depending on your version). You can change the title, axis labels, colors, and font size. The box itself can be formatted by right-clicking the box shape and choosing Format Data Series.

If you want to compare multiple datasets side by side, create a separate column for each dataset and select all of them before inserting the chart. Excel will create multiple boxes on the same plot, making it straightforward to see which dataset has more spread or a higher median.

When a box and whisker plot is the right choice

Use a box and whisker plot when you want to show the distribution of a single variable across your data, or when you want to compare distributions across groups. They are especially useful for spotting skewed data, identifying outliers, and comparing the spread of different datasets without getting lost in individual values.

They are less useful if you have very few data points (under 10) or if you need to show exact values — a box and whisker plot hides individual numbers by design. For that, a scatter plot or a histogram might be better. But for a quick visual summary of where your data sits and how spread out it is, a box and whisker plot is hard to beat.

Frequently Asked Questions

What if my data has negative numbers?

Box and whisker plots handle negative numbers without any problem. The QUARTILE and MIN/MAX functions work on negative values the same way they work on positive ones. Your plot will straightforward extend into the negative side of the axis.

Can I make a box and whisker plot from data in multiple columns?

Yes. If you have separate columns for different groups or categories, select all the columns at once before inserting the chart. Excel will create a box for each column, arranged side by side, so you can compare them visually.

Why does my plot show dots instead of whiskers at the top or bottom?

Excel treats extreme outliers differently depending on your version and settings. If a value is more than 1.5 times the interquartile range away from Q1 or Q3, Excel may plot it as a dot instead of extending the whisker. This is intentional — it keeps the chart readable when you have very extreme values. The dots are still part of your data; they are just highlighted separately.

Do I need to sort my data before making the plot?

No. Excel calculates quartiles and min/max values regardless of the order your data is in. Sorting is not required, though it can help you spot errors or unusual values when you are checking your data for quality.

What is the difference between QUARTILE and PERCENTILE in Excel?

QUARTILE divides data into four equal parts (0, 25, 50, 75, 100 percent). PERCENTILE lets you specify any percentile you want. For a box and whisker plot, QUARTILE is simpler because you only need the standard four divisions. If you want to show the 10th or 90th percentile instead, use PERCENTILE, but that is not standard for this type of chart.