Box Plot in Excel: Create and Read One Step by Step

To create a box plot in Excel for Microsoft 365 on Windows, place each group of raw numeric observations in its own column, select the data, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel draws the chart; you can then format its quartiles, whiskers, mean markers, and outlier points to suit the comparison.

What a box plot shows

A box plot, also called a box-and-whisker plot, summarizes the distribution of a set of numbers. It is useful when you want to compare typical values and spread across groups without displaying every observation individually. For example, a teacher might compare test scores across classes, or a business analyst might compare delivery times across regions.

The box covers the middle half of the observations, from the first quartile (Q1) to the third quartile (Q3). A line inside the box marks the median, or middle value. The distance from Q1 to Q3 is the interquartile range (IQR), a measure of the spread of the central half of the data. A taller or longer box indicates more variation in that central portion.

In Excel’s standard box-and-whisker chart, whiskers reach to the smallest and largest observations that are not classified as outliers. Points beyond the whiskers can appear individually when outlier display is enabled. This means a whisker does not necessarily end at the minimum or maximum value in the data. The chart can also show the mean, which is the arithmetic average, as a marker or line.

Prepare the data in columns

Arrange the original observations by group, with one group per column and a descriptive header at the top. Each column should contain the individual measurements, not precomputed quartiles or averages. For instance, columns headed North, South, and West could contain delivery times for those regions. The columns do not need to contain the same number of observations; leave unused cells blank rather than filling them with zeroes, because a zero would be treated as a real measurement.

Keep the data numeric and check for unintended text, totals, or notes mixed into the observation range. A total at the bottom of a column is not another observation and can distort the chart. If dates or times are the values being compared, use consistent, valid Excel date or time entries. A short, clear header helps identify each box in the finished chart.

If your source data is arranged with group names down the rows rather than across columns, you can still select it and try the chart. If Excel treats the rows and columns in the wrong direction, select the chart and use Chart Design > Switch Row/Column. For a simple comparison, columns are often the easiest arrangement to inspect and maintain.

Create a box plot in Excel

Click a cell in the prepared data, or select the entire range including its headers. Check that the selection includes all groups and observations but excludes unrelated totals.
On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker. Excel inserts a chart based on the selected data.
Review the horizontal labels and the number of boxes. If the chart has one box per observation rather than one per group, use Chart Design > Switch Row/Column.
Click the chart title and type a specific title, such as Delivery Time by Region. Add axis titles if the measurement units would otherwise be unclear.
Right-click a box and choose Format Data Series to adjust the statistical display. The exact controls are described below.

Excel for Mac also offers statistical charts in supported desktop versions, but ribbon placement and labels can differ by release. Look for Box & Whisker under the Insert tab’s statistical chart options. Excel for the web may not expose the same creation and formatting controls as the desktop app, so use Microsoft 365 desktop Excel when the chart type or settings are unavailable in a browser.

Read the boxes, median, and whiskers

Start with the median line. If one group’s median is higher than another’s, its central observation is higher, but that alone does not tell you whether the groups differ meaningfully. Next compare box lengths: a wider box indicates a larger IQR and more spread among the middle half of that group’s values. Unequal box sizes can reveal differences that a comparison of averages would hide.

Then examine the whiskers and any individual points. Excel identifies potential outliers using a common rule based on 1.5 times the IQR: values below Q1 minus 1.5 times the IQR, or above Q3 plus 1.5 times the IQR, are outside the usual whisker limits. The whiskers extend to the most extreme observed values still inside those limits. This is a convention for flagging unusually distant observations, not proof that a value is erroneous. Check the source data and subject matter before deciding what an outlier means.

Consider a small example with the values 4, 5, 6, 7, 8, 9, 10, 11, 12, and 30. Using Excel’s inclusive quartile calculation, Q1 is 6.25 and Q3 is 10.75, so the IQR is 4.5. The upper outlier limit is 17.5, making 30 a potential outlier. The upper whisker reaches 12, the largest value within that limit, rather than 30. The median is 8.5. This illustrates why the dot beyond the whisker is not the same thing as the whisker endpoint.

Choose quartile and display settings

To change the series settings, right-click a box and choose Format Data Series. In the Format Data Series pane, use Series Options to find settings such as Quartile Calculation, Show outlier points, Show inner points, Show mean markers, and Show mean line. Available labels can vary slightly by Excel build. The outlier option controls whether flagged points are drawn; inner points can show individual observations, which may make a chart crowded when a group has many values.

Excel provides inclusive and exclusive quartile calculations. These are two recognized ways to calculate quartiles, and they can produce different box boundaries, especially in small samples. Neither is universally correct for every analysis: use the convention required by your course, organization, or reporting method, and apply it consistently when comparing charts. The setting changes the chart’s summary, not the underlying observations.

For a transparent numerical check, you can calculate the values separately. With the ten example observations in A2:A11, the inclusive first quartile is =QUARTILE.INC(A2:A11,1), which returns 6.25. With the same values in A2:A11, the inclusive third quartile is =QUARTILE.INC(A2:A11,3), which returns 10.75. With the same values in A2:A11, the median is =MEDIAN(A2:A11), which returns 8.5. With Q1 in C2 and Q3 in C3, the IQR is =C3-C2, which returns 4.5. These calculations use the inclusive method; an Excel chart set to exclusive quartiles may show different box edges.

Customize the chart for a clear comparison

A good box plot makes the comparison, units, and sample groups clear without decorating the data into the background. Use a descriptive chart title and category labels, and state units in an axis title or the chart title when appropriate. For example, Delivery time in days is more informative than Results. Keep group colors consistent if the same categories appear in multiple charts, and avoid assigning a different color to every box unless color communicates something meaningful.

Mean markers can help readers compare averages with medians. A large gap between the mean and median may indicate skew or the influence of extreme values, but the box plot does not explain the cause. Showing inner points can be helpful for small samples because readers can see how many observations lie in the distribution. With a large sample, individual points may overlap heavily; turn them off or use a separate chart for detailed observations.

For accessibility and readability, do not rely on color alone to distinguish groups. Use clear labels, adequate contrast, and a chart size that leaves room for category names. If groups have very different sample sizes, consider displaying the number of observations alongside the chart or in a nearby table. A box plot summarizes spread but does not itself communicate sample size.

Common problems and how to fix them

The chart shows the wrong number of boxes: Excel may have interpreted rows as series or columns as categories. Use Chart Design > Switch Row/Column, and verify that each group has its own column of observations.
A group is missing or looks unusually small: Check that its values are numeric and that the selected range includes them. Text-formatted numbers may not behave as expected, and blank cells are not substitutes for missing measurements.
The whiskers do not reach the minimum and maximum: This is often expected. The whiskers stop at the most extreme values within the outlier limits, while observations beyond them appear as separate points when outlier display is on.
Two people get different box boundaries: Compare the quartile calculation setting and the source data. Inclusive and exclusive methods can give different quartiles, particularly with small datasets.
The chart looks cluttered: Turn off inner points, simplify gridlines, enlarge the chart, or split a very large number of groups across several charts. Do not hide outliers simply because they are inconvenient; decide whether their display is appropriate for the analysis.

When a box plot is useful—and what it cannot show

A box plot is a compact option for comparing distributions, especially when a table of individual values would be too long. It can reveal differences in medians, central spread, and potential extremes at a glance. It is often more informative than a bar chart of averages when variation matters. A bar chart can make two groups with the same average look identical even if their distributions are very different.

However, a box plot compresses the data. It does not show every value by default, and it can conceal distribution shapes such as multiple clusters or gaps. Histograms, dot plots, or frequency charts can make those patterns easier to see. When sample sizes are small, individual points alongside the box may be more revealing than the box alone. For important decisions, pair the chart with sample sizes and appropriate descriptive statistics rather than treating one visual summary as a complete analysis.

Frequently Asked Questions

Does Excel have a built-in box plot?
Yes. In Microsoft 365 for Windows, select the data and choose Insert > Insert Statistic Chart > Box and Whisker. If the command is not available in your Excel version, update the desktop app or use a supported version with the chart type.

What do the dots outside the box plot mean?
When Show outlier points is enabled, dots beyond the whiskers represent observations outside the chart’s outlier limits. They are potential outliers under the chart’s statistical rule, not automatically data errors.

Why does the whisker not end at the minimum or maximum?
Excel’s whiskers generally extend to the most extreme observations that remain within the outlier limits. A more extreme value is shown separately as an outlier point when that option is enabled.

Can I make a box plot with groups of different sizes?
Yes. Put each group’s observations in a separate column and leave unused cells blank. Do not fill empty spaces with zeroes, because zero is a numeric observation and changes the distribution.

Should I choose inclusive or exclusive quartiles?
Use the method required by your reporting standard or analysis instructions. If there is no specified convention, choose one method, document it, and use it consistently; quartile methods can differ for small samples.

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.