Box plot in Excel

  • Post author:
  • Post category:Uncategorized

A box and whisker plot (box plot) shows data distribution in terms of median, minimum and maximum values, and the two quartiles: first and third.  It quickly shows how our data is spread out, if it is symmetric or skewed.

Figure 1.  Final result: Box plot

Preparing data to create a boxplot

These are the required data points for a box and whiskers plot:

  • Median – the middle value in our data set
  • First quartile – 25th percentile
  • Third quartile – 75th percentile  
  • Minimum – the lowest value in our data set
  • Maximum – the largest value in our data set

Suppose we have below table of values in cells B3:B13.  We then compute our required data using the functions MEDIAN, QUARTILE, MIN and MAX, as shown in Column F.  

Figure 2.  Data for boxplot

From the above data, we are able to prepare the charts that we are going to plot.  We will need to plot the following:

  • Hidden box, whose value or height is equal to Q1
  • Lower Box, equal to Median – Q1
  • Upper Box, equal to Q3 – Median
  • Top Whisker, equal to Maximum – Q3
  • Bottom Whisker, equal to Q1 – Minimum

Figure 3.  Chart values for boxplot

How to create a boxplot?

In newer Excel versions 2016 and 2013, the box and whisker plot chart type is already available.  However, it is still better to learn how to create a boxplot using only the Column Chart.  

With all the relevant values prepared, we can now create a box and whisker plot in Excel version 2016, 2013, or lower.

Plot the three boxes stacked on top of each other

Step 1.  Select cells D10:E12, click Insert tab > Column > Stacked Column

Figure 4.  Stacked column in Insert tab

A chart showing three columns will be created.  

Figure 5.  Insert column charts

Step 2.  

Click the chart, Design tab > Switch Row/Column

Figure 6.  Switch Row/Column option in Design tab

A stacked column will be created.  

Figure 7. stacked column

Figure 8.  Output: Stacked boxes for boxplot

Insert the whiskers

Step 3. Select the upper box, click Layout Error Bars More Error Bars Options

Figure 9.  Error Bars in Layout tab

The Format Error Bars dialog box will appear.  

Step 4.  Click Vertical Error Bars

  • Display Direction Plus
  • Error Amount > Custom Specify value

Figure 10.  Customize top whiskers for boxplot

The Custom Error Bars dialog box will appear.

Step 5.  In the Positive Error Value, click then select cell E13.  Click OK.  

Figure 11.  Custom positive error value for whiskers

Figure 12.   Output: Top whisker added

Step 6. Select the lower box, click Layout Error Bars > More Error Bars Options

Step 7.  In Format Error Bars, do the following:

  • Click Vertical Error Bars
  • Display Direction > Minus
  • Error Amount > Custom Specify value > Negative Error Value
  • Click  and select cell E14

Figure 13.   Custom negative error value for whiskers

Our box and whisker plot is now coming together.

Figure 14.  Output: Bottom whisker added

Customize boxplot

Step 8.  Next, we customize and clean up our graph by doing the following:

  • Right-click the Hidden box (blue) and select Format Data Series

Figure 15.  Format Data Series in Menu options

  • In Format Data Series, click Fill and select No Fill

Figure 16.  Hide bottom box to show whisker

  • Click the upper box and change Fill Color to Blue, Accent 1, Lighter 60%

Figure 17.  Customize upper box fill color for boxplot

  • Click Border Color Solid Line > Black

Figure 18.  Customize upper box border color for boxplot

  • Do the same for the lower box: change fill color to Blue, Accent 1, Lighter 60% and Border Color to Black

Figure 19.  Output: Customize boxes and whiskers for boxplot

  • Delete the legend and the horizontal axis

Figure 20.  Delete legend and horizontal axis

  • Add a chart title by clicking Layout tab > Chart Title > Above Chart

Figure 21.  Output: Box plot