Box and Whisker Plot in Excel: A Step-by-Step Guide

To build a box and whisker plot in Excel for Microsoft 365 on Windows, arrange each data group in its own column with a heading, select the data, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel creates a chart that summarizes the groups’ medians, quartiles, spread, and possible outliers. Have your numerical data ready in a clean table before you start.

What You’ll Need

Excel for Microsoft 365 on Windows, or a version of Excel that includes the Box and Whisker chart type.
A worksheet containing numerical observations, with one group per column and a heading at the top of each column.
Enough observations to make the summary useful. A box plot can be created from a small sample, but its quartiles and outlier picture are more informative when each group contains several observations.

For example, you might put morning, afternoon, and evening delivery times in columns A, B, and C. Each column should contain the observations for one group. The groups do not have to contain the same number of values: Excel can chart unequal-length columns, provided the unused cells are left blank rather than filled with text or zeroes.

Step-by-Step

Arrange the data by group. Put a descriptive heading in the first row of each column, such as Morning, Afternoon, and Evening. Enter one observation per cell below its heading. Keep the headings in a single row and avoid merged cells, subtotals, blank rows within the data, and notes in the measurement columns. A box plot compares distributions, so every value in a column should represent the same kind of measurement and use the same unit.
Check and clean the values. Make sure the observations are numeric Excel values, not numbers stored as text. Remove accidental spaces, spelling notes, and error values from the measurement range. A genuinely missing observation should usually remain blank; do not replace it with zero unless zero is a real measurement. A zero is treated as data and can pull a group’s distribution down substantially. If your source data is in rows instead of columns, you can still chart it, but confirm the group orientation after creating the chart.
Select the complete table. Drag across the headings and all populated observation cells, including blank cells that fall inside the rectangular range for unequal group sizes. For example, if three group headings are in A1:C1 and the longest group runs to row 21, select A1:C21. Including the headings helps Excel use them as category names. Do not include unrelated totals, explanatory text, or a second table next to the data.
Insert the chart. On the ribbon, select Insert > Insert Statistic Chart > Box and Whisker. Excel adds a chart to the worksheet. If the chart command is not immediately visible, look in the Charts area of the Insert tab for Insert Statistic Chart. Depending on the ribbon width or Excel build, the command may appear inside a chart drop-down. The chart may initially use a default title and style; those can be edited after insertion.
Check that Excel interpreted the groups correctly. Each column heading should appear as a separate category along the horizontal axis, with one box for each group. If Excel has treated rows as groups, select the chart, open the Chart Design tab, and choose Switch Row/Column. Check the result rather than relying on the command alone: the labels and number of boxes should match the way you intend to compare the data. If the chart still looks wrong, delete it and select just the intended table before inserting it again.
Add a useful title and axis labels. Select the chart title and replace it with a specific description, such as Delivery time by shift. To add labels, select the chart and use Chart Design > Add Chart Element > Axis Titles. Name the horizontal axis for the groups and the vertical axis for the measurement, including the unit where helpful, such as Time (minutes). A title and units make the chart easier to understand when it is copied into a report or viewed without the source table.
Review the box, median, and whiskers. The box spans the first and third quartiles, and the line inside it marks the median. The box therefore contains the middle half of the observations. In Excel’s box-and-whisker chart, whiskers extend to the most extreme values within the whisker limits; values beyond those limits can appear separately as outlier points. A whisker does not necessarily reach the minimum or maximum when there are outliers. Use the data table to verify any point that seems surprising.
Set the quartile calculation if needed. Right-click one of the boxes and choose Format Data Series. In the Format Data Series pane, open Series Options and find Quartile Calculation. Excel offers Inclusive median and Exclusive median. The choice affects how quartiles are calculated, particularly in small samples, so use the method required by your course, organization, or analysis standard. If there is no required convention, pick one method and use it consistently for every group and chart.
Choose which details to display. In Format Data Series, review options such as Show outlier points, Show inner points, Show mean markers, and Show mean line. Outlier points help readers see observations outside the whisker limits; inner points display individual observations and can make a chart busy when there is a lot of data. A mean marker can be useful when the average matters, but it should not be mistaken for the median. Select only the details that help answer the question your chart is meant to address.
Finish the formatting and check the source. Use the Chart Design and Format tabs to adjust the style, colors, chart size, and labels. Keep colors consistent when comparing groups, and do not remove the median line or make it difficult to see. Finally, compare the chart categories with the column headings and check that the source values and units are correct. If the data changes later, confirm that the chart’s selected source range includes any new observations.

On Excel for Mac, look on the Insert tab for the statistical chart options; the exact ribbon layout can vary by release. If Box and Whisker is missing in Excel for the web or an older desktop build, open the workbook in a current desktop version of Excel that supports the chart type. The available formatting controls can also differ between desktop and web versions.

Common Mistakes

Putting every group into one column. A single combined column produces one distribution rather than a box for each group. Keep groups in separate columns and label them clearly.
Mixing measurements or units. A column that combines minutes and seconds, or counts and percentages, does not describe one coherent distribution. Convert values to a common unit before charting.
Replacing missing data with zero. Zero is a valid numeric observation, not a placeholder for an unknown value. Use a blank cell for a missing measurement when appropriate.
Assuming whiskers always show the full range. In Excel, observations beyond the whisker limits may be plotted as outliers. Look at the separate points and the source data before describing the whiskers as minimum-to-maximum.
Ignoring the quartile convention. Inclusive and exclusive quartile calculations can produce different box boundaries, especially with small samples. State or consistently apply the convention when precision matters.
Reading the box as a confidence interval. The box represents the middle 50% of the data, not a confidence interval or a margin of error. Use the correct statistical summary for the claim you want to make.

Tips

Use meaningful group headings rather than labels such as Group 1 and Group 2. Clear labels reduce the need for a separate legend.
Keep the vertical axis consistent when comparing multiple box plots. An automatic scale can make similar spreads look different if you compare separate charts with different axis ranges.
Show individual inner points when the samples are small and readers need to see every observation. For larger samples, consider whether displaying all points makes the chart crowded.
Use a box plot to compare distributions, not just averages. Two groups can have similar medians but very different spreads, or similar spreads but different medians.
Preserve a copy of the original data before cleaning or reorganizing it. That makes it easier to trace unexpected outliers back to their source.
If a reader may not know box plots, add a short note explaining that the box covers the middle half of observations and the center line is the median.

Troubleshooting

The Box and Whisker option is missing. Confirm that you are using a desktop Excel version with statistical charts. Check the Insert tab’s chart drop-down, since the button may be collapsed when the ribbon is narrow. If you are using an older release or Excel for the web and the chart type is unavailable, open the file in a supported desktop version or update Excel.

I see one box instead of one per group. Check the selected range and its orientation. Each group should normally have its own column and heading. If the data is arranged in rows, select the chart and choose Chart Design > Switch Row/Column, then verify the categories and boxes. If headings were not included in the selection, Excel may also label categories generically.

The chart has unexpected outliers or whiskers. First confirm that the source values are correct and use a consistent unit. Then review the selected range for stray numbers, text, or zeros used as missing-value placeholders. Remember that Excel identifies outlier points relative to the quartiles and whisker limits; an unusual point is not automatically an error. Verify it against the original record before changing or removing it.

My quartiles do not match another tool. The tools may use different quartile conventions, or one chart may include a different set of observations. In Format Data Series, check Excel’s Quartile Calculation setting and compare the exact values included in both calculations. Small samples are particularly sensitive to the inclusive-versus-exclusive choice.

The chart does not update when I add data. Select the chart and use Chart Design > Select Data to inspect the chart data range. Expand the range to include the new observations. For a data set that grows regularly, converting the source range to an Excel table with Insert > Table can make future additions easier to manage, but confirm that the chart includes the new rows after adding them.

The chart is hard to read. Make it larger, simplify the colors, and add clear titles and units. If several groups have very different scales, inspect the vertical axis and consider whether they belong on the same chart. Avoid shrinking the chart until category labels overlap; fewer, clearer groups are better than a crowded plot.

Frequently Asked Questions

What does a box and whisker plot show?
The box shows the middle 50% of observations, with its lower and upper edges at the first and third quartiles. A line inside the box marks the median. Whiskers show the non-outlier extent under the chart’s rules, while values beyond that extent may appear as separate points.

Can I make a box plot with data arranged in rows?
Yes. Excel can chart data arranged in rows, but it may initially interpret the rows and columns in the opposite way from what you want. Use Chart Design > Switch Row/Column and check that each intended group has its own box and axis label.

How many values do I need for each group?
Excel can create a chart with only a few observations, but a very small sample gives a limited picture of the distribution and can make quartile choices more noticeable. Use enough observations to support the comparison, and be cautious about drawing broad conclusions from a small group.

Can I show every data point on the chart?
Yes. Right-click a box, choose Format Data Series, and turn on Show inner points. You can also show outlier points separately. With many observations, the individual points may overlap or clutter the plot, so check whether showing them improves readability.

Why does my Excel box plot look different from one in another program?
Programs can calculate quartiles and whisker limits differently, and they may display means or individual points by default. Check the quartile method, the exact source observations, and the display options before comparing charts. Consistent settings make comparisons more meaningful.

Can I edit the colors and title after creating the chart?
Yes. Select the chart to use the Chart Design and Format tabs, and click the chart title to replace its text. To change statistical display options such as quartile calculation or mean markers, right-click a box and open Format Data Series.

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.