How to Make a Box and Whisker Plot in Excel

Display a five-number summary of data

By

Updated on September 30, 2020

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.

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.
Select the data you want to use to make the chart.
Select the Insert tab.
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.
Select the All Charts tab in the Insert Chart dialog box.
Select Box and Whisker and choose OK. A basic box and whisker plot chart appears on the worksheet.

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.

Select the top box on the chart and then select Add Chart Element on the Chart Design tab.
Select Error Bars and choose More Error Bar Options to open the Format Error Bars menu.
Select Plus under Direction in Error Bars Options.
Select Custom and choose Specify Value in the Error Amount Section to open the Custom Error Bars dialog box .

Add the Bottom Whisker

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

Select the bottom box on the chart and select Add Chart Element on the Chart Design tab.
Select Error Bars and choose More Error Bar Options to open the Format Error Bars menu.
Select Minus under Direction in Error Bars Options.
Select Custom and choose Specify Value in the Error Amount Section. The Custom Error Bars dialog box will open.
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.

Select Chart Title and enter the title you want to appear for the chart.
Right-click one of the boxes on the chart and choose Format Data Series to open the Format Data Series pane.
Increase or decrease the Gap Width to control the spacing of the gap between the boxes.
Select Show Inner Points to display the data points between the two whisker lines.
Also in the Format Data Series pane, select Show Outlier Points to display outliers below or above the whisker lines.
Select Show Mean Markers to display the mean marker of the data series.
Select Show Mean Line to display the line connecting the means of the boxes in the data series.
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.
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.