How to Create and Customize a Box and Whisker Plot in Excel

Creating box and whisker plots in Excel is a valuable skill for any data analyst. These plots provide a clear summary of our data distribution, helping us make informed decisions based on our analysis.

In this tutorial, we'll learn why box and whisker plots are important, why Excel is good for creating them, and how to build these kinds of plots in Excel—both from scratch and in a very quick and straightforward way. In addition, we'll explore how to customize a box and whisker plot in Excel to make it more insightful.

The Quick Answer: How to Create a Box and Whisker Plot in Excel

To create a box and whisker plot in Excel, follow these steps:

• Select the Excel cells containing the values to be plotted. 
• Open the Insert tab. 
• Click on the Recommended Charts button. 
• Open the All Charts tab. 
• Select the Box & Whisker chart type. 
• Press OK.

Why Box and Whisker Plots Are Important

Box and whisker plots, also known as box plots or box and whisker diagrams, are a powerful type of visualization used to display the distribution of a data series. They are particularly helpful for statistical data analysis since they allow us to:

• Gather a comprehensive statistical summary of the data in interest, including the minimum, maximum, median, and mean values, as well as the first and third quartiles (Q1 and Q3).
• Understand the overall data distribution at a high level.
• Figure out if our data distribution is normal or skewed (and the direction of skewness).
• Identify the spread of our data.
• Detect outliers and their magnitude.
• Estimate data variability.
• Determine the best measure of center (median or mean).
• Compare the distribution of multiple categories next to each other.

To quickly start with Excel, consider taking the Introduction to Excel course.

Why Excel Is Good For Creating Box and Whisker Plots

Excel allows us to create a box and whisker plot in just a few quick steps. Excel also makes it easy to customize our box and whisker plot, and it isn't only about adjusting colors and lines. For example, we can decide whether or not we want to show or hide the raw data points, the outliers, and the mean, or whether to include or exclude the median when calculating the quartiles. 

Excel is also a good choice because our box and whisker plot is likely part of a larger data analysis project, for which Excel offers powerful tools for data manipulation, visualization, and analysis. It’s no wonder Excel is widely used by companies all over the world. Finally, building data visualizations in Excel is intuitive, straightforward, and doesn't require any coding skills.

Methods to Create a Box and Whisker Plot in Excel

In Excel, it's possible to quickly create a box and whisker plot by using a dedicated feature. Alternatively, we can decide to opt for the long way and do it from scratch. In both cases, Excel allows us to create either a single box and whisker plot or a set of plots next to each other, each for a separate category of the data. In addition, it's possible to build a horizontal box and whisker plot (or multiple plots) in Excel.

For simplicity, let's assume throughout the next three sections that we want to create a single box and whisker plot for a single column of values in Excel. 

Let’s consider a simple table containing the ages of cats and dogs from a hypothetical pet adoption center. The task will be to apply various Excel approaches to create box and whisker plots displaying the statistics for the ages of those animals.

An example Excel table with cats and dogs. Image by Author

If you want to unlock the full power of Excel and learn how to prepare, visualize, and analyze data from zero to an advanced level, the Excel Fundamentals skill track is the right place to start.

Creating a basic box and whisker plot: the long way 

First, let's familiarize ourselves with box and whisker plots by building one from scratch. 

We'll split this section into subsections but use continuous step numbering throughout the whole section. This will help us realize the number of steps needed to create a box and whisker plot in Excel from scratch—and the relative complexity of the whole process compared to the easy way, which we're going to revisit in the next section.

Calculating the statistical values

To start, you need to define a specific area on your Excel worksheet where you’ll calculate and store statistical and auxiliary values.

1. On the same Excel worksheet, create an area for calculating and storing statistical values. Let’s call this area a statistical table. You can place it to the right or below your main table.
2. For clarity, consider delineating the statistical table (select the table—right-click—select Format Cells...—open the Border tab—click on Outline—press OK).
3. In the first column of the statistical table, enter the following, one per row:
• “Min“
• “Q1“
• “Median“
• “Q3“
• “Max“
• “Mean“
4. In the second column of the statistical table, calculate each statistical notation using the following Excel formulas. Here, your_data_range refers to the column of values for which you want to create a plot:
• =MIN(your_data_range)
• =QUARTILE.EXC(your_data_range, 1)
• =MEDIAN(your_data_range)
• =QUARTILE.EXC(your_data_range, 3)
• =MAX(your_data_range)
• =AVERAGE(your_data_range)

Calculating the box heights and whisker lengths

Next, you need to determine the dimensions for the boxes and whiskers that will form the core components of your plot.

5. In the third column of the statistical table, enter the names of the values to be calculated, one per row:
• “Underlying box height“
• “Lower box height“
• “Upper box height“
• “Lower whisker length“
• “Upper whisker length“
6. In the fourth column of the statistical table, calculate each value in interest using the following formulas:
• =Q1
• =Median-Q1
• =Q3-Median
• =Q1-Min
• =Max-Q3

Calculating a statistical table in Excel. Formulas shown. Image by Author

Calculating a statistical table in Excel. Results. Image by Author

Creating the boxes

Now that you have your statistical values, you can create the visual representation by building the boxes.

7. Select the three Excel cells containing the calculated values of the box heights.
8. Open the Insert tab on the Excel ribbon.
9. Click on the Recommended Charts button of the Charts group.
10. Open the All Charts tab in the pop-up window.
11. Select Column from the list on the left side of the pop-up window.
12. Select the Stacked Column option.
13. Select the stacked column preview image (the one to the right).
14. Press OK.

Creating the boxes from scratch in Excel. Image by Author

Formatting the boxes

To ensure your plot is clear and visually appealing, you need to format the boxes.

15. Select the underlying box on the plot area (the closest box to the x-axis).
16. Right-click and select Format Data Series....
17. Click on the Fill & Line button on the Format Data Series pane that appears on the right side of the screen.
18. Click on the Fill drop-down arrow.
19. Select the No fill option.
20. Select the lower box from the two boxes currently visible in the plot area.
21. Select a color you want for the Color option in the Fill drop-down section on the Format Data Series pane (e.g., blue).
22. Click on the Border drop-down arrow.
23. For the Color option, select a darker shade of the same color you selected in step 21 (e.g., dark blue).
24. Select the upper box from the two boxes currently visible in the plot area.
25. For the Color option in the Fill drop-down section on the Format Data Series pane, select the color you chose earlier.
26. For the Color option in the Border drop-down section on the Format Data Series pane, select the same color once again.

Formatting the underlying box from scratch in Excel. Image by Author

Formatting the upper box from scratch in Excel. Image by Author

Creating the whiskers

Next, you need to add the whiskers to your plot, which represent the range of the data.

27. Select the underlying box, which is now invisible.
28. Click on the Chart Elements button that appears to the right of the chart area.
29. Hover over the Error Bars option until a drop-down arrow appears to its right.
30. Click on the drop-down arrow that appears.
31. Select More Options....
32. Click on the Error Bar Options button on the Format Error Bars pane that appears on the right side of the screen.
33. Select the Minus option for Direction in the Vertical Error Bar drop-down section.
34. Select the Custom option for Error Amount.
35. Click on Specify Value in that field.
36. In the pop-up window, remove the value in the Negative Value Error field.
37. Select the Excel cell that contains the calculated value for the lower whisker length.
38. Press OK.
39. Select the upper box on the plot area.
40. Click on the Chart Elements button that appears to the right of the chart area.
41. Hover over the Error Bars option until a drop-down arrow appears to its right.
42. Click on the drop-down arrow that appears.
43. Select More Options....
44. Select the Plus option for Direction in the Vertical Error Bar drop-down section on the Format Error Bars pane on the right side of the screen.
45. Select the Custom option for Error Amount.
46. Click on Specify Value in that field.
47. In the pop-up window, remove the value in the Positive Value Error field.
48. Select the Excel cell that contains the calculated value for the upper whisker length.
49. Press OK.

Opening more options for error bars. Image by Author

Setting the error bar options. Image by Author

Creating the lower whisker from scratch. Image by Author

Adding the mean point

To complete the plot, you need to add the mean point, which gives an additional reference for the central tendency of the data.

50. Copy the calculated value for the mean in the statistical table you created earlier.
51. Select the chart area.
52. Press Ctrl+V on your keyboard.
53. Select any of the boxes in the plot area.
54. Right-click and select Change Series Chart Type....
55. In the pop-up window, find the last data series in the Series Name column.
56. Select the Scatter chart type from the drop-down list for this data series.
57. Press OK.

Changing the series chart type pop-up window. Image by Author

Changing the series chart type to scatter. Image by Author

Removing the redundant elements

Finally, to ensure the plot is clean and focused, you need to remove any unnecessary elements.

58. Select and delete the legend from the chart area.
59. Select and delete the horizontal axis of the plot (you'll see the only value 1 on it).

A simple box and whisker plot from scratch in Excel. Image by Author

(Optional) Formatting the mean point

For additional clarity and customization, you can format the mean point on the plot.

60. Select the mean point marker on the plot.
61. Click on the Fill & Line button on the Format Data Series pane on the right side of the screen.
62. Open the Marker tab.
63. Open the Marker Options drop-down section.
64. Select Built-in.
65. Select the cross symbol for the Type field (the 4th option in the drop-down list for that field).
66. Select No fill in the Fill drop-down section.
67. For the Color option in the Border drop-down section, select the same color you selected in step 23.

Formatting the mean point from scratch in Excel. Image by Author

Creating a basic box and whisker plot in Excel: the easy way

As we can see, the long way of creating a basic box and whisker plot in Excel looks rather daunting and time-consuming. Luckily, we can obtain the same result in just eight easy steps by using a dedicated Excel feature.

1. Select the Excel cells containing the values for which you want to create a box and whisker plot.
2. Open the Insert tab on the Excel ribbon.
3. Click on the Recommended Charts button of the Charts group.
4. Open the All Charts tab in the pop-up window.
5. Select Box & Whisker from the list on the left side of the pop-up window.
6. Press OK.
7. Select the horizontal axis of the plot (you'll see the only value 1 on it).
8. Press the delete key on your keyboard.

Creating a box and whisker plot using a dedicated Excel feature. Image by Author

To make sure your box and whisker plot in Excel yields valuable insights, you need to ensure first that your data is meaningful and properly cleaned and transformed. The Data Preparation in Excel course will teach you how to prepare Excel data through logical functions, nested formulas, lookup functions, and pivot tables.

Creating a horizontal box and whisker plot in Excel

To create a horizontal box and whisker plot, we follow the same instructions as for creating a vertical box and whisker plot from scratch, with some new steps, mostly related to adding the mean point. Below is the whole process step by step.

Calculating the statistical values and box and whisker lengths

1. On the same Excel worksheet, create an area for calculating and storing statistical values. Let’s call this area a statistical table. You can place it to the right or below your main table.
2. For clarity, consider delineating the statistical table (select the table—right-click—select Format Cells...—open the Border tab—click on Outline—press OK).
3. In the first column of the statistical table, enter the following, one per row:
• “Min“
• “Q1“
• “Median“
• “Q3“
• “Max“
• “Mean“
4. In the second column of the statistical table, calculate each statistical notation using the following Excel formulas. Here, your_data_range refers to the column of values for which you want to create a plot:
• =MIN(your_data_range)
• =QUARTILE.EXC(your_data_range, 1)
• =MEDIAN(your_data_range)
• =QUARTILE.EXC(your_data_range, 3)
• =MAX(your_data_range)
• =AVERAGE(your_data_range)
5. In the third column of the statistical table, enter the names of the values to be calculated, one per row:
• “Underlying box length“
• “Left box length“
• “Right box length“
• “Left whisker length“
• “Right whisker length“
6. In the fourth column of the statistical table, calculate each value in interest using the following formulas:
• =Q1
• =Median-Q1
• =Q3-Median
• =Q1-Min
• =Max-Q3   

Creating and formatting the boxes and whiskers

7. Select the three Excel cells containing the calculated values of the box lengths.
8. Open the Insert tab on the Excel ribbon.
9. Click on the Recommended Charts button of the Charts group.
10. Open the All Charts tab in the pop-up window.
11. Select Bar from the list on the left side of the pop-up window.
12. Select the Stacked Bar option.
13. Select the stacked bar preview image (the one to the right).
14. Press OK.
15. Select the underlying box on the plot area (the closest box to the y-axis).
16. Right-click and select Format Data Series....
17. Click on the Fill & Line button on the Format Data Series pane that appears on the right side of the screen.
18. Click on the Fill drop-down arrow.
19. Select the No fill option.
20. Select the left box from the two boxes currently visible in the plot area.
21. Select a color you want for the Color option in the Fill drop-down section on the Format Data Series pane (e.g., blue).
22. Click on the Border drop-down arrow.
23. For the Color option, select a darker shade of the same color you selected in step 21 (e.g., dark blue).
24. Select the right box from the two boxes currently visible in the plot area.
25. For the Color option in the Fill drop-down section on the Format Data Series pane, select the color you chose earlier.
26. For the Color option in the Border drop-down section on the Format Data Series pane, select the same color once again.
27. Select the underlying box, which is now invisible.
28. Click on the Chart Elements button that appears to the right of the chart area.
29. Hover over the Error Bars option until a drop-down arrow appears to its right.
30. Click on the drop-down arrow that appears.
31. Select More Options....
32. Click on the Error Bar Options button on the Format Error Bars pane that appears on the right side of the screen.
33. Select the Minus option for Direction in the Vertical Error Bar drop-down section.
34. Select the Custom option for Error Amount.
35. Click on Specify Value in that field.
36. In the pop-up window, remove the value in the Negative Value Error field.
37. Select the Excel cell that contains the calculated value for the left whisker length.
38. Press OK.
39. Select the right box on the plot area.
40. Click on the Chart Elements button that appears to the right of the chart area.
41. Hover over the Error Bars option until a drop-down arrow appears to its right.
42. Click on the drop-down arrow that appears.
43. Select More Options....
44. Select the Plus option for Direction in the Vertical Error Bar drop-down section on the Format Error Bars pane on the right side of the screen.
45. Select the Custom option for Error Amount.
46. Click on Specify Value in that field.
47. In the pop-up window, remove the value in the Positive Value Error field.
48. Select the Excel cell that contains the calculated value for the right whisker length.
49. Press OK.

Adding the mean point

50. Copy the calculated value for the mean in the statistical table you created earlier.
51. Select the chart area.
52. Press Ctrl+V on your keyboard.
53. Select any of the boxes in the plot area.
54. Right-click and select Change Series Chart Type....
55. In the pop-up window, find the last data series in the Series Name column.
56. Select the Scatter chart type from the drop-down list for this data series.
57. Press OK.
58. Select the new point that appears on the plot.
59. Right-click and select Select Data....
60. In the pop-up window, select the last series from the Legend Entries (Series) list.
61. Press the Edit button.
62. Click on the Series X values field.
63. Select the Excel cell that contains the calculated mean value.
64. Remove the content of the Series Y values field.
65. Type 1 in the Series Y values field.
66. Press OK.
67. Press OK again.
68. Select the secondary axis of the plot (the one to the right of the plot area).
69. Click on the Axis Options button of the Axis Options tab on the Format Axis pane that appears on the right side of the screen (it can be already opened).
70. Open the Axis Options drop-down section.
71. Play with the values for Bounds (try setting 0 for Minimum and 2 for Maximum) until the value 1 on the primary y-axis coincides with the value 1 on the secondary y-axis, meaning that the mean point is now located inside the box on the same horizontal line as the whiskers.

Selecting the data. Image by Author

Editing the series. Image by Author

Changing the coordinates of the mean. Image by Author

Customizing the secondary axis. Image by Author

Removing the redundant elements

72. Select and delete the legend from the chart area.
73. Select and delete the primary vertical axis of the plot (you'll see the only value 1 on it).
74. Select and delete the secondary vertical axis of the plot.

A horizontal box and whisker plot in Excel. Image by Author

(Optional) Formatting the mean point

75. Select the mean point marker on the plot.
76. Click on the Fill & Line button on the Format Data Series pane on the right side of the screen.
77. Open the Marker tab.
78. Open the Marker Options drop-down section.
79. Select Built-in.
80. Select the cross symbol for the Type field (the 4th option in the drop-down list for that field).
81. Select No fill in the Fill drop-down section.
82. For the Color option in the Border drop-down section, select the same color you selected in step 23.

To explore Excel's various data visualization options that can help you analyze and interpret your data, consult this tutorial: Visualizing Data in Excel.

Creating box and whisker plots for multiple categories in Excel

In all our approaches so far, we were building a box and whisker plot for the whole range of data (in our case—for the ages of cats and dogs from a hypothetical pet adoption center altogether). What if we need to create a separate box and whisker plot for each category but display all such plots on the same chart? Let's say that we want to visually compare the age statistics for cats and dogs.

1. In the Excel worksheet where you store the table with the values to be plotted, select both the column containing the categories and the one with the corresponding values. If these columns aren't adjacent, press Ctrl before selecting the second range of cells.
2. Open the Insert tab on the Excel ribbon.
3. Click on the Recommended Charts button of the Charts group.
4. Open the All Charts tab in the pop-up window.
5. Select Box & Whisker from the list on the left side of the pop-up window.
6. Press OK.

Creating a box and whisker plot for multiple categories. Image by Author

Customizing a Box and Whisker Plot in Excel

Now, let's briefly explore how we can customize a box and whisker plot in Excel. 

Apart from various general adjustments applicable to all types of Excel charts—such as adding or removing chart elements or filters, formatting data series, customizing text, or changing chart style (you can learn more in the tutorial on How to Create and Format a Combo Chart in Excel, in the chapter How to Format a Combo Chart in Excel)—we can consider some modifications related specifically to box and whisker plots. 

Note that the below instructions regard only box and whisker plots created by using the Box & Whisker Excel feature, discussed in the sections on Creating a basic box and whisker plot in Excel: the easy way and Creating box and whisker plots for multiple categories in Excel.

1. Select your box and whisker plot (or plots) on the plot area.
2. Click on the Series Options button on the Format Data Series pane on the right side of the screen.
3. In the Series Options drop-down section, play with the following settings until the resulting chart satisfies your needs:
• change the gap width
• display the inner points
• hide the outlier points
• hide the mean marker(-s)
• display the mean line (only for multiple category box and whisker plots)
• calculate the quartiles including the median(-s)

Customizing a box and whisker plot in Excel. Image by Author

Conclusion

To summarize, in this tutorial, we drilled down various approaches to creating a box and whisker plot in Excel, as well as some ways of its customization to make it more efficient for statistical data analysis. For further learning, check out these courses: Data Visualization in Excel and Data Analysis in Excel.