Box Plot in Excel 2007: How to Build One Manually

Excel 2007 has no built-in Box and Whisker chart type, so you cannot create one through the chart gallery as you can in newer Excel versions. You can still make a basic box plot by calculating the quartiles and whisker limits, then building the visual with a stacked column chart and lines or shapes.

Why Excel 2007 has no box plot chart

Excel 2007 predates the dedicated Box and Whisker chart. Its chart gallery includes familiar types such as column, line, pie, and scatter charts, but not a statistical box plot. Microsoft added the built-in chart type in Excel 2016. That means instructions telling you to choose Insert > Statistic Chart > Box and Whisker do not apply to Excel 2007; that menu is not present in this version.

A box plot is a compact way to compare distributions. The box spans the first quartile (Q1) to the third quartile (Q3), with a line at the median. Whiskers show values beyond the box, and some versions of the plot also mark unusually distant observations as individual points. A box plot is useful for comparing spread and medians across groups, but it does not show every individual value.

In Excel 2007, the calculations are straightforward. The challenge is drawing and maintaining the chart: the box, median, whiskers, and outlier markers are not automatically connected as a single statistical chart. For a one-time graphic, a manually assembled chart can be adequate. For a chart that must update reliably, consider newer Excel or a suitable statistical tool.

Calculate the five-number summary

Put one group of numeric observations in A2:A11, with a heading in A1. For illustration, use the values 3, 4, 5, 6, 7, 8, 9, 10, 11, and 20. Excel 2007’s QUARTILE function uses the inclusive quartile method. With those ten values, Q1 is 5.25, the median is 7.5, and Q3 is 9.75. The minimum is 3 and the maximum is 20.

With the observations in A2:A11, enter these formulas in a summary table; the formulas return the values shown:

With the data in A2:A11, =MIN(A2:A11) returns 3.
With the data in A2:A11, =QUARTILE(A2:A11,1) returns Q1, 5.25.
With the data in A2:A11, =MEDIAN(A2:A11) returns 7.5.
With the data in A2:A11, =QUARTILE(A2:A11,3) returns Q3, 9.75.
With the data in A2:A11, =MAX(A2:A11) returns 20.

These five values are often called the five-number summary. Check that the observations are numeric and that the intended range does not include a heading or unrelated values. QUARTILE ignores blank cells and text in a referenced range, but errors in the range can affect the calculation. The quartiles can differ slightly from results produced by another program if it uses a different quartile convention.

Choose a whisker and outlier rule

A box plot’s whisker rule matters: the whiskers do not always extend to the minimum and maximum. A common Tukey convention calculates the interquartile range, or IQR, as Q3 minus Q1. It then sets lower and upper fences at Q1 minus 1.5 times the IQR and Q3 plus 1.5 times the IQR. The whiskers end at the most extreme observed values still inside those fences; observations beyond them are plotted separately as potential outliers.

In the example, the IQR is 9.75 − 5.25 = 4.5. The lower fence is 5.25 − 6.75 = −1.5, and the upper fence is 9.75 + 6.75 = 16.5. Therefore, 20 is beyond the upper fence. The lower whisker ends at 3, the largest non-outlier value is 11, and 20 should be marked separately. This is not a claim that 20 is an error; it is simply an observation flagged by the chosen rule.

Some charts instead draw whiskers to the actual minimum and maximum. That convention would extend the top whisker to 20 and would not show 20 as a separate point. Decide which definition your audience expects, label the chart if needed, and use the same rule for every group you compare. To calculate exact whisker endpoints in Excel 2007, you can inspect the sorted values and select the most extreme observations that fall within the fences; the built-in chart-free workflow does not calculate those endpoints automatically.

Build the box with a stacked column chart

A stacked column chart can provide the central box. Make a small helper table for one category with two series: a base value equal to Q1 and a box height equal to Q3 minus Q1. For the example, those are 5.25 and 4.5. The base series positions the visible box at Q1; the height series draws from Q1 to Q3.

Select the category and the two helper values, then choose Insert > Column > 2-D Stacked Column.
If Excel plots the values in the wrong orientation, use Chart Tools > Design > Switch Row/Column so the base and box height are stacked for the category.
Click the base series and format it with no fill and no line. It must remain in the chart as the invisible offset; deleting it would make the box start at zero.
Format the box-height series with a clear fill and border. Set a sensible vertical axis scale through Format Axis so the chart includes all relevant values and the box is easy to read.

The precise appearance of the chart depends on your data and chosen axis limits. If multiple groups are being compared, each group needs its own Q1 base and Q3-minus-Q1 height, arranged as categories in the helper table. Keep the same vertical scale across the groups; using different scales can make their spreads appear misleadingly similar or different.

Add the median, whiskers, and outliers

The stacked column only supplies the box. In Excel 2007, a simple manual method is to overlay lines on the chart: draw a horizontal line through the box at the median, vertical whisker segments from each box edge to its whisker endpoint, and short horizontal caps at the whisker ends. For the example, the median line belongs at 7.5; the lower whisker runs from 5.25 down to 3, and the upper whisker runs from 9.75 up to 11. Add a distinct point above the box for the outlier at 20.

Use Insert > Shapes > Line to draw the lines, and use a small oval or another clear marker for an outlier. If possible, select the chart before inserting shapes so they are associated with the chart rather than floating independently on the worksheet. Set the axis bounds before placing the lines. If you later resize the chart, change its scale, or update the data, check the overlay carefully: manually drawn shapes do not recalculate their positions from cell values. Include a label or note explaining the whisker rule.

This is best treated as a presentation graphic rather than a fully dynamic statistical chart. For several categories or frequently refreshed data, manually repositioning every median, cap, and outlier can become error-prone. Keep a visible summary table beside the chart so a reader can verify the plotted values.

Alternatives and version considerations

If you need a chart that stays linked to the data, the most practical option is often to open the workbook in Excel 2016 or later and use its Box and Whisker chart. In current Microsoft 365 for Windows, select the source data, then choose Insert > Insert Statistic Chart > Box and Whisker. The exact ribbon wording can vary by release. Excel for the web and Mac may also differ in feature availability or controls, so check the chart options in the version you are using.

Other options are to use a reputable add-in compatible with Excel 2007, create the graphic in a statistical application, or use a different chart when the audience does not need quartiles and outliers. A dot plot can show individual observations; a histogram emphasizes frequency distribution. Neither is a box plot, and each answers a somewhat different question. Do not assume that installing an add-in is necessary or that every add-in supports Excel 2007: confirm compatibility, publisher, and data-handling terms before installing software.

Common mistakes and interpretation

Using the wrong quartile definition: Excel 2007’s QUARTILE function uses an inclusive method. If a report specifies another method, calculate quartiles consistently with that specification instead of comparing unlike results.
Calling every whisker endpoint a maximum: Under the 1.5-IQR convention, a whisker ends at the most extreme in-fence observation, not necessarily the sample minimum or maximum.
Treating an outlier as automatically invalid: A plotted outlier is a flag for investigation, not proof of a bad measurement. Check the source and context before excluding any value.
Forgetting that a box plot hides detail: Different datasets can have similar quartiles but different internal patterns. Show the raw data or use another chart when individual values matter.
Letting manual shapes drift: Shapes do not update from worksheet formulas. Recheck their positions after editing the data, changing axis limits, or resizing the chart.

When comparing groups, compare medians, box widths, whisker lengths, and outlier locations together. A taller box indicates a wider middle 50 percent of the data, not necessarily greater overall variability in every sense. Unequal sample sizes and skewed distributions also affect interpretation, so include sample sizes or supporting data when the comparison is important.

Frequently Asked Questions

Can Excel 2007 make a box and whisker chart automatically?
No. Excel 2007 does not include a built-in Box and Whisker chart type. You can calculate the statistics and assemble a chart manually, use a compatible add-in, or use a newer Excel version with the dedicated chart.

What is the easiest way to make a box plot in Excel 2007?
For a single, static graphic, calculate the quartiles and whisker endpoints, create a stacked column from Q1 and Q3 minus Q1, then overlay lines for the median and whiskers. Add outlier markers separately and verify their placement after any chart changes.

Does Excel 2007 have the QUARTILE function?
Yes. QUARTILE(array, quart) is available in Excel 2007. Use quart values 1 and 3 for Q1 and Q3; QUARTILE(array, 2) gives the median. MEDIAN is also available and is often clearer for the middle value.

Should box-plot whiskers reach the minimum and maximum?
Not necessarily. Some plots extend whiskers to the minimum and maximum. Under the common 1.5-IQR rule, they reach the most extreme data points within the fences, and observations outside the fences are shown separately. State which convention you use.

Why do my quartiles differ from another spreadsheet or textbook?
Quartile definitions vary, especially for small samples. Excel 2007’s QUARTILE uses an inclusive method, while another tool or reference may use a different interpolation rule. Confirm the method before comparing results.

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.