This tutorial provides a step-by-step example of how to create the following horizontal box plot in Excel:
Let’s jump in!
Step 1: Enter the Data
First, let’s create the following dataset that shows the points scored by 15 different basketball players:
Step 2: Calculate Values for the Plot
Suppose we would like to create a horizontal box plot to visualize the distribution of points values.
A box plot (sometimes called a box-and-whisker plot) is a plot that shows the five-number summary of a dataset, which includes the following values:
- Minimum
- First Quartile
- Median
- Third Quartile
- Maximum
The following screenshot shows how to calculate these five values along with the values to be shown on the horizontal box plot:
Step 3: Insert Bar Chart
Next, highlight the cell range D7:D9, then click the Insert tab along the top ribbon, then click the icon called Stacked Bar within the Charts group.
The following bar chart will be created:
Next, right click anywhere on the chart, then click Select Data from the dropdown menu that appears, then click the button called Switch Row/Column.
Once you click OK, the chart will now look like this:
Step 4: Transform Bar Chart into Box Plot
Next, right click on the blue bar, then click the Fill icon, then click No Fill:
Next, click on the bar with no fill, then click the tiny green plus sign in the top right corner of the plot, then click the dropdown arrow next to Error Bars, then click More Options:
In the Format Error Bars panel that appears on the right side of the screen, click Custom, then click the button called Specify Value:
In the new window that appears, type 0 for the Positive Error Value and D10 for the Negative Error Value box:
Repeat the process for the grey box, instead using 0 for the Negative Error Value and D11 for the Positive Error Value:
Lastly, right click on the orange bar and choose No Fill for the Fill and Black for the Outline.
Repeat this process for the grey bar.
The final box plot will look like this:
From the box plot we can see the five number summary of the dataset:
- The left whisker tells us that the minimum value is 5.
- The line on the left side of the box tells us that Q1 is 6.
- The line inside the box tells us that the median value is 8.
- The line on the right side of the box tells us that Q3 is 12.
- The right whisker tells us that the maximum value is 17.
Step 5: Customize the Box Plot (Optional)
Feel free to add a title, customize the colors, and adjust the width of the whiskers to make the plot easier to read.
The final plot may look like this:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Add Average Line to Bar Chart in Excel
How to Make a Frequency Polygon in Excel
How to Add a Horizontal Line to Scatterplot in Excel
