If you have ever stared at a column of numbers and wondered which value sits in the middle, where the outliers hide, and how spread out the rest of the data actually is, a box and whisker plot in Excel is the chart you want. It compresses an entire distribution into a single, scannable shape — minimum, first quartile, median, third quartile, maximum, and any outliers — and Excel can draw it for you in less than a minute.

Most people first meet the box plot in a statistics class, then never use it again because their software made it painful. Excel changed that in version 2016. Today, whether you are on Excel 365, Excel 2019, Excel 2021, or Excel for the web, the built-in Box and Whisker chart type does the math, draws the quartiles, and flags outliers without a single formula. You highlight the data, click Insert, pick the icon, and you have a publication-quality chart.

This guide walks through every step, every option, and every workaround. You will learn the shortcut path for users who just need a chart fast, the deep-dive path for analysts who want to control quartile method and outlier rules, and the legacy path for anyone stuck on Excel 2013 or earlier who still needs a clean box plot using stacked bars. We will also cover horizontal box plots, multiple-series comparisons, color and label tweaks, and the most common reasons your chart looks wrong on the first try.

Before opening Excel, it helps to know what each piece of a box plot actually means. The box itself spans from the first quartile (Q1, the 25th percentile) to the third quartile (Q3, the 75th percentile). Inside the box, a horizontal line marks the median (Q2, the 50th percentile). Excel also draws an X — the mean — by default, which is a small but useful detail that many other tools skip. The whiskers extend outward to the smallest and largest values that are not classified as outliers. Outliers, when present, appear as individual dots beyond the whiskers.

Why does this matter? Because a box plot tells you four things at a glance that a bar chart or line chart cannot: central tendency (where is the middle?), spread (how wide is the typical range?), skewness (is one side longer than the other?), and outliers (which values are unusual?). For test scores, sales figures, response times, manufacturing tolerances, or survey ratings, that is exactly the summary most analysts need.

Let's walk through a real example. Suppose you have a column labeled Test Scores with 30 student results between 42 and 98. To build the chart, click any cell inside the data range, hold Shift, and click the last cell so the entire column (including the header row) is selected. Excel needs the header so it can label the series in the legend later.

Now go to the Insert tab. In the Charts group you will see a small icon that looks like a column with a line across it — that is the Statistic Chart dropdown. Click it, and the first option in the second row is Box and Whisker. A single click places the chart on your sheet. The default styling is plain, but the math is already correct: Excel has calculated the quartiles using the exclusive method (more on that shortly), drawn the whiskers to the furthest non-outlier values, and plotted any outliers as individual dots.

If you do not see the Box and Whisker option, you are almost certainly on Excel 2013 or earlier. Skip to the legacy stacked-column workaround later in this guide.

Once the chart appears, click on it to reveal the Chart Design and Format tabs on the ribbon. Most of the visual tweaks happen in the Format Data Series pane, which you open by right-clicking any box on the chart and choosing Format Data Series. This is where the real customization power lives, and it is also where users typically make their first mistake: closing the pane too quickly without exploring the four toggles that completely change what the chart shows.

The four toggles are: Show inner points (displays every data value as a dot), Show outlier points (toggles outlier dots on or off), Show mean markers (the X inside the box), and Show mean line (a line connecting the means across multiple categories). Below them is the most important choice of all — the Quartile Calculation method. You can pick Inclusive median or Exclusive median. The difference matters more than most users realize.

Comparing multiple groups is where box plots really earn their keep. Suppose instead of one column of test scores you have four — Class A, Class B, Class C, and Class D — each with 30 students. Highlight all four columns including the headers, insert the box and whisker chart, and Excel draws four side-by-side boxes on the same axis. Now you can see at a glance which class has the highest median, which has the widest spread, and which has the most outliers.

This single-chart comparison is what makes box plots indispensable for AB testing, department-by-department performance reviews, before-and-after studies, and any situation where you need to compare distributions, not just averages. A bar chart of class averages would hide the fact that Class B has a tight 70-85 range while Class D ranges from 45 to 99 with the same mean.

If your groups are stored in long format — one column for category names and one column for values — Excel handles that too. Highlight both columns, insert the chart, and Excel automatically groups by the category column. This is the structure you will get from most database exports, so do not feel obligated to pivot your data first.

Color and styling matter more than they should. The default Excel box plot uses a single muted blue for everything — boxes, whiskers, outliers — which makes multi-group charts hard to read at a glance.

Click any box, then click again to select just that one series (the first click selects all series, the second narrows to one), and use the Fill bucket on the ribbon or the Format Data Series pane to assign a distinct color. A good rule: pick a color palette with clear contrast between adjacent boxes, and reserve red for any group you want to highlight as problematic.

To switch from vertical to horizontal orientation, there is unfortunately no one-click option for native box plots. The workaround: right-click the chart area, choose Select Data, and click Switch Row/Column. This flips the axis assignment. If that does not produce the layout you want, you may need to use the legacy stacked-column approach (covered below) which gives full control over orientation.

Adding data labels is another common request. Excel does not show numeric labels on box plots by default because there are six values per box (min, Q1, median, mean, Q3, max) and labeling all of them creates clutter. The cleanest solution is to add a small text box with the median value next to each box, or use the QUARTILE function in adjacent cells to display the numbers in a small table beneath the chart.

For users on Excel 2013 or older, the Box and Whisker chart type does not exist. You can still build a perfectly accurate box plot — it just takes more setup. The trick is to use a stacked column chart and hide the bottom segment, leaving only the visible box and the error bars (which become your whiskers).

Start by creating a small helper table next to your data. The rows should be: Minimum, Q1 (minus Minimum), Median (minus Q1), Q3 (minus Median), Maximum (minus Q3). Use MIN, QUARTILE.INC, MEDIAN, QUARTILE.INC, and MAX formulas referencing your data range. The subtractions stack the columns correctly. Then insert a stacked column chart from those five values, set the bottom segment fill to No Fill so it disappears, and add error bars from the second segment (downward) and the fourth segment (upward) to create the whiskers.

It sounds tedious, and it is. But the result is a fully customizable box plot that respects every formatting choice you throw at it. Many analysts still prefer this method even on Excel 2021 because it allows for horizontal orientation, custom whisker definitions (5th and 95th percentiles instead of 1.5 × IQR, for example), and granular color control on every component. If you build the helper table as a reusable template, the second chart only takes two minutes.

Another reason to use the legacy method: animation in PowerPoint. Native Excel box plots paste into PowerPoint as a single object, which means you cannot animate the boxes individually. A stacked-column box plot pastes as a multi-series chart, so you can fade in each group one at a time during a presentation. Small detail, but it can transform a static report into a guided narrative.

Box plots show up frequently in standardized tests, data analysis interviews, and Excel certification exams. The most common question types ask you to identify the median from a box, compare spreads between two groups, or spot outliers. A few quick rules will get you through almost any test question. The median is always the line inside the box, never an edge.

The mean — the X — does not have to sit on the median; if it is to the right of the median, the data is right-skewed (positive skew), and if it is to the left, the data is left-skewed (negative skew). When two boxes overlap heavily, you generally cannot conclude their medians differ significantly without a statistical test. When the boxes do not overlap at all, you can almost always conclude they come from different distributions.

For Excel-specific exam questions, memorize three function names: QUARTILE.INC (inclusive quartile, matches the textbook method), QUARTILE.EXC (exclusive quartile, Excel's default for box plots), and PERCENTILE.INC (general percentile calculation). The legacy QUARTILE function still works for backward compatibility and behaves identically to QUARTILE.INC.

One question that trips people up: what happens if the dataset has only four values? The QUARTILE.EXC function returns #NUM! because exclusive percentiles need at least four data points and produce undefined results at the extremes. Always have at least five data points if you plan to use exclusive quartiles, or switch to inclusive for very small samples.

A quick word on accessibility. Box plots can be tricky for colorblind readers when two adjacent groups use red and green. Stick to colorblind-safe palettes — blue and orange, or shades of a single hue with varying brightness — whenever your audience is broad or your chart will be published online. Excel's built-in monochromatic palette under Chart Styles is a safe starting point for accessibility-first reports.

Another small but useful tip is to widen the gap between boxes when you have three or more groups. Right-click any box, open Format Data Series, and reduce the Gap Width slider. A gap of around 50 to 75 percent gives each box room to breathe without losing the side-by-side comparison feel. Tight defaults make charts look cramped, especially on slide decks where readers see the visual from twenty feet away.

Box and whisker plots remain one of the most efficient ways to compress a dataset into a chart that tells a story without overwhelming the viewer. In Excel they used to be a pain to build, requiring stacked columns, hidden series, and manual error bars. Today, on any version from 2016 onward, the chart is genuinely a single-click affair — and the math is correct out of the box.

The work, then, is no longer in drawing the chart but in choosing what to show. Pick the quartile method that matches your audience. Decide whether mean markers and inner points add value or just clutter. Use color deliberately to highlight the group that matters most. Add a one-sentence caption that says what the reader should notice. These small editorial choices separate a chart that gets glanced at from a chart that changes a decision.

If you are preparing for an Excel exam or a data analysis interview, practice building these charts from raw data three or four times until the steps are automatic. Then practice reading them — given a chart, can you identify the median, the spread, the skew, and the outliers in under fifteen seconds? That speed is what every employer and every exam is actually testing.

One more habit worth building. After you create a chart, audit it against the data table you started from. Run QUARTILE.INC or QUARTILE.EXC in adjacent cells. Compare the computed numbers to what you see on the box. If anything is off by more than a rounding error, something is wrong with the source data — usually a hidden blank or a number stored as text. Catching that mismatch before you share the chart with a manager or client is the difference between a quick spot-check and a long email explaining why the report needs fixing.

Finally, save your favorite box plot as a chart template. Right-click the finished chart, choose Save as Template, and Excel writes a CRTX file you can apply to any future chart with two clicks. Build the template once with your preferred colors, font sizes, mean marker visibility, and outlier style, and every future analysis starts from a polished baseline instead of the muted default.