Five numbers. That's all a box and whisker plot really shows — minimum, first quartile, median, third quartile, maximum — yet it tells you more about a dataset's shape in two seconds than a table of 500 rows ever will. If you've ever stared at a column of test scores or sales figures and wondered where most of the data actually sits, this is the chart you want.
Excel 2016 added a built-in Box & Whisker chart type, which means you don't need the messy stacked-column-with-error-bars trick anymore. Short answer for newer versions: Insert → Insert Statistic Chart → Box & Whisker. Three clicks, no formulas, automatic quartiles. We'll walk every step below.
This guide covers the modern built-in chart, the legacy workaround for Excel 2013 and earlier, and the customization options that turn a default chart into something publication-ready. If you're warming up on the basics, the how to use excel walkthrough covers ribbons, cells, and saving. For broader chart skills, how to make a graph in excel shows you the Insert tab end to end.
One thing worth flagging upfront. Excel's built-in box plot uses the exclusive quartile method by default, which can disagree with what your statistics textbook taught you (most textbooks use the inclusive method). Don't panic. There's a one-click toggle in the format pane. We'll get to it.
Picture a small rectangle — the box — sitting on a number line. A horizontal line crosses the box near the middle. Two thin lines, the whiskers, extend from the top and bottom of the box. Sometimes a few isolated dots float beyond the whiskers. Each piece carries meaning.
The box spans from Q1 (the 25th percentile) up to Q3 (the 75th percentile). The middle 50% of your data lives inside that box. That range is called the interquartile range, or IQR. The line inside the box is the median — the middle value when you sort all data points. Not the average. The median.
The whiskers reach to the smallest and largest values that aren't outliers. By the standard convention Excel follows, a point is treated as an outlier if it's more than 1.5 × IQR above Q3 or below Q1. Those outliers appear as dots beyond the whiskers. Excel also lets you show an X for the mean — a separate marker from the median line.
Bar charts show one number per category — usually the mean. A box plot shows the entire distribution: spread, skew, outliers, and centre, all in the same visual. If you're comparing test scores across five classes, a bar chart tells you who scored highest on average. A box plot tells you which class had the widest range, which had a suspicious outlier, and whether scores skew high or low. Three pieces of analysis for the same plotting effort. That's why scientific journals, finance dashboards, and quality-control teams default to box plots when comparing distributions.
The smallest data point that isn't classified as an outlier. The bottom whisker reaches here.
One quarter of your data falls below this value. The bottom edge of the box sits here.
The middle value when sorted. Half the data sits above, half below. Drawn as a line inside the box.
Three quarters of your data falls below this. The top edge of the box marks this value.
The largest data point that isn't classified as an outlier. The top whisker reaches here.
Here's the path you'll use 95% of the time. It works in Excel 2016, 2019, 2021, Microsoft 365, and Excel for Mac 2016 onward. No formulas. No helper columns. Excel calculates every quartile for you.
Start with your data in a single column or several columns side-by-side. One column per group you want to compare — test scores for Class A in column A, Class B in column B, Class C in column C. Headers in row 1, raw values from row 2 down. The columns don't need to be the same length, which is the part newcomers always overlook. Different class sizes? Fine. Excel handles ragged columns without complaint.
Select the data including headers. Go to Insert on the ribbon. Look for the Statistic Chart icon — it's the small picture showing a histogram in the Charts group. Click it. A dropdown shows three options: Histogram, Pareto, and Box and Whisker. Pick the third. Done. Excel inserts a chart with one box per column.
If you can't find the Statistic Chart icon, the alternative path is Insert → Recommended Charts → All Charts tab → Box & Whisker from the left-hand list. Same result, two extra clicks. Worth knowing because the ribbon icon shrinks on smaller screens and sometimes disappears into an overflow menu.
One more layout option. If you want a single box plot for one column of data — not a comparison — just select that one column. Excel will draw one box. The chart still labels axes correctly and shows the same five-number summary. Single-distribution box plots are common for quality-control reports where you only have one process to monitor.
For the bigger picture of why Excel matters in any office role, the microsoft excel hub pulls together certifications, formulas, and the skills employers test for. And if you're comparing the box plot to other ways of showing distributions, the how to make a histogram in excel guide covers when each chart type wins.
Headers in row 1. One column per group. Raw values from row 2 down. Columns can be different lengths — that's fine.
Click the top-left header and drag to the bottom-right value. Include headers — Excel uses them as box labels.
Click Insert on the ribbon, then find the Statistic Chart icon in the Charts group.
Click the Statistic Chart dropdown. Pick Box and Whisker — the third option after Histogram and Pareto.
Excel drops the chart in your worksheet. Drag it where you want. Edit the title by clicking it once and typing.
Right-click a box, choose Format Data Series, and switch quartile calculation between Inclusive and Exclusive as needed.
Here's where Excel's built-in box plot trips up statisticians. By default, Excel uses the exclusive median method to calculate Q1 and Q3. That matches the QUARTILE.EXC function and most modern statistical software. But it might disagree with your textbook, which probably teaches the inclusive method (QUARTILE.INC, also what the older QUARTILE function returns).
What's the difference? Both methods sort your data and find the median first. The inclusive method includes the median when computing Q1 from the lower half and Q3 from the upper half. The exclusive method excludes the median from both halves. For datasets with even counts, both produce identical answers. For odd counts, they diverge by a small amount — usually one value position.
For most real-world data, the choice barely changes the visual. For small datasets (under 20 points) or formal statistical reports, it can matter. The good news: switching is a one-click toggle. Right-click any box in the chart. Choose Format Data Series from the menu. The right-hand pane opens. Under Series Options, find the Quartile Calculation dropdown. Pick Inclusive median or Exclusive median. The chart redraws instantly.
If you're not sure which one matches your textbook or report standard, calculate both with formulas in a helper area and compare to the values in your box plot. =QUARTILE.INC(A2:A50, 1) for inclusive, =QUARTILE.EXC(A2:A50, 1) for exclusive. Match the values to the chart, then set the toggle accordingly. The excel formulas reference covers QUARTILE syntax and related percentile functions in depth.
Excel applies the standard Tukey rule for outliers. Any point above Q3 + 1.5 × IQR or below Q1 − 1.5 × IQR is flagged as an outlier and drawn as a dot outside the whisker. The whisker itself stops at the most extreme non-outlier value, not at the data minimum or maximum.
This is the part newcomers misread. The bottom whisker is not automatically the smallest data point. If your smallest point is an outlier, the whisker stops short of it and the outlier appears as a separate dot below. Same logic at the top. It's standard statistical practice, but the visual surprises people the first time they see a dot floating away from the box.
Whisker style is configurable too. In the same Format Data Series pane, you'll find checkboxes for Inner Points, Outlier Points, Mean Markers, and Mean Line. Toggle outliers off if you want simple min/max whiskers instead of the Tukey convention. Useful when presenting to non-statistical audiences who'd get distracted by stray dots.
Right-click any box. Choose Format Data Series. The pane on the right is where the magic happens. Excel exposes four toggles that change what's drawn on each box, and a few formatting controls that change how those drawings look.
Show inner points sprinkles every non-outlier data point inside the whiskers as small dots. Useful for small datasets where you want viewers to see every observation, not just the summary. For datasets above 50 points it creates visual noise — skip it. Show outlier points is on by default and is the dot you see beyond the whisker for Tukey-defined outliers. Toggle off if you want plain min/max whiskers.
Show mean markers adds an X inside the box at the mean (average) value. Different from the median line. Useful when distributions are skewed and you want viewers to see how mean and median diverge. Show mean line connects the means of adjacent boxes with a line — handy when comparing groups along a time series or treatment progression.
For coloring individual boxes, click once to select the whole series, then click again to select just one box. The right-side Fill & Line pane lets you change that box's color independently. Repeat for each box if you want a multi-color palette — say, red for failing scores, yellow for borderline, green for passing. Color also helps when sharing with audiences who have color-vision deficiency, paired with text labels or pattern fills.
Whisker styling sits in the same Fill & Line section under the Whiskers sub-heading after you select one of them. Change the whisker line weight, dash style, or color. Dashed whiskers signal uncertainty in academic publications — a small touch your statistics professor will notice. The excel conditional formatting rules can also drive cell-by-cell color before charting if you want the chart palette to match a status table elsewhere in the workbook.
What it does: Plots every non-outlier value as a small dot inside the whiskers.
When to use: Small datasets (under 30 points). When viewers benefit from seeing each observation, not just the summary box.
When to skip: Datasets over 50 points — the dots overlap and the box becomes a smudge.
What it does: Draws Tukey-rule outliers as separate dots beyond the whiskers (default ON).
When to use: Statistical reports. Quality-control charts. Anywhere outliers carry actionable meaning.
When to skip: Non-technical audiences who'd misread the dots as errors or random noise.
What it does: Adds an X marker inside each box at the arithmetic mean (separate from the median line).
When to use: Skewed distributions where mean and median diverge — viewers can see the difference at a glance.
When to skip: Symmetric data — mean and median nearly overlap, adding clutter.
What it does: Connects mean markers across adjacent boxes with a line, like a trendline.
When to use: Time-series or treatment-progression data where you want to highlight how the mean shifts across groups.
When to skip: Unrelated category groups where a trend line implies a false connection.
If you're stuck on Excel 2013, 2010, 2007, or earlier, the built-in Box & Whisker chart doesn't exist. The classic workaround uses a stacked column chart with error bars. It looks identical to the modern version once styled but takes about ten minutes of setup. Worth knowing if your IT department refuses to upgrade — or if you need backward-compatible reports.
Build a helper table with five rows: Minimum, Q1, Median, Q3, Maximum. Use =MIN(A2:A50), =QUARTILE.INC(A2:A50, 1), =MEDIAN(A2:A50), =QUARTILE.INC(A2:A50, 3), and =MAX(A2:A50). Add two derived rows: Box bottom height = Q1 − Min, and Box middle height = Median − Q1, Box top height = Q3 − Median. These give you the segments needed for the stacked bars.
Insert a 2-D Stacked Column chart from the five derived rows. Hide the bottom segment (Q1 − Min) by setting its fill to No Fill. The remaining stacked segments now form the box visually. Right-click the chart and add error bars to the top of the box pointing up to the Max, and to the bottom pointing down to the Min. Format the error bars as caps to mimic whiskers.
It's fiddly. Outliers don't appear automatically. The mean marker isn't built in. But the resulting chart is fully compatible with Excel 97 through 2013. If you absolutely must produce a box plot in an older version, this is the path. For workflows where you're regularly producing reports across versions, consider exporting summary statistics to a separate workbook and using the modern chart there.
One more option for older versions: the FunFun or Real Statistics add-ins both ship box-plot generators that work back to Excel 2007. Free for personal use, paid for commercial. They install through File → Options → Add-ins → Manage Excel Add-ins → Go. Less manual work than the stacked-column trick. For deeper formula skills that help when building any legacy chart workaround, the excel functions reference is the best place to start.
Default Excel charts look fine for internal review. They look amateur in a published report. Three formatting changes turn a default box plot into something polished. First, the color palette. Default boxes use Excel's accent colors in order. For three groups it works; for seven it gets ugly.
Click once to select the whole series, then click again on one box to isolate it. The Fill & Line section assigns a specific color. Use semantic colors — red for failures, green for passes — or a sequential palette for ordinal categories.
Second, the gridlines. Default charts have horizontal gridlines that visually compete with the boxes. Right-click any gridline and choose Delete. Third, the font — bump axis text to 12pt and the title to 14pt bold. Tiny change, big improvement.
Once you've styled the chart, right-click the chart area and choose Save as Template. Templates save the color palette, gridline visibility, font sizes, and quartile toggle — reusable from Insert → Recommended Charts → All Charts → Templates. The excel templates guide covers template management in detail.
Three chart types compete for distribution-display duty in Excel: box plots, histograms, and bar charts. They look similar from a distance and answer wildly different questions. Picking the right one is half the battle.
A histogram shows the shape of one distribution by counting how many values fall into each bin. Tall bars where data clusters, short bars in the tails. Great for spotting bimodal distributions, skewness, or unexpected gaps. Limitation: showing more than two distributions on one histogram gets messy because overlapping bars hide each other.
A bar chart shows one number per category — usually a sum or average. Fast to read. Useless for spread or outliers. If you need to know which sales region performed best on average, a bar chart wins. If you need to know which region had the most variable sales, you need a box plot or histogram.
A box plot compares the spread, centre, and outliers of multiple distributions side-by-side. It's the right tool when you have 3–20 groups and want to compare them all on the same axis. Each box uses minimal screen real estate, so 12 boxes fit on one chart while 12 histograms would need their own grid. The trade-off: less detail than a histogram (no bin shape, no bimodality detection).
Rule of thumb. One group with detail: histogram. Many groups comparison: box plot. Quick rank: bar chart. The how to create a scatter plot in excel covers when scatter plots beat all three (two variables, correlation).
A few misinterpretations show up over and over. The box does not represent the data range — it represents the middle 50%, the interquartile range. The whiskers plus outliers represent the range. A small box doesn't mean a small dataset either; it means the middle 50% are tightly clustered. You could have 10,000 points packed into a narrow band.
Whiskers can be asymmetric. Short lower whisker plus long upper whisker means right-skew — most values cluster low with a long tail of high values. Income data and response times often look this way. Symmetric box plots suggest roughly normal distribution. Use the visual symmetry as a quick normality check.
Outliers aren't always errors. The Tukey 1.5 × IQR rule flags points statistically — that's a convention, not a verdict. A flagged outlier might be your best customer or your highest-yield batch. Don't delete them without checking what they represent. The how to remove duplicates in excel workflow handles cleaning errors, but outlier detection is a different question.
Excel's built-in chart falls short in a few specialist cases. Notched box plots (confidence intervals around the median) aren't supported — switch to R or Python for that. Violin plots and beeswarm plots aren't native either. And when comparing more than 20 boxes side-by-side, labels overlap badly — split into smaller charts grouped by region or time period instead. For everything else, the built-in chart handles the common cases beautifully.
Select your data with headers, go to Insert → Statistic Chart → Box and Whisker. Excel calculates Q1, median, Q3, min, max, and outliers automatically and draws one box per column. The built-in chart requires Excel 2016 or later. Older versions need a stacked-column-with-error-bars workaround or an add-in like Real Statistics.
The line inside the box is the median — the middle value when the data is sorted. Half the data points sit above the line, half below. It is not the mean (average). Excel can optionally show the mean as a separate X marker via Format Data Series → Show Mean Markers. The median is robust to outliers; the mean is not, which is why box plots highlight the median by default.
Both methods sort data and find the median first. Inclusive median (QUARTILE.INC) includes the median when computing Q1 from the lower half and Q3 from the upper half. Exclusive median (QUARTILE.EXC) excludes the median from both halves. They produce identical results for even-count datasets and slightly different results for odd-count datasets. Excel's default for box plots is Exclusive — toggle to Inclusive via Format Data Series → Quartile Calculation if your reference standard requires it.
Excel uses the standard Tukey rule. A point is an outlier if it falls more than 1.5 × IQR above Q3 or below Q1, where IQR is the interquartile range (Q3 − Q1). Outliers appear as dots beyond the whiskers. The whiskers themselves extend to the most extreme non-outlier values, not the absolute min and max. Turn outlier detection off in Format Data Series if you want plain min/max whiskers instead.
Not directly — the built-in Box & Whisker chart was added in Excel 2016. For older versions, build a stacked-column chart from a helper table containing Min, Q1, Median, Q3, Max, then add error bars to mimic whiskers. The free Real Statistics add-in offers a one-click box plot generator that works back to Excel 2007. Both produce visually identical results to the built-in chart in newer versions.
Right-click any box, choose Format Data Series, and tick Show mean markers in the Series Options pane. An X appears inside each box at the mean (arithmetic average) — separate from the median line. Tick Show mean line to connect mean markers across adjacent boxes with a trend line, useful for showing how the mean shifts across groups in a time series or treatment progression.
Two likely reasons. First, the quartile method — Excel defaults to Exclusive, most textbooks use Inclusive. Switch via Format Data Series → Quartile Calculation. Second, Excel uses the Tukey 1.5 × IQR rule for outliers, which means whiskers stop short of the data min/max when outliers exist. Some textbooks draw whiskers all the way to min and max regardless. Toggle Show outlier points off to match that simpler convention.
Use a box plot when comparing the spread, centre, and outliers of multiple groups side-by-side — 3 to 20 distributions on one chart. Use a histogram when you want to see the shape of one distribution in detail, including bimodality or skewness. Use a bar chart when you only need one number per category (sum or average) and don't care about spread. Quick rule: one group with detail → histogram; many groups with comparison → box plot; quick rank by single value → bar chart.