Boxplots are a useful way to show the distribution of data in Microsoft Excel. However, Excel does not have a boxplot template. This does not mean that it is impossible or even difficult to create one. Read on to learn how to create a boxplot in Excel using a stacked column chart and a few extra steps.
These instructions apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Microsoft 365, and Excel Online.
Create data for a boxplot
Excel shows the distribution of numbers when you create a chart. The first thing you need to do is define the data you want to show in your boxplot. This example uses two columns of data, but you can use more if needed.
- Enter a title for each column. To use sample data, enter 2017 in D3 etc 2018 in E3.
Although the lines are labeled in the example, these labels are not used when creating the chart, so enter them if you select or skip this step.
- Enter data into the cells in each column.
- Save the spreadsheet with the data table you created.
Enter chart formulas
Calculating quartile values is necessary to create a boxplot. Create another table with formulas to calculate the minimum, maximum, and mean values from the table, as well as the first and third quartiles.
- Choose where you want to enter the formulas to calculate the quartile values. For the boxplot example, the formulas are entered in cells H4 through H8. The rows of this table contain the following data:
- minimum value
- First quartile
- median value
- Third quartile
- maximum value
- Enter formula =MIN(cell range) in the first cell. To follow the example, enter =MIN(D4:D15) in the cell H4.
- Enter formula =QUARTILE.INC(cell range, 1) in the next cell. To follow the example, enter =QUARTILE.INC(D4:D15, 1) in the cell H5.
- Enter formula =QUARTILE.INC(cell range, 2) in the next cell. To follow the example, enter =QUARTILE.INC( D4:D15, 2) in the cell H6.
- Enter formula =QUARTILE.INC(cell range, 3) in the next cell. To follow the example, enter =QUARTILE.INC(D4:D15, 3) in the cell H7.
- Enter formula =MAX(cell range) in the next cell. To follow the example, enter =MAX(D4:D15) in the cell H8.
- 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 are automatically assigned to the columns of the table.
Calculate quartile differences
The differences between each phase must be calculated before creating the diagram. Create a third table to calculate the differences between the following phases.
- First quartile and minimum value
- Median and first quartile
- Third quartile and median
- Maximum and third quartile
- Choose where you want to enter the formulas to calculate the quartile values. For the boxplot example, the formulas start in cell L4.
- In the first cell, enter the minimum value for the first column. To follow the example, enter =H4 in the cell L4.
- In the next cell, find the difference between the first quartile and the minimum value. To follow the example, enter =IMSUB(H5,H4) in the cell L5.
- Copy the formula to the other cells in the column. The formulas are automatically assigned to the desired cells.
- Copy the formulas from the cells in the first column to the right column to calculate the quartile values of the data in the second column of the formula table.
- Save changes to your worksheet.
Create a stacked column chart
Using the data from the third table, create a stacked column chart that can be changed to a box chart.
- Select all data from the third table. Select the insertion tab, point to Insert a column chart and select Stacked Column.
Because Excel uses horizontal datasets to create stacked columns, the chart doesn’t look like a boxplot at first.
- Right-click on the chart and select select data. The website Select data source open for.
- Choose Change row/column at the center of the dialogue. Choose OK. The chart is converted to a standard boxplot.
You can format the chart however you want by changing the chart title, hiding the background data series, choosing different chart styles or colors, and more.
You can also make additional changes and convert the chart to a box and whisker chart.