Vertical Box and Whisker Plot in Excel: How to Make One

Yes. In Excel for Microsoft 365 on Windows, select your data, then choose Insert > Insert Statistic Chart > Box and Whisker to create a vertical box-and-whisker plot. Put each group in its own column, with a group name at the top and its numeric observations underneath; Excel places the groups along the horizontal axis and the measured values on the vertical axis.

What a vertical box-and-whisker plot shows

A box plot compresses a distribution into a few useful landmarks, making it easier to compare groups than a list of numbers alone. The box runs from the first quartile (Q1) to the third quartile (Q3), so it contains the middle half of the observations. A line inside the box marks the median. The box’s height is the interquartile range, or IQR, which is Q3 minus Q1.

In Excel’s standard box-and-whisker chart, whiskers extend to the smallest and largest values that are not classified as outliers. Values beyond the whiskers can appear as individual points. The exact point at which Excel treats a value as an outlier is based on the 1.5-times-IQR rule. A box plot is therefore not simply a bar chart with error bars: it represents the spread and shape of a distribution, not just a total or average.

For example, a chart comparing delivery times for three carriers could show each carrier as one box. A taller box means the middle half of delivery times is more spread out. A high median suggests a longer typical delivery, while isolated points beyond a whisker may indicate unusually long or short deliveries. The chart does not explain why those observations occurred; it helps identify where to investigate.

Prepare the worksheet data

Arrange the observations in separate columns. Use a clear heading in the first row and put one measured value in each cell below it. Each column becomes a category in the chart. The groups do not need to contain the same number of observations: a shorter column can simply have empty cells beneath its last value.

For instance, a worksheet might have the heading Group A in A1 and its measurements in A2:A11, with Group B in B1 and its measurements in B2:B9. Select the complete range, including the headings and the blank cells within the rectangular range if necessary. Excel generally ignores empty cells, but a zero is a real numeric value and affects the distribution. Do not enter 0 to mean missing data.

Keep one observation per cell; do not put comma-separated values in one cell.
Use consistent units across all groups, such as seconds for every observation.
Check that values stored as text have been converted to numbers, especially after importing data.
Keep explanatory notes and totals outside the selected measurement columns so Excel does not interpret them as observations.

Unequal sample sizes are allowed, but sample size still matters when interpreting the result. A box based on five observations can look comparable to one based on fifty, even though the smaller group provides less information about its distribution. Consider showing sample counts in labels or nearby cells when that context is important.

Create the chart in Excel for Windows

Arrange each group in a separate column and include a descriptive heading above each column.
Select the headings and all observation cells. If the data is in nonadjacent ranges, it is often simpler to place it in a clean rectangular table first.
On the ribbon, choose Insert > Insert Statistic Chart > Box and Whisker.
Review the category labels and chart orientation. If Excel has treated rows as groups instead of columns, select the chart and choose Chart Design > Switch Row/Column.
Add a meaningful chart title and axis titles using Chart Design > Add Chart Element. Label the vertical axis with the measurement and its units.

The command names can vary slightly with ribbon layout or Excel build. If Box and Whisker is not available in the Insert Statistic Chart menu, check that you are using a desktop edition of Excel that supports this chart type; it is available in Excel 2016 and later, including Microsoft 365. A workbook opened in a browser or an older Excel release may not offer the same chart-creation controls. Excel for Mac has statistical chart support in modern releases, though ribbon placement can differ from Windows.

After insertion, click a box and open Format Data Series to adjust the chart’s statistical display. On Windows, you can usually right-click a box and choose Format Data Series. The options are applied to the series, so changes can affect all groups in that chart rather than just one box.

Understand quartiles and Excel’s settings

Quartiles divide ordered data into four parts. The median is the middle value, while Q1 and Q3 mark the lower and upper quartile positions. With an even number of observations, the median is typically the average of the two central values. Different calculation conventions can produce slightly different quartiles, especially for small samples, so two programs may draw boxes that are not identical even when they use the same observations.

Excel’s box-and-whisker series settings include a quartile calculation choice: Inclusive median or Exclusive median. Inclusive and exclusive methods differ in how the median is handled when dividing observations into the lower and upper halves. For a large dataset, the visual difference is often small; with a short list, it can noticeably move a box edge. If you are matching a published report or another software package, confirm which convention it uses and set the Excel chart accordingly.

Here is a small worked example using Group A values 2, 4, 5, 7, 8, 9, 10, 12, 14, and 18 in ascending order. With those ten values in A2:A11, the inclusive quartiles are Q1 = 5.5 and Q3 = 11.5, giving an IQR of 6. In the same layout, this formula returns Q1: =QUARTILE.INC(A2:A11,1). For that range, this formula returns Q3: =QUARTILE.INC(A2:A11,3). To calculate the IQR from the same cells, use =QUARTILE.INC(A2:A11,3)-QUARTILE.INC(A2:A11,1), which returns 6. These formulas calculate statistics in worksheet cells; they do not control the chart’s quartile setting.

For this example, the 1.5-IQR fences are -3.5 and 20.5. None of the observations lies outside those limits, so the whiskers reach 2 and 18 and there are no outlier points. If you change the data or choose another quartile convention, the quartiles and fences may change.

Format outliers, means, and inner points

Excel’s Format Data Series pane offers several choices that change what the chart communicates. Their availability and wording can vary slightly by release, but common settings include:

Show outlier points: Displays individual values beyond the whiskers. This is useful for spotting unusual observations, but a plotted outlier is not automatically an error.
Show inner points: Displays individual observations that fall within the whiskers. This can reveal clustering or repeated values, though a dense dataset may make the chart crowded.
Show mean markers: Adds a marker for the arithmetic mean. The mean can differ from the median when a distribution is skewed or contains extreme values.
Show mean line: Adds a line representing the mean in the box. Use it only when it helps readers distinguish mean from median clearly.
Show outlier points and inner points: Use both when the individual observations matter, such as when a reader needs to see the sample behind each summary.

To change the appearance, select a box, right-click, and choose Format Data Series. Look in Series Options for the quartile calculation and point-display checkboxes. Use the Fill & Line controls to change fill, border, and marker styling. Keep the median line visible and make outlier markers distinct enough to see, but avoid decorative effects that make the summary harder to read.

Make the chart easier to interpret

A well-labeled chart prevents readers from guessing what the values mean. Replace a generic title such as Box and Whisker with a specific title, for example Weekly response time by support team. Add units to the vertical axis, and use category names that are short enough to remain legible. If the category labels are long, widen the chart or shorten the labels in the source table rather than shrinking the text excessively.

Think carefully about the value-axis scale. Starting the axis at zero is not a universal requirement for a box plot; the axis should make the distribution understandable without hiding meaningful variation. However, a very narrow scale can exaggerate small differences between groups. If you set axis bounds manually, make sure no relevant box, whisker, or outlier is clipped. Right-click the vertical axis and choose Format Axis to review the bounds and number formatting.

Use a consistent color scheme if colors carry meaning, such as treatment groups or locations. If color has no analytical purpose, a restrained single-color style often makes comparisons clearer. Avoid using color alone to convey a critical distinction, since charts may be printed in grayscale or viewed by people with color-vision differences.

Common problems and how to fix them

The chart shows the wrong groups: Excel may have interpreted rows as series. Select the chart and use Chart Design > Switch Row/Column, then check that each category has the intended label.
A group appears to have no box: Check whether the column contains enough distinct numeric observations. A group with one value, or with identical values, has little or no visible spread. Also check for numbers stored as text.
Zeros or blanks distort the result: Remove placeholder zeros if they mean missing data. Leave missing observations blank rather than substituting a value that changes the distribution.
Outliers seem incorrect: Confirm the quartile method and inspect the original observations. Excel’s outlier display follows the chart’s statistical rule; it does not know whether a value is a data-entry mistake or a genuine event.
Labels or markers are hard to read: Enlarge the chart, reduce unnecessary gridlines, and simplify long category names. If there are many groups, consider splitting them across charts instead of squeezing every box into one small plot.

A box plot can also hide important features. Two groups may have similar medians and quartiles but very different sample sizes, gaps, or clusters. Showing inner points can help with small datasets. For large datasets, a histogram or a dot plot may reveal distribution details that overlap in a box plot.

Box plot compared with other Excel charts

Choose a box-and-whisker chart when the main question is how distributions compare: which group has a higher median, more variability in its middle half, or more unusually distant values? Choose a column chart when the comparison is between totals or single summary values, such as monthly revenue. A column chart of averages can conceal variation among individual measurements, while a box plot makes spread visible.

A histogram is better for examining the shape of one distribution in detail, including peaks and gaps. A scatter chart is useful when each observation has a paired x- and y-value or when you want to study a relationship between two numeric variables. A line chart is appropriate when order in time is essential. These charts answer different questions; a box plot is not a replacement for every chart that contains numeric data.

If your Excel edition does not offer a box-and-whisker chart, a practical alternative is to calculate summary statistics in cells and build a chart with a more manual method, or use a tool that supports box plots. Manual construction is more work and can be easy to mislabel, so document the quartile convention and whisker rule. For a simple presentation, a table of median, Q1, Q3, minimum, and maximum may be clearer than a workaround chart.

Frequently Asked Questions

Can I make a vertical box plot in Excel?
Yes. Insert a Box and Whisker chart using Insert > Insert Statistic Chart > Box and Whisker. With each group in a separate column, categories appear horizontally and the numeric scale runs vertically.

How do I change a horizontal-looking box plot to vertical?
Excel’s standard Box and Whisker chart uses a vertical value axis. If the categories or data are arranged in an unexpected direction, select the chart and choose Chart Design > Switch Row/Column. Check the axis labels afterward to confirm the groups are correct.

What do the whiskers mean in Excel?
They extend from the box to the most extreme observations that are not classified as outliers under the chart’s 1.5-times-IQR rule. Individual values beyond the whiskers can be shown as outlier points.

Can a box plot compare groups with different sample sizes?
Yes. The columns can have different numbers of observations, and blanks do not need to be filled with zeros. Because box plots do not automatically make sample sizes obvious, include counts when differences in group size affect how readers should interpret the comparison.

Why do Excel and another program show different quartiles?
They may use different quartile conventions, particularly for small samples. Check whether both charts use inclusive or exclusive quartiles and whether their whisker and outlier rules match before treating the difference as an error.

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.