The Box and Whisker plot in Excel

The Box Plot (a.k.a. Box-and-Whisker Plot) provides an extremely useful alternative to looking at a data set in an effort to determine its central tendency, spread, skewness, and the existence of outliers.

A Box Plot focuses on five summary measures of the distribution of the data:

1. The median
2. The lower quartile
3. The upper quartile
4. The smallest observation
5. The largest observation

The standard model Box Plot in Excel is cluttered with dark shadings and heavy contours activating the negative white space between the data series. Moreover, the Box plot in Excel is only vertical with limited abaility to control its formatting.

So here I'm offering you an Excel template that takes a data set as an input and creates a horizontal one-dimensional scatterplot with the Box plot.

The Excel model has the following capabilities:

- Show a summary table of the descriptive statistics by category.
- Show/hide the distribution of the points along with the Box plot.
- Sort categories by alphabetical order, mean, median, range or maximum values.
- Sort categories by descending or ascending order.
- Highlight the median, mean, maximum and minimum values by category.
- Highlight a selected (by user) category with a different color using a dropdown list.
- Highlight selected (by user) data points with their labels using a dropdown list.
- Jitter data to reduce overplotting. Amount of jitter can be adjusted using a spinner.
- Define the number formatting of the labels.
- User-friendly interface to adjust the above settings.
- Data entered into Excel Table—hence the chart and formulas update automatically when the datasource expands or shrinks.
- The size of the data matrix is only limited by Excel's charting and calculating capabilities.
- Fully customizable chart using standard Excel functionality.
- Unlocked workbook/worksheets with ability to expand template to suit your needs.

System requirements

The template relies heavily on Excel's Dynamic Arrays capabilities, only available to Microsoft 365 subscribers.

What's the refund policy?

If what you see is not what you expected, just reply to the download email within 30 days, and you'll get a full refund. No questions asked.