Box Plot Template in Excel: Build One Step by Step

A box plot template in Excel is easiest to make with the built-in Box and Whisker chart: arrange each group’s numeric observations in its own column, select the data, then choose Insert > Insert Statistic Chart > Box and Whisker. Excel draws the medians, quartiles, whiskers, and—if enabled—outliers, so you can reuse the worksheet by replacing or extending the data.

What an Excel box plot template shows

A box plot, also called a box-and-whisker plot, summarizes the distribution of numeric observations. It is useful for comparing groups when you want to see not only their typical values but also how spread out the data is and whether unusually high or low observations appear.

Median: the line inside the box, representing the middle value in the ordered data.
Quartiles: the lower and upper edges of the box, commonly called Q1 and Q3. The box spans the middle half of the observations.
Interquartile range: the distance from Q1 to Q3, which describes the spread of that middle half.
Whiskers: lines extending beyond the box to show the rest of the data within the chart’s whisker rule.
Outliers: individual observations plotted beyond the whiskers when the outlier display is enabled.

Unlike a bar chart of averages, a box plot can reveal when two groups have similar medians but very different variability. It does not show every detail of a distribution, however: two groups can have similar boxes and whiskers while having different shapes or clusters within the data.

Arrange the data for a reusable template

Put a descriptive heading at the top of each group’s column, then list one numeric observation per row beneath it. For example, a template comparing three production lines could use columns named Line A, Line B, and Line C. Each row below a heading should represent one measured item, such as one item’s processing time.

Groups do not have to contain the same number of observations. Leave unused cells blank rather than filling them with zero: a zero is a real numeric value and can change the scale and summary. Check that measurements use consistent units and that entries intended to be numbers are stored as numbers, not text. Keep notes, dates, and explanations outside the numeric ranges used for the chart.

For a practical starter layout, use one worksheet for the raw observations and a second for the finished chart and notes. Give the chart a meaningful title, identify the measurement and units, and record the date or source of the data when that context matters. The template can then be copied for another study without mixing the new measurements into the old one.

Create the Box and Whisker chart in Excel

Enter the group names in the first row and the observations below them. Include the headings when you select the range.
Select the complete data range, including the headings and any blank cells within the groups’ columns.
On Windows in Excel for Microsoft 365, choose Insert > Insert Statistic Chart > Box and Whisker.
Review the chart. If Excel treated the rows as groups instead of the columns, select the chart and choose Chart Design > Switch Row/Column.
Use the chart title and axis labels to state what is measured and the units, then format the series to show the statistical details you need.

On Excel for Mac, select the data and use the Insert tab’s statistical chart controls to choose Box & Whisker; the exact label or grouping can vary slightly by build. If the chart type is not available in the Excel version you are using, create it in the Microsoft 365 desktop app rather than assuming a regular column chart is equivalent. The chart is also different from a box plot image pasted into a sheet: a real chart remains linked to worksheet data and can be reformatted.

Read the quartiles, whiskers, and outliers carefully

Excel offers inclusive and exclusive median methods for calculating quartiles in its Box and Whisker chart. In a selected chart, right-click a box and choose Format Data Series; in the Format Data Series pane, look under Series Options for Quartile Calculation. Inclusive and exclusive methods can produce different quartiles, particularly for small samples. Choose one method deliberately and use it consistently when comparing charts.

Under the common 1.5-times-the-interquartile-range rule, the lower fence is Q1 minus 1.5 times the IQR, and the upper fence is Q3 plus 1.5 times the IQR. The whiskers reach the most extreme observed values still inside those fences; points beyond them are shown as outliers when that option is turned on. A plotted outlier is not automatically an error. It is a prompt to check the source and consider whether the value is meaningful.

For a concrete example, suppose a group has these ten values in ascending order: 12, 14, 15, 16, 18, 20, 21, 22, 24, and 40. With the inclusive method, Q1 is 15.25, the median is 19, and Q3 is 21.75. The IQR is 6.5, so the fences are 5.5 and 31.5. The whiskers would reach 12 and 24, while 40 lies beyond the upper fence. This example also shows why a whisker does not necessarily end at the smallest or largest observation.

Check a group’s values with worksheet formulas

You can calculate summary values separately to check a chart or create a compact reference table. These formulas use Excel’s inclusive quartile convention; use the chart’s inclusive setting for a like-for-like comparison. For the example above, place the ten observations in A2:A11.

With the observations in A2:A11, enter this formula in a summary cell to calculate Q1; it returns 15.25 for the example data:

=QUARTILE.INC(A2:A11,1)

With the same values in A2:A11, use this formula for the median; it returns 19:

=MEDIAN(A2:A11)

With the same values in A2:A11, use this formula for Q3; it returns 21.75:

=QUARTILE.INC(A2:A11,3)

To calculate the interquartile range, put Q1 in C2 and Q3 in C3. With that summary layout, this formula in C4 returns 6.5:

=C3-C2

These formulas are useful for checking a calculation, but they do not create the chart by themselves. If you compare the chart to formulas and the values disagree, first check whether the chart uses inclusive or exclusive quartiles and whether the selected range includes the same observations.

Format the chart for comparison

Select the chart series and open Format Data Series to control which details are visible. Depending on the data and your purpose, Excel offers options such as showing inner points, outlier points, mean markers, and a mean line. A mean marker can complement the median, but it should be identified clearly: the mean and median answer different questions and may be far apart when data are skewed.

Keep the same axis scale when comparing groups across separate charts. If one chart uses a much narrower vertical range, a small difference can look dramatic. Use distinct but restrained series colors, make the median line visible, and avoid adding decorative effects that obscure the boxes. For a report, include sample sizes when groups have very different numbers of observations, because a box plot alone does not make those counts obvious.

For repeated updates, consider turning the source range into an Excel table using Insert > Table, with the group labels as headers. A table makes the data easier to filter and extend, and a chart based on the table can generally include newly added rows. Still check the chart’s source range after adding a group or changing the layout; confirm that every intended observation is included and no notes or totals have become part of the plotted data.

Common problems and how to fix them

The chart shows one box instead of one per group: Excel may have interpreted the orientation differently. Try Chart Design > Switch Row/Column, and confirm that group names are in the header row.
A box is missing or looks unexpectedly small: inspect the source column for text-formatted numbers, blanks, or a very different scale. A group with identical values has no visible box height because its quartiles coincide.
Zeros or blanks distort the result: remove placeholder zeros when they mean no observation. Leave genuinely missing observations blank, and do not replace them with estimated values unless that treatment is justified and documented.
Outliers look like mistakes: check the original record, units, and measurement process first. Do not delete a valid observation simply to make the chart look neater.
Two charts give different quartiles: verify the quartile method, data range, and treatment of missing values. Small groups are especially sensitive to different quartile conventions.
The chart does not update as expected: inspect Chart Design > Select Data and check the displayed source range. This is particularly important after moving, deleting, or adding columns.

Built-in chart or a manual template?

For most users, Excel’s built-in Box and Whisker chart is the best starting template: it draws the statistical chart from raw observations and includes settings for quartiles and point displays. A manually constructed plot can be useful when you need a specialized appearance or a fixed design, but it takes more work. You must calculate the summary statistics, build chart series from them, and carefully format the axes and error bars or stacked elements. That approach is more vulnerable to errors when source data change.

A histogram is another alternative, but it answers a different question by grouping values into bins and showing frequencies. A scatter plot can show every observation and make clusters visible, although it may be harder to compare several groups at a glance. Choose the chart based on the question: use a box plot for a compact comparison of distributions, and consider adding a histogram or dot plot when distribution shape or individual observations matter.

Frequently Asked Questions

Does Excel have a box plot template?

Excel for Microsoft 365 includes a Box and Whisker chart type, so you can build a template from a worksheet range instead of downloading a separate file. Organize observations by group, insert the chart, and save a copy of the workbook with the layout and labels you want to reuse.

How do I make a box plot with multiple groups?

Place each group in a separate column with its name in the top row, select all the columns, and insert a Box and Whisker chart. If Excel plots the groups in the wrong direction, use Chart Design > Switch Row/Column.

What do the dots outside the whiskers mean?

They are observations beyond the chart’s whisker limits when outlier points are displayed. Under the usual 1.5-IQR rule, a point beyond the lower or upper fence is plotted separately. Check the data before deciding whether it is an error or a valid unusual result.

Why are my Excel quartiles different from another calculator’s?

Software can use different quartile conventions, and Excel’s chart lets you choose inclusive or exclusive median calculation. Check that convention, the sample size, and the selected data range before concluding that a chart is wrong.

Can I make a box plot in Excel for the web?

Available chart types and editing controls can differ between Excel for the web and desktop builds. If you cannot find Box and Whisker in the web app, open the workbook in Excel for Microsoft 365 desktop and create or adjust the chart there.

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.