A great way of representing statistical data in **Excel** is by using a **box plot**. If the data in a data set are interrelated to each other than showing them in a box plot is a wonderful idea. It helps to visualize the distribution of data. A modified box plot is slightly different from a normal box plot. In this article, we will show you how to make a modified box plot in **Excel**.

## Download Practice Workbook

You can download the free **Excel** workbook here and practice on your own.

## Modified Box Plot

The main difference between a modified box plot and a standard box plot lies in terms of showing the outliers. In a standard box plot, the outliers are included in the main data and cannot be differentiated from the plot. But, in a modified box plot, users can differentiate outliers from the main data by seeing the plot, as the plot displays outliers as points far from the whiskers of the plot.

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

In this article, you will see the step-by-step procedures to make a modified box plot in **Excel**. Also, after making the box plot, we will analyze the plot in terms of different values found from our data set.

### Step 1: Preparing Data Set

To make the modified box plot, we will need a data set first. To do that,

- First of all, prepare the following data set.
- Here, we have some random names and their obtained marks in an exam.

### Step 2: Inserting Box and Whisker Plot Command

After preparing our data set, we now have to insert some commands. For that,

- Firstly, we will select the data range from cell
.*C4:C15*

- Secondly, go to the
group from the*Charts*tab of the ribbon.*Insert* - Then, click on the icon named
.*Insert Statistic Chart* - Lastly, select
from the dropdown.*Box and Whisker*

### Step 3: Showing Modified Box Plot

Now we are in the final step of our procedure. To show the result, do the followings.

- After inserting the command from the previous step, you will see the following plot.

- Finally, name the plot as
and you will get to see the distribution of all the data throughout the plot.**Modified Box Plot**

**Read More:** **How to Remove Last Modified By in Excel (3 Ways)**

## Analyzing Modified Box Plot in Excel

From our previous discussion, you can see how to make a modified box plot in Excel. A box plot is mainly a synopsis of five numbers, which are- the minimum value, the first quartile, the median value, the third quartile, and the maximum value. Also, the modified box plot shows the mean value and the lower and upper limits of a data set. It also displays the outliers separately. Now, in our following discussion, you will see how to find those values and how to show them in the plot.

### 1. Finding Out Minimum Value

From our data set, we will find the minimum value. To do that, follow the following steps.

**Step 1:**

- Firstly, use the following formula of
in cell*the MIN function*.*F4*

`=MIN(C5:C15)`

**Step 2:**

- Secondly, press
and get the minimum value which is*Enter*.*33*

**Step 3:**

- Finally, show the value in the box plot.

**Read More:** **How to Move Data from Row to Column in Excel (4 Easy Ways)**

### 2. Calculating First Quartile

The first quartile in a data set represents the value that is between the minimum and the median value. To calculate the first quartile, see the following steps.

**Step 1:**

- Firstly, type the following formula of
in cell*the QUARTILE.EXC function*.*F5*

`=QUARTILE.EXC(C5:C15,1)`

**Step 2:**

- Secondly, press
to see the value which is*Enter*.**59**

**Step 3:**

- Finally, show the first quartile in the modified box plot.

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

### 3. Determining Median Value

To determine the median value, do the followings.

**Step 1:**

- First of all, use the following formula of
in cell*the MEDIAN function*.*F6*

`=MEDIAN(C5:C15)`

**Step 2:**

- Secondly, hit the
button to see the result.*Enter*

**Step 3:**

- Finally, mark the value in the plot that is
.*64*

### 4. Measuring Third Quartile

The third quartile can be described as the value that lies between the median and the maximum value of a data set. We will use the following steps to measure it.

**Step 1:**

- Firstly, in cell
, type the following formula of*F7*to measure the third quartile.*the QUARTILE.EXC function*

`=QUARTILE.EXC(C5:C15,3)`

**Step 2:**

- Secondly, to see the result, press
.*Enter*

**Step 3:**

- Finally, present the value in the box plot.

**Read More:** **If a Value Lies Between Two Numbers Then Return Expected Output in Excel**

**Similar Readings**

**How to Fix Formula in Excel (9 Easy Methods)****[Fixed!] Excel Links Not Working Unless Source Workbook Is Open****Make Sankey Diagram in Excel (with Detailed Steps)****How to Move Up and Down in Excel (5 Easy Methods)**

### 5. Finding Out Maximum Value

In this discussion, we will find out the maximum value. For that, do as followings.

**Step 1:**

- First of all, to find out the maximum value, write the following formula of
**the**.*MAX function*

`=MAX(C5:C15)`

**Step 2:**

- In the second step, press
to see the result.*Enter*

**Step 3:**

- Finally, show the result in the plot which is
.*98*

### 6. Calculating Mean Value

In the following section, we will calculate the mean value of the data set. For that, do as follows.

**Step 1:**

- In the beginning, apply the following formula of
in cell*the AVERAGE function*.*F9*

`=AVERAGE(C5:C15)`

**Step 2:**

- Secondly, to see the result hit
.*Enter*

**Step 3:**

- Thirdly, point out the mean value in the plot, which is shown as the letter
in plot.*X*

**Read More:** **How to Calculate Root Mean Square Error in Excel**

### 7. Determining Interquartile Range

The interquartile range (**IQR**) is the difference between the third quartile and the first quartile of a data set. To determine this from our data set, do the following.

**Step 1:**

- Firstly, in cell
write the following formula.*F10,*

`=F7-F5`

**Step 2:**

- In the second step, press the
button to see the result.*Enter*

**Step 3:**

- Thirdly, we will multiply the
by**IQR**to find the upper and lower limits of this data set.*1.5* - So, use the following formula in cell
.*F10*

`=F10*1.5 `

**Step 4:**

- Finally, to see the result hit
.*Enter*

### 8. Measuring Lower Limit and Upper Limit

Now, we will measure the lower limit and the upper limit of our data set. The procedure is as follows.

**Step 1:**

- Firstly, type the following formula in cell
to measure the lower limit.*F12*

`=F5-F11`

**Step 2:**

- Secondly, press
to see the lower limit which is*Enter*.*38*

**Step 3:**

- Thirdly, type the following formula in cell
to measure the upper limit.*F14*

`=F7+F11`

**Step 4:**

- Fourthly, hit the
button to see the result.*Enter*

**Step 5:**

- Finally, point out the lower limit and the upper limit in the plot.

**Read More:** **How to Set Intervals on Excel Charts (2 Suitable Examples)**

### 9. Showing Outliers in Modified Box Plot

This is the last point in our analysis. We will show the outliers in this content. The detailed procedures are as follows.

- In the previous step, you will see the lower limit and the upper limit of the data set.
- Any value that is lower than the lower limit or higher than the upper limit is considered as an outlier.
- From the above discussion, you can see two values in the data set which are out of range of these limits.
- These values are
and*98*.*33* - Finally, mark these values in the plot to present the outliers.

**Read More: How to Make a Dot Plot in Excel (3 Easy Ways)**

## Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to make a modified box plot in **Excel** by following the above-described method. Please share any further queries or recommendations with us in the comments section below. The **ExcelDemy **team is always concerned about your preferences.

## Related Articles

**How to Do Descriptive Statistics in Excel (2 Suitable Methods)****Create an Organizational Chart in Excel (2 Suitable Ways)****How to Create Butterfly Chart in Excel (2 Easy Methods)****Show Menu Bar in Excel (2 Common Cases)****How to Make Flashcards in Excel (2 Suitable Ways)****Calculate WACC in Excel (with Easy Steps)****How to Add Trailing Zeros in Excel (2 Easy Ways)**