How to Make a Box and Whisker Plot in Excel

Display a five-number summary of data

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, 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 Microsoft 365, Excel 2013, and Excel 2010.

Use Excel's Box and Whisker Plot Maker

For Excel 2019, Excel 2016, or Excel for Microsoft 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.

    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) to open the Insert Chart dialog box.

    Excel with Recommended Charts highlighted
  5. Select the All Charts tab in the Insert Chart dialog box.

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

    Box & Whisker option in Excel

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 on the chart and then select Add Chart Element on the Chart Design tab.

    Add Chart Element highlighted
  2. Select Error Bars and choose More Error Bar Options to open the Format Error Bars menu.

    Excel with More Error Bars Options highlighted
  3. Select Plus under Direction in Error Bars Options.

    Plus chosen under Direction
  4. Select Custom and choose Specify Value in the Error Amount Section to open the Custom Error Bars dialog box .

    Custom Error Bars dialog in Excel

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 on the chart and select Add Chart Element on the Chart Design tab.

  2. Select Error Bars and choose More Error Bar Options to open the Format Error Bars menu.

    More Error Bars Options in Excel
  3. Select Minus under Direction in Error Bars Options.

    Minus selected 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.

    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.

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

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

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

    Gap Width slider in Excel
  4. Select Show Inner Points to display the data points between the two whisker lines.

    Show Inner Points highlighted in Excel
  5. Also in the Format Data Series pane, 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.
    Quartile Calculation options in Excel Format Data Series pane
  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 the 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.

Chart Design options in Excel