You’re not the only one who has ever glanced at a huge spreadsheet full of statistics and felt overwhelmed. For a lot of students and professionals, the hard part isn’t simply getting the data, but figuring out what it means. This is exactly what a box and whisker plot in Excel is meant to do.
What is Box and Whisker Plot in Excel?
It is a certain kind of chart that shows how data is spread out. It is often called a “box plot.” Unlike a bar chart that shows totals, this plot breaks your data into quartiles. It gives you a bird’s-eye view of how spread out your numbers are.
Box and Whisker Plot in Excel Explained
You might think of it as a summary of the “shape” of your data. The “box” shows the center 50% of your data points, where most of the action happens. The “whiskers” are the lines that go from the top and bottom of the box to show the highest and lowest values. You can see the range and the average all at once without having to look at each data point.
Parts of a Box and Whisker Plot
You need to know the five main parts of a box and whisker plot in order to master it:
- Minimum: The smallest number in the set, not including any that are too high or too low.
- First Quartile (Q1): The “25th percentile” mark is the first quartile.
- Median (Q2): The exact middle value of the data collection..
- Third Quartile (Q3): The “75th percentile” mark.
- Maximum: The highest number in the set, not including any outliers.
- Mean: This is the mathematical average, and it is usually indicated with a “X.”
- Outliers: The points that are quite far from the typical range. They are commonly depicted as single dots.
How to Create Box and Whisker Plot in Excel Step by Step
Making it in Excel is a lot easier in newer versions (2016 and after), because it is now a built-in chart type.
Preparing Your Data for Box and Whisker Chart in Excel
Make sure your data is in order before you click any buttons:
- Put your labels (categories) in a single column.
- Put your numbers in the next column.
- Make sure that there are no empty rows or text strings in your number columns.
Steps to Insert a Box and Whisker Chart in Excel
To make your chart, follow these steps:
- Choose your information: Make the cells with your labels and numbers stand out.
- Click on “Insert” in the top ribbon to go to the Insert Tab.
- Look for Statistical Charts: Find the icon that looks like a tiny histogram.
- Click on the box and whisker plot option to see the box and whisker plot.
- Review: Your chart will show up on your worksheet right away.
Customising the Box and Whisker Chart in Excel
You can make your chart look professional once it’s made:
- Double-click the series to open the “Format Data Series” box.
- Gap width: Change this to make the boxes wider or thinner.
- Display Inner Points: If you want to see each data point as a dot inside the box, tick this box.
- Mean Markers: You can toggle the ‘X’ that represents the average value.
Box and Whisker Plot in Excel from Summary Data
You might not always have the whole list of all the data points. You might only have the numbers that were calculated. Making an Excel file from summary data takes a different approach.
What is Summary Data for Box and Whisker Plot?
Summary data consists of pre-calculated values like the Min, Max, Median, and Quartiles. If you are provided with these five numbers instead of a raw list of 100 values, you are working with summary data.
Steps to Create Box and Whisker Plot Using Summary Data
Since the built-in box plot tool prefers raw data, using summary data often involves a “workaround” using stacked column charts:
- Create a Table: List your summary stats (Q1, Min, Median, etc.).
- Calculate Differences: Calculate the height of each segment (e.g., Median minus Q1).
- Insert Stacked Column: Create a stacked column chart.
- Format as Whiskers: Use “Error Bars” to represent the whiskers extending to the Min and Max values.
- Hide Bottom Boxes: Set the fill of the bottom-most box to “No Fill” to make it look like the box is floating.
Box and Whisker Plot in Excel with Examples
Let’s look at an example. Think of a store that sells light bulbs. They want to know how long bulbs last for two distinct brands.
| Statistics | Brand A (Hours) | Brand B (Hours) |
| Minimum | 800 | 950 |
| Q1 | 1000 | 1050 |
| Median | 1200 | 1100 |
| Q3 | 1400 | 1150 |
| Maximum | 1600 | 1250 |
How to Read a Box and Whisker Chart in Excel
In the example above, Brand A’s “box” is substantially bigger, which means that the bulbs last for a long time. Brand B’s box and whiskers are quite short, which means that its bulbs are very consistent, even though they don’t last as long as Brand A’s greatest bulb.
Uses of Box and Whisker Plot in Excel
- Real estate: Looking at how house values vary from one neighbourhood to the next.
- Healthcare: Looking at how long it takes for patients to get better at different hospitals.
- Manufacturing: Checking for differences in the sizes of parts to make sure they are of good quality.
Benefits of Using Box and Whisker Plot in Excel
Using a box and whisker plot offers several advantages over traditional tables or standard bar graphs.
Advantages of Visualizing Data with Box Plots
- Identifies Outliers: It clearly marks data points that are unusually high or low.
- Shows Symmetry: You can see if your data is “skewed” (leaning towards the high or low end).
- Compactness: You can compare 10 different categories in one small chart area without it looking cluttered.
Also Read:
FAQs
How do you create a box and whisker chart in Excel?
To create this plot in Excel, select your data, go to the 'Insert' tab, click the 'Statistical Chart' icon, and choose 'Box and Whisker'. This will automatically generate the plot based on your selected range.
What does a box and whisker plot show in Excel?
A box and whisker plot shows a five-number summary: the minimum, first quartile, median, third quartile, and maximum. It also highlights the mean and any outliers, providing a visual representation of data distribution.
Can you create this plot in Excel from summary data?
Yes, you can create a plot in Excel from summary data, though it requires using a stacked column chart with error bars. You must calculate the differences between your quartiles to manually build the sections of the plot.
What are quartiles in a box and whisker plot?
Quartiles divide your data into four equal parts. The first quartile (Q1) is the 25th percentile, the second (Q2) is the median or 50th percentile, and the third (Q3) is the 75th percentile. The "box" in the plot spans from Q1 to Q3.
Is the mean always shown in an Excel box plot?
By default, Excel includes a small 'X' inside the box to represent the mean (average). However, you can toggle this on or off in the 'Format Data Series' settings if you only want to focus on the median.
