Locked lesson.
About this lesson
Create a Box and Whisker (Box plot) chart in Excel to display the distribution of data based on a five-number summary.
Exercise files
Download this lesson’s related exercise files.
4.08 box-and-whisker-charts - Exercise.docx100.4 KB 4.08 box-and-whisker-charts - Exercise solution.docx
5.8 MB Exercise - Box and Whisker Chart.xlsx
10.3 KB Exercise - Box and Whisker Chart - Solution.xlsx
15.3 KB Workbook - Box and Whisker Charts.xlsx
11.4 KB Workbook - Box and Whisker Charts - Complete.xlsx
16.1 KB
Quick reference
Box and Whisker Charts
Create a Box and Whisker Chart to display the distribution of data.
When to use
We use a Box and Whisker Chart whenever we want to display the distribution of data based on a five-number summary. This chart type is useful for identifying outliers and understanding the spread and skewness of data.
Instructions
Create a Box and Whisker Chart
- Select the data.
- From the Insert tab, in the Charts group, click the diagonal arrow in the corner of the group.
- Click on All Charts.
- Click Box & Whisker.
- Click OK.
Understanding the Box and Whisker Chart
The box height shows us the interquartile range which represents the middle 50% of the data. A larger box would indicate more variability in the middle part of the data. The line is the median line and represents the median value. If the line is not in the center of the box it means there is a skewness in the data.
The bottom edge of the box indicates the first quartile which represents the lower half of the dataset meaning 25% of the data points are below this value.
The top edge of the box represents the third quartile. This is the median or the upper half of the dataset meaning 75% of the data points are below this value.
The 'whiskers' are the lines you see coming out of the box. The lower whisker extends from quartile 1 to the smallest data point. The upper whisker extends from quartile 3 to the highest data point.
This shows us our data distribution.
Format a Box and Whisker Chart
Change the Box Color
- Click on the boxes.
- From the Format tab, click the Shape Fill drop-down.
- Change the color to something light.
Change the Gap Width
- Press CTRL+1 to open the Format Data Series pane.
- Move the Gap width slider to adjust the width of the boxes.
Series Options
We can show the inner points, outlier points, mean markers, and mean line by selecting the relevant check boxes in Series Options.
Adjust the Axis
- Click on the vertical axis and press CTRL+1.
- From Axis Options, change the minimum bound to 2000 and the maximum bound to 7500.
Add a Chart Title
- Double-click in the chart title text box.
- Type 'Distribution of Monthly Income'.
Login to download
Sorry, we don’t have a transcript for this lesson yet.
Lesson notes are only available for subscribers.