How to Make a Box Plot in Excel

Box plots are a useful way to show data distribution in Microsoft Excel. However, Excel doesn't have a box plot chart template. That doesn't mean it's impossible or even difficult to create one. Keep reading to learn how to make a box plot in Excel using a stacked column chart and a few additional steps.

Set up Data for a Box Plot

Excel displays the distribution of numbers when you create a plot chart. The first thing to do is set up the data you want to display in your box plot chart. This example will use two columns of data, but you could use more if necessary.

1. Enter a heading for each column. To use the example data, enter 2017 in D3 and 2018 in E3.

   Although the rows are labeled in the example, these labels are not used in the creation of the chart, so enter them if you choose or skip this step.

   

2. Enter the data in the cells in each column.

   

3. Save the worksheet with the data table you created.

Enter Plot Chart Formulas

Calculating the quartile values is required in order to make a box plot chart. Make another table populated with formulas to calculate the minimum, maximum, and median values, from the table as well as the first and third quartiles.

1. Choose where you want to enter the formulas to calculate the quartile values. For the example box plot chart, the formulas will be entered in cells H4 through H8. The rows in this table will contain the following data:

   • Minimum value
   • First quartile
   • Median value
   • Third quartile
   • Maximum value

2. Enter the formula =MIN(cell range) into the first cell. To follow the example, enter =MIN(D4:D15) into cell H4.

   

3. Enter the formula =QUARTILE.INC(cell range, 1) into the next cell. To follow the example, enter =QUARTILE.INC(D4:D15, 1) into cell H5.

   

4. Enter the formula =QUARTILE.INC(cell range, 2) into the next cell. To follow the example, enter =QUARTILE.INC( D4:D15, 2) into cell H6.

   

5. Enter the formula =QUARTILE.INC(cell range, 3) into the next cell. To follow the example, enter =QUARTILE.INC(D4:D15, 3) into cell H7.

   

6. Enter the formula =MAX(cell range) into the next cell. To follow the example, enter =MAX(D4:D15) into cell H8.

   

7. Copy the formulas into the next column. If your data table has more than two columns, copy the formulas into as many columns as your table contains. The formulas will automatically correlate with the columns in the table.

Calculate Quartile Differences

The differences between each phase must be calculated before creating the chart. Create a third table to calculate the differences between the following.

• First quartile and minimum value
• Median and first quartile
• Third quartile and median
• Maximum value and third quartile

1. Choose where you want to enter the formulas to calculate the quartile values. For the example box plot chart, the formulas will begin in cell L4.

2. In the first cell, enter the minimum value for the first column. To follow the example, enter =H4 in cell L4.

   

3. In the next cell, find the difference between the first quartile and minimum value. To follow the example, enter =IMSUB(H5,H4) in cell L5.

   

4. Copy the formula down to the other cells in the column. The formulas will automatically correlate with the desired cells.

5. Copy the formulas from the cells in the first column to the column to the right to calculate the quartile values for the data in the second column of the formula table.

   

6. Save the changes to your worksheet.

Create a Stacked Column Chart

Using the data in the third table, create a stacked column chart, which can be modified to make a box plot chart.

1. Select all the data from the third table. Choose the Insert tab, point to Insert Column Chart and select Stacked Column.

   Because Excel uses horizontal data sets to create stacked columns, the chart will not resemble a box plot initially.

   

2. Right-click the chart and choose Select Data. The Select Data Source dialog box will open.

   

3. Select the Switch Row/Column button in the center of the dialog. Choose OK. The chart will convert to a standard box plot.

   

You can format the chart as desired by changing the chart title, hiding the bottom data series, selecting different chart styles or colors, and more.

Alternatively, you can make further modifications and convert the chart into a box and whisker plot chart.