A box and whisker plot (also called a box plot) is one of the best charts for visualizing the distribution of data. It shows the median, quartiles, minimum and maximum values, and potential outliers, making it a favorite tool for statisticians, students, researchers, and business analysts.
If you’ve been wondering how to make a box and whisker plot in Excel, you’re in the right place. Microsoft Excel includes a built-in Box & Whisker chart that allows you to create professional-looking plots in just a few clicks. Even if you’re using an older version of Excel without this feature, there are alternative methods to build a similar chart.
This comprehensive guide will walk you through every step, explain how box plots work, provide customization tips, troubleshoot common problems, and answer frequently asked questions.
Quick Summary: Key Takeaways
- A box and whisker plot displays the spread and distribution of numerical data.
- Excel 2016, Excel 2019, Excel 2021, Excel for Microsoft 365, and newer versions include a built-in Box & Whisker chart.
- Older versions of Excel require creating a box plot manually.
- Properly organized data produces the best results.
- You can customize colors, quartile calculations, outliers, and chart styles.
What Is a Box and Whisker Plot?
A box and whisker plot is a statistical chart that summarizes a dataset using five key values:
- Minimum value
- First quartile (Q1)
- Median (Q2)
- Third quartile (Q3)
- Maximum value
The chart may also display outliers, which are values that fall significantly outside the normal data range.
Instead of showing every individual number, a box plot gives you a quick overview of how your data is distributed.
Why Use a Box and Whisker Plot?
Box plots are useful because they help you:
- Compare multiple datasets
- Identify outliers
- Understand data spread
- Visualize the median
- Detect skewed distributions
- Analyze statistical variation
They’re commonly used in:
- Education
- Scientific research
- Finance
- Healthcare
- Manufacturing
- Quality control
- Business analytics
Which Versions of Excel Support Box and Whisker Charts?
The built-in Box & Whisker chart is available in:
| Excel Version | Supported |
|---|---|
| Excel 365 | ✅ Yes |
| Excel 2021 | ✅ Yes |
| Excel 2019 | ✅ Yes |
| Excel 2016 | ✅ Yes |
| Excel 2013 | ❌ No |
| Excel 2010 | ❌ No |
| Excel 2007 | ❌ No |
If you’re using Excel 2013 or earlier, you’ll need to create the chart manually.
Prepare Your Data
Before creating the chart, organize your data into columns.
Example:
| Class A | Class B | Class C |
|---|---|---|
| 75 | 68 | 81 |
| 82 | 74 | 79 |
| 90 | 88 | 95 |
| 70 | 72 | 85 |
| 84 | 80 | 92 |
| 78 | 69 | 87 |
Each column represents one category.
How to Make a Box and Whisker Plot in Excel
Follow these simple steps.
Step 1: Enter Your Data
Open Excel and place your numerical data into columns.
Avoid blank rows inside the dataset.
Step 2: Select the Data
Click and drag to highlight all the values you want to include.
Include column headers if available.
Step 3: Open the Insert Tab
At the top of Excel, click:
Insert
Step 4: Open the Statistical Chart Menu
Locate the Insert Statistic Chart button.
Click the drop-down arrow.
Step 5: Select Box & Whisker
Choose:
Box & Whisker
Excel instantly generates the chart.
Step 6: Resize and Position the Chart
Drag the corners to resize the chart.
Move it to your preferred location on the worksheet.
Understanding the Parts of a Box Plot
Knowing what each section represents helps you interpret the chart correctly.
| Part | Meaning |
|---|---|
| Lower whisker | Minimum value (excluding outliers) |
| Bottom of box | First quartile (Q1) |
| Line inside box | Median |
| Top of box | Third quartile (Q3) |
| Upper whisker | Maximum value (excluding outliers) |
| Dots | Outliers |
How to Customize a Box and Whisker Plot
Excel offers several formatting options.
Change Chart Title
Click the title and type your own.
Example:
Monthly Sales Distribution
Change Colors
- Select the chart.
- Open Chart Design.
- Choose Change Colors.
Add Data Labels
Some versions of Excel allow additional labels for easier interpretation.
Modify Quartile Calculation
To change how quartiles are calculated:
- Right-click the chart.
- Select Format Data Series.
- Choose:
- Inclusive median
- Exclusive median
Show or Hide Outliers
Within Format Data Series, you can:
- Show outlier points
- Hide outlier points
How to Compare Multiple Groups
One major advantage of box plots is comparing categories.
For example:
| Department | Scores |
|---|---|
| Sales | 82–96 |
| Marketing | 70–91 |
| HR | 68–89 |
Each department appears as its own box plot.
This makes differences easy to spot.
How to Interpret a Box Plot
When analyzing your chart, look for:
Median
The center line shows the middle value.
Spread
A taller box indicates greater variation.
Outliers
Dots outside the whiskers represent unusual values.
Symmetry
A centered median suggests balanced data.
A shifted median may indicate skewness.
Common Problems and Solutions
Box & Whisker Option Missing
Possible reasons:
- You’re using Excel 2013 or earlier.
- Office needs updating.
Upgrade to Excel 2016 or later.
Chart Looks Incorrect
Check that:
- Data is numeric.
- No text values are included.
- Blank cells are removed.
Outliers Not Showing
Excel only displays outliers when values fall outside the calculated range.
Data Is Grouped Incorrectly
Ensure:
- Each category has its own column.
- Headers are correctly selected.
Tips for Better Box Plots
To create professional charts:
- Label every category clearly.
- Use consistent colors.
- Remove unnecessary formatting.
- Keep datasets clean.
- Add descriptive chart titles.
Advantages of Box and Whisker Plots
Box plots provide several benefits:
- Easy statistical summary
- Quick comparison between groups
- Clear visualization of variation
- Highlights outliers
- Professional presentation
Box Plot vs. Histogram
| Feature | Box Plot | Histogram |
|---|---|---|
| Shows median | ✅ Yes | ❌ No |
| Shows quartiles | ✅ Yes | ❌ No |
| Shows outliers | ✅ Yes | Sometimes |
| Displays frequency | ❌ No | ✅ Yes |
| Compares datasets | ✅ Excellent | Moderate |
Best Practices
For the most accurate results:
- Use numerical data only.
- Include enough observations.
- Avoid missing values.
- Compare similar datasets.
- Keep formatting simple and readable.
Conclusion
Learning how to make a box and whisker plot in Excel is an excellent skill for anyone working with data. Excel’s built-in Box & Whisker chart makes it easy to visualize data distribution, identify outliers, and compare multiple datasets without performing complex calculations manually.
By organizing your data correctly, using the built-in statistical chart, and customizing the appearance, you can create clear, professional charts that make data analysis easier to understand. Whether you’re a student, researcher, or business professional, mastering box plots will help you present statistical information more effectively.
Frequently Asked Questions (FAQs)
1. How do I create a box and whisker plot in Excel?
Select your data, go to the Insert tab, choose Insert Statistic Chart, and click Box & Whisker.
2. Why can’t I find the Box & Whisker chart in Excel?
The feature is only available in Excel 2016, Excel 2019, Excel 2021, Excel for Microsoft 365, and newer versions.
3. What does the box represent in a box plot?
The box represents the middle 50% of your data, from the first quartile (Q1) to the third quartile (Q3).
4. What are the dots outside the whiskers?
They represent outliers, which are values significantly higher or lower than the rest of the dataset.
5. Can I create a box plot in Excel 2013?
Excel 2013 doesn’t include a built-in Box & Whisker chart. You’ll need to create one manually using calculated quartiles and stacked charts, or upgrade to a newer version of Excel that supports the feature.