How to Draw a Box Plot in Excel | Build It Two Ways

Drawing a box plot in Excel takes one click with the built-in chart in modern versions or a manual workaround in Excel 2013, depending on your software.

A box plot reveals data distribution at a glance, but how to draw a box plot in Excel depends entirely on whether your copy has the built-in Box and Whisker chart type. Newer versions include it as a one-click option in the Insert menu. Excel 2013 requires a few extra steps using quartile formulas and a stacked column chart. Both methods produce the same five-number summary you need for statistical analysis.

The One-Click Method in Modern Excel

If your Excel shows Insert Statistic Chart in the ribbon, you have the modern chart type. Start by selecting your data range — a single column of values works, or multiple columns for side-by-side box plots. Then go to Insert > Insert Statistic Chart > Box and Whisker. The chart appears with the five-number summary already computed: minimum, first quartile, median, third quartile, and maximum. Outliers show as individual dots beyond the whiskers.

You’ll see a vertical box with whiskers extending to the extremes of your data, and any outliers plotted as dots above or below the whiskers.

The Manual Method for Excel 2013 and Older

When Insert Statistic Chart doesn’t include Box and Whisker, you’re on a version that needs the manual workaround Microsoft documented for Excel 2013. It uses helper columns and a stacked column chart to build the box and whiskers from scratch.

Step 1: Calculate the five-number summary. In a helper area, enter these formulas referencing your data range:

• Minimum: =MIN(range)
• First quartile: =QUARTILE.INC(range, 1)
• Median: =QUARTILE.INC(range, 2)
• Third quartile: =QUARTILE.INC(range, 3)
• Maximum: =MAX(range)

Step 2: Calculate the quartile differences. The stacked column needs three segments for the box:

• First segment (bottom of box to Q1): the minimum value
• Second segment (Q1 to median): =Q1 - minimum
• Third segment (median to Q3): =Q3 - median

Step 3: Build the helper table. Create a small table with three rows: minimum, Q1 difference, and Q3-to-median difference. This table drives the stacked chart.

Step 4: Insert a stacked column chart. Select the helper table and go to Insert > Insert Column Chart > Stacked Column.

Step 5: Hide the filler series. Select the bottom series (the minimum row) and set Fill to No Fill. Do the same for the top series (the Q3-to-median difference). Only the middle series — the box from Q1 to median — should remain visible.

Step 6: Add error bars for the whiskers. Go to Design > Add Chart Element > Error Bars > Standard Deviation. Then right-click the error bars and choose Format Error Bars. Set Direction to Minus, End Style to No Cap, and Error Amount to Percentage 100.

Step 7: Format the box. Select the visible box series and apply a solid fill color and a border line so it reads as a proper box plot.

You’ll see a vertical box spanning from Q1 to the median, with whiskers extending down to the minimum and up to the maximum values you calculated.

Common Mistakes That Break Your Box Plot

Three errors cause most of the troubleshooting threads on this topic. Using the built-in chart in Excel 2013 simply won’t work — the template doesn’t exist there, and the ribbon won’t show it. Forgetting to hide the filler series leaves a three-tier colored column instead of a single box. Misconfiguring the error bars is the whisker culprit: the official settings are Minus direction, No Cap end style, and 100% percentage for the error amount.

One more limitation worth knowing: Excel box plots are vertical only. Microsoft’s documentation states they are “produced vertically only” and “cannot be done horizontally.” A horizontal box plot requires exporting to another application, which is not a native Excel workflow.

Can You Make a Horizontal Box Plot in Excel?

Not natively. Microsoft’s documentation confirms Excel box plots are vertical-only. Some users copy the chart into PowerPoint and rotate it there, but that is not an Excel workflow and should not be described as native Excel behavior.

Pick the Method That Matches Your Excel Version

Check your Insert menu first. If you see Insert Statistic Chart, you’re on a modern version and the built-in Box and Whisker chart is the obvious choice — faster, automatic, and less error-prone. If that option is missing, you’re on Excel 2013 or older, and the manual quartile-and-stacked-column method documented by Microsoft works faithfully. Use the formulas in the table above to build the helper columns, follow the seven-step sequence, and you’ll have a clean box plot ready for your report.