How to Make a Box Plot in Excel (With Easy Steps)

This tutorial will demonstrate how to make a box plot in excel. Box plot is another way of presenting statistical data for better visualization. It helps to compare different values and understand how they are distributed throughout the dataset. Setting a box plot is helpful for various purposes such as summarizing different variations of a large dataset, comparing multiple distributions, showing outliners, indicating symmetry and skewness to a degree, etc. So, learning how to make a box plot in excel is essential.


Download Practice Workbook

You can download the practice workbook from here.


What Is Box Plot?

A box plot is a way of representing different numbers graphically. It can be drawn vertically or horizontally. It mainly works with 5 sets of numbers: Minimum, First Quartile, Median (also known as the second Quartile), Third Quartile, and Maximum. The values in a vertical box are explained below.

  • Minimum: It indicates the smallest value in the dataset.
  • First Quartile: It is displayed as the 25th percentile of the dataset and It stays between a minimum value and the median of the dataset.
  • Median (also known as the second Quartile): It is the 50th percentile of the dataset and stays in the middle.
  • Third Quartile: It marks the 75th percentile of the dataset and stays in the middle of the median and maximum values of the dataset.
  • Maximum: It is the maximum number of the dataset.

Step-by-Step Procedures to Make a Box Plot in Excel

If you follow the steps correctly, you should learn how to make a box plot in excel on your own. The steps are described below:

1. Arranging Dataset

Our goal is to make a box plot. To do so, we have to arrange a dataset first. In this dataset, we have Total Marks in Column B, Statistics in Column D, and Differences in Column G. By the side of the Statistics and Differences columns we have the Value columns (Column E and Column H)  where the values will be input.

Procedures to Make a Box Plot


2. Creating Box Plot

Next, we want to create a box using the 5 sets of numbers. The step is described below.

  • At first, go to any blank ( in this case, Cell E5) and insert the following formula.
=MIN(B5:B13)

Procedures to Make a Box Plot

  • Second, press the Enter button to get the result.

Procedures to Make a Box Plot

  • Third, go to any blank (in this case, Cell E6) and insert the following formula.
=QUARTILE.INC(B5:B13,1)

Procedures to Make a Box Plot

  • Forth, press the Enter button to get the result.

Procedures to Make a Box Plot

  • Fifth, go to any blank (in this case, Cell E7) and insert the following formula.
=QUARTILE.INC(B5:B13,2)

Procedures to Make a Box Plot

  • Sixth, press the Enter button to get the result.

go to any blank (in this case, Cell E8) and insert the following formula.

  • Seventh, go to any blank (in this case, Cell E8) and insert the following formula.
=QUARTILE.INC(B5:B13,3)

go to any blank (in this case, Cell E8) and insert the following formula.

  • Eighth, press the Enter button to get the result.

go to any blank (in this case, Cell E8) and insert the following formula.

  • Ninth, go to any blank (in this case, Cell E9) and insert the following formula.
=MAX(B5:B13)

Procedures to Make a Box Plot

  • Last, press the Enter button to get the result.

Procedures to Make a Box Plot

Read More: How to Make a Forest Plot in Excel (2 Suitable Examples)


3. Calculating Differences in Values

In this next step, we aim to calculate the differences in values. To fulfill this goal we will follow the below steps.

  • To begin with, we will determine the difference between Q1 and Min So, we put the formula in the H6 cell.
=E6-E5

Procedures to Make a Box Plot

  • In addition, use the cells to determine the differences according.
  • At the end of this step, you will get the below result.

Procedures to Make a Box Plot


4. Inserting Column Chart

The next step is to insert a column chart in excel. The step is described below.

  • Firstly, select the whole dataset you want to represent, and from the Insert tab select the 2-D Column chart option.

Procedures to Make a Box Plot

  • Secondly, you will get a chart like the below image.

  • Thirdly, right-click on the Select Data option.

  • Fourthly, in the Select Data Source dialog box, select the Switch Row/Column option and press OK.

Procedures to Make a Box Plot

  • Lastly, you will get the result like the below image.


5. Editing and Formatting Column Chart

After that, we want to edit and format the column chart for better visualization. The process of this step is.

  • To begin with, right-click on the bottom section of the chart and select the Format Data Series option.

  • In addition, in the Format Data Series window, select No fill in the Fill section and No line in the Border section.

  • Finally, you will get the result like the below image.


6. Creating Whiskers for Box Plot

Next, our aim is to create the Whiskers for the boxplot by the following steps.

  • Firstly, select the chart and go to Chart Elements>Error Bars>More Options.

  • Secondly, in the Format Error Bars option window, select Minus in the Vertical Error Bar, Cap in the End Style section, and 100% in the Error Amount option.

  • or you can do the same thing by going to Chart Design>Add Chart Element>Error Bars>More Error Bars Options.

  • Next, again in the Format Error Bars option window, select Minus in the Vertical Error Bar, Cap in the End Style section, and 100% in the Error Amount option.

  • Lastly, you will get the below result.

Read More: How to Show Menu Bar in Excel (2 Common Cases)


7. Presenting Final Result

You can hide the upper and lower portion by using the same previous steps. The final result of this process will be like the below image.


Things to Remember

  • The most important part is to determine the differences in the values. So, we have to be careful about that step.
  • In case inserting the chart, you can’t choose any chart option you have to choose the Stacked Column chart. Otherwise, the process won’t work.
  • In the case of editing and formatting, you have your freedom. In this case, we have shown it our way, you can hide or color more ways in this section accordingly.

Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to make a box plot in excel. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Zehad Rian Jim
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo