How to Make a Box and Whisker Plot in Excel

Display a five-number summary of data

Learn how to make a box and whisper plot

Helloquence / Unsplash

 

In Microsoft Excel, a box plot uses graphics to display groups of numerical data through five values, called quartiles. Box plot charts can be 'dressed up' with whiskers, which are vertical lines extending from the chart boxes. The whiskers indicate variability outside the upper and lower quartiles.

Box and whisker plots are typically used to depict information from related data sets than have independent sources, such as test scores between different schools or data from before and after changes in a process or procedure. 

In recent versions of Excel, you can create a box and whisker chart using the Insert Chart tool. Although older versions of Excel don't have a box and whisker plot maker per se, you can create one by converting a stacked column chart into a box plot and then adding the whiskers.

These instructions apply to Excel 2019, Excel 2016, Excel for Office 365, Excel 2013, and Excel 2010.

Use Excel's Box and Whisker Plot Maker

For Excel 2019, Excel 2016, or Excel for Office 365, make a box and whisker plot chart using the Insert Chart tool.

  1. Enter the data you want to use to create a box and whisker chart into columns and rows on the worksheet. This can be a single data series or multiple data series.

    Screenshot of data for box and whisker chart
  2. Select the data you want to use to make the chart.

    Screenshot of selected data
  3. Select the Insert tab.

    Screenshot of Insert tab in Excel
  4. Select Recommended Charts in the Charts group or select the dialog box launcher in the lower-right corner of the charts group. The Insert Chart dialog box will open.

    Screenshot of Insert Chart dialog box
  5. Select the All Charts tab.

    Screenshot of Insert Chart dialog with All Charts tab selected
  6. Select Box and Whisker and choose OK. A basic box and whisker plot chart will appear on the worksheet.

    Screenshot of Box and Whisker chart option

Transform a Box Plot Chart into a Box and Whisker Plot

For Excel 2013 or Excel 2010, start with a stacked column chart and transform it into a box and whisker plot chart.

Create a basic box plot chart in Excel and then add the whiskers.

Screenshot of basic box plot chart in Excel

Add the Top Whisker

The whiskers on a box and whisker box plot chart indicate variability outside the upper and lower quartiles. Any data point that falls outside the top or bottom whisker line would be considered an outlier when analyzing the data.

  1. Select the top box and then select Add Chart Element on the Chart Design tab.

    Screenshot of Add Chart Element
  2. Select Error Bars and choose More Error Bar Options. The Format Error Bars menu will open.

    Screenshot of More Error Bars Options
  3. Select Plus under Direction in Error Bars Options.

    Screenshot of Plus under Direction
  4. Select Custom and choose Specify Value in the Error Amount Section. The Custom Error Bars dialog box will open.

    Screenshot of Custom Error Bars dialog

Add the Bottom Whisker

Once you've added the top whiskers, then you can add the bottom whiskers in a similar fashion.

  1. Select the bottom box and select Add Chart Element on the Chart Design tab.

  2. Select Error Bars and choose More Error Bar Options. The Format Error Bars menu will open.

    Screenshot of More Error Bars Options
  3. Select Minus under Direction in Error Bars Options.

    Screenshot of Minus under Direction in Error Bars Options.
  4. Select Custom and choose Specify Value in the Error Amount Section. The Custom Error Bars dialog box will open.

    Screenshot of Custom Error Bars dialog
  5. Delete the contents of the Positive Error Value box. Select the bottom values on the worksheet and select OK to close the Custom Error Bars window.

Format a Box and Whisker Plot Chart in Excel

Once you have created the chart, use Excel's chart formatting tools to

  1. Select Chart Title and enter the title you want to appear for the chart.

    Screenshot of Chart Title
  2. Right-click one of the boxes on the chart and choose Format Data Series to open the Format Data Series pane.

    Screenshot of Format Data Series in right-click menu
  3. Increase or decrease the Gap Width to control the spacing of the gap between the boxes.

    Screenshot of Gap Width
  4. Select Show Inner Points to display the data points between the two whisker lines.

    Screenshot of Show Inner Points
  5. Select Show Outlier Points to display outliers below or above the whisker lines.

  6. Select Show Mean Markers to display the mean marker of the data series.

  7. Select Show Mean Line to display the line connecting the means of the boxes in the data series.

  8. Select a method for Quartile Calculation:

    • Inclusive Median is included in the calculation if the number of values in the data is odd.
    • Exclusive median is excluded from the calculation if there are an odd number of values in the data.
    Screenshot of Inclusive Median
  9. Select the next box in your plot chart to customize it in the Format Data Series pane and repeat for any remaining boxes.

Edit or Change the Appearance of the Box and Whisper Plot

To make changes to the appearance of your box and whisker plot chart, select any area of the chart and then choose Chart Design or Design Tools on the Chart Tools tab, depending on which version of Excel you are using.

Modify factors such as the chart layout, style, or colors using the same methods described above.

Screenshot of Chart Styles