Excel Box Plot With Data Points: How to Show Every Value

Yes. In Excel for Microsoft 365 on Windows, select the box-and-whisker chart, open Format Data Series, and under Series Options check Show inner points; check Show outlier points as well if you want the unusual values shown. The first setting displays observations within the whiskers, while the second displays observations Excel classifies as outliers.

What a box plot with data points shows

An Excel box plot, also called a Box & Whisker chart, summarizes the distribution of numeric observations for one or more groups. The box represents the middle portion of the data, the line inside it marks the median, and whiskers extend beyond the box. When you show data points, the chart also plots the individual observations rather than relying only on the summary.

That combination is useful because two groups can have similar medians and quartiles but very different patterns underneath. A cluster of values, a gap, or a handful of unusually high observations may be hard to spot in a summary alone. Visible points help readers assess how many observations there are and where they fall, while the box and whiskers provide a compact view of the overall spread.

Excel offers separate controls for inner points and outliers. An inner point is an observation within the whisker range. An outlier is plotted beyond a whisker under the chart’s outlier rule. Turning on one option does not automatically turn on the other, so choose both if your goal is to display every observation.

Arrange the source data by group

For the clearest setup, put each group in its own column, with a group name in the first row and its numeric observations below. For example, columns named Morning, Afternoon, and Evening could contain the measured readings for each time period. The groups do not need to have the same number of observations; leave unused cells blank rather than filling them with zeros, since a zero would be treated as a real measurement.

Use one observation per cell and keep headings distinct from the numeric data. Check for numbers stored as text, stray spaces, notes in the middle of a column, or totals mixed in with the observations. These can lead to unexpected chart categories or make the chart represent something other than the individual measurements you intended. Missing observations should generally remain blank, not be replaced with an average unless that is a deliberate and documented data-cleaning decision.

If your data is arranged in rows instead, select the range and insert the chart. If Excel interprets the groups in the wrong direction, use Chart Design > Switch Row/Column. Before adding labels or formatting, check that the chart has one box per intended group and that its category names match your headings.

Create the chart and turn on points

Select the source range, including the group headings.
On the Windows ribbon, choose Insert > Insert Statistic Chart > Box & Whisker.
Click one of the boxes in the chart to select the data series. If the chart contains several groups, clicking a box generally selects the series of boxes rather than one individual observation.
Right-click the selected series and choose Format Data Series. Alternatively, use the chart formatting controls to open the Format Data Series pane.
In Series Options, select Show inner points. Select Show outlier points too if outliers should appear on the chart.

The points should appear as markers over the relevant group. If you only want to emphasize unusual readings, leave Show inner points off and enable Show outlier points. If you want to show the full sample, enable both. The chart updates as you change these options, so inspect each group after applying them.

On Excel for Mac, the chart type is also available from the Insert tab, though ribbon wording and pane placement can vary by build. Select the chart series and open Format Data Series, then look for the equivalent Series Options controls. Excel for the web may not offer every desktop chart-formatting option; if you cannot find the point controls, open the workbook in the desktop app.

Choose the quartile and display settings

Excel’s Series Options also lets you choose how quartiles are calculated. The Inclusive median and Exclusive median choices can produce different quartiles, especially in small samples. A quartile is a cut point in an ordered dataset, and there are multiple conventions for calculating it. For a chart used in a report, use the convention required by your organization or field. If there is no required convention, choose one consistently and state it when readers need to compare your results with another analysis.

Other options can add a mean marker or a mean line. The mean is the arithmetic average, whereas the line inside the box is the median. They can differ notably when a distribution is skewed or contains extreme values. Showing the mean can be helpful, but label or explain it so readers do not mistake it for the median. You can also format the series’ fill, border, and marker appearance; keep the points visible against the box fill and whiskers.

For a chart intended to show the full set of observations, check that neither point option has been disabled and that the markers are not hidden by a heavy fill or other chart elements. These controls affect what the chart displays; they do not add or remove values from the worksheet.

How to interpret the boxes, whiskers, and markers

The median divides the ordered observations into a lower and an upper half. The box spans the first and third quartiles, so it covers the middle half of the data. The interquartile range, often shortened to IQR, is the distance between those quartiles. In a conventional box plot, whiskers reach the most extreme observations still within 1.5 IQR of the nearer quartile; observations beyond that range are shown separately as outliers. The exact quartile calculation setting can affect the displayed boundaries.

Do not assume that a whisker always reaches the minimum or maximum value. If an observation lies outside the whisker rule, it may appear as an outlier marker instead. Also, an outlier marker is not automatically an error. It identifies a value unusual relative to the distribution and rule used by the chart. Check the source record and context before deciding whether it reflects a mistake, a meaningful rare event, or a valid extreme measurement.

With inner points displayed, identical or very close observations can overlap. A marker that looks like one point may therefore represent multiple values in the same position. The box plot communicates distribution, but it is not always the best way to count observations or inspect exact values. Keep the worksheet available, add data labels only if they remain legible, or use a complementary chart when precise point-level reading matters.

Concrete example: comparing three groups

Suppose a worksheet contains response times for three service teams, with one team per column. A box-only chart might show that the teams have similar medians. Enabling inner points could reveal that one team’s times cluster tightly, another has a broad spread, and the third has several readings well above its typical range. Enabling outlier points makes those high readings visible beyond the whiskers.

That visual difference can guide a useful follow-up: inspect the actual records for the high readings, confirm that they use the same units, and consider whether they came from a distinct circumstance. The chart alone cannot explain why a value is high, prove that a team performs better, or establish that a difference is statistically significant. It is a descriptive view of the values supplied to it, not a substitute for checking the data or choosing an appropriate statistical test.

Common problems and practical fixes

No points appear: Reopen Format Data Series and check Show inner points and Show outlier points. If both are off, Excel displays the summary without individual markers.
Only a few points appear: You may have enabled outliers but not inner points. Turn on Show inner points to add observations within the whiskers.
The chart has one box instead of several: Confirm that each group is in a separate column or use Chart Design > Switch Row/Column. Verify that the selected range includes all group headings and observations.
Some values seem missing: Check that they are numeric cells inside the selected range and not text, blanks, or values in a column Excel did not include. Also confirm that the chart’s data range covers the full dataset.
Markers are hard to distinguish: Reduce visual clutter by making the box fill less dominant, using a contrasting border, or displaying fewer groups per chart. Overlap is a real limitation when many observations share similar values.
Quartiles do not match another tool: Compare the Inclusive median and Exclusive median setting with the other tool’s calculation method. Different quartile conventions can produce different box edges without either chart necessarily being wrong.

When to use a different chart

A box plot with individual points is a strong choice when you want both a compact summary and a view of the underlying observations. For very large datasets, however, markers can crowd together and obscure density. A histogram can show how frequently values fall within ranges, while a dot plot can make small samples easier to inspect point by point. A scatter chart is more suitable when each observation also has a meaningful second numeric variable, such as time or dose.

These alternatives answer different questions. A histogram emphasizes frequency across bins; a box plot emphasizes quartiles, median, and spread; a scatter chart emphasizes relationships between two numeric measures. Choose based on the question and the number of observations, not just on which chart looks more familiar. For comparisons across groups, use consistent scales and quartile settings so that visual differences are not caused by formatting choices.

Frequently Asked Questions

How do I show all data points on an Excel box plot?

Select the chart series, open Format Data Series, and under Series Options turn on both Show inner points and Show outlier points. Inner points cover observations within the whiskers; the outlier option adds observations plotted beyond them.

Can I show only outliers in an Excel box plot?

Yes. Leave Show inner points unchecked and select Show outlier points. The chart will show the box-and-whisker summary and the outlier markers, but not the ordinary observations inside the whiskers.

Why do the points overlap?

Observations with the same or very similar values can be drawn in the same area, making several markers look like one. Consider a dot plot for a small sample or a histogram for a large one if the number or density of values is the main thing readers need to see.

Does Excel’s whisker include the minimum and maximum?

Not necessarily. In the conventional 1.5-IQR rule, whiskers end at the most extreme values still within the allowed range, and more extreme observations appear as outliers. Review the chart’s point settings and the data rather than assuming the whisker endpoints are the dataset’s absolute minimum and maximum.

Why do my quartiles differ from another box plot?

Excel provides Inclusive median and Exclusive median quartile calculations, and other programs may use a different convention. Select a consistent method and compare the settings before treating a difference in box edges as a data error.

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.