**What Is Box Plot In Excel?**

A

Box Plot in Excelis a graphical representation of statistical data. It compares multiple datasets using quartiles and indicates how the values or numeric data get distributed. We can create aBox Chart in Excel,vertically or horizontally.

A **Box Plot in Excel **represents the five-number summary technique of the dataset, indicating the **Median** and the degree of dispersion of the data from the center.

For example, the following table shows the number of Smartphone units sold from January to March.

When we create a** Box Plot in Excel **for the above table, using the **Box and Whisker** **Plot** **in Excel**, we get the following output.

- The top-most and bottom-most points in each
**Box Chart in Excel**show the maximum, and the minimum number of smartphone units sold each month, respectively. - The remaining data points denote the units sold between the highest and lowest points.
- The entire plot highlights the data set distribution based on the five-number summary of the dataset.

##### Table of contents

###### Key Takeaways

- A
**Box Plot in Excel**helps us visualize large dataset’s distribution using the five-number summary technique. It enables users to quickly determine the**Mean**, the data dispersion levels, and the distribution skewness and symmetry. - We can create a
**Box Chart in Excel**using the Stacked column [**Horizontal Box Plot in Excel**] or Bar chart [**Vertical Box Plot in Excel]**. - We can also create a
**Vertical Box Plot in Excel**as follows, select the “**Insert”**tab > go to the “**Charts**” group > click on the “**Insert Statistic Chart**” drop-down > select the “**Box and Whisker”**option.

**Understanding Box Plot (also known as Box and Whisker Plot)**

In the **Box Plot in Excel,** we see stacked boxes, each indicating a quartile. And the lines drawn at the end of the box look like whiskers. Hence, the name **Box and Whisker Plot in Excel. **We can create a **Vertical** or **Horizontal Box Plot in Excel.**

The Plot Elements of the **Box Plot in Excel**, shown in the below image, are as follows:

**First Quartile**

The **First Quartile**, or **Q1**, is the lower quartile since it gets evaluated at the 25^{th} percentile point, the lower quartile limit. Mathematically, it is the product of one-fourth of the value and one.

The syntax of the **First Quartile** is:

The arguments of the **First Quartile** are:

– The cell range for which we must find the*array***First Quartile**.– It is*quart***1**because it is the**First Quartile**.**Median**or**Second Quartile –****Median**is the value at the center of a dataset when the values are in ascending or descending order.

The syntax of the **Median()** is:

The arguments of the **Median()** are:

– The cell range of the dataset.*number1, [number2], …*

The **Median** is the second quartile because it represents the 50th percentile, and the Median [the midpoint of the dataset] also indicates the same. Hence, we must determine the Median as the function argument in a cell range of the dataset.

**Third Quartile****–**

The **Third Quartile**, or **Q3**, is the upper quartile, which calculates the 75^{th} percentile point, the upper quartile limit. Mathematically, it is the product of one-fourth of the value and three.

The syntax of the **Third Quartile** is:

The arguments of the **Third Quartile** are:

– The cell range for which we must find the*array***Third Quartile**.– It is*quart***3**because it is the**Third Quartile**.**Interquartile Range –**

It is the difference between the **First Quartile** and the **Third Quartile **and is a superior measure of the data distribution.

**Outliers**

Outliers are the points lying outside the whiskers.

**Minimum**

It is the least non-outlier point in the dataset. Therefore, in a** Box Chart in Excel**, it is identified as it is the lowest point of the lower whisker.

**Maximum**

It is the highest non-outlier point in the dataset. In a** Box Chart in Excel**, it is the highest point of the upper whisker.

**How To Create Box Plot In Excel?**

We will create a **Horizontal Box Plot in Excel **for the company’s data.

In the below table, the data is as follows:

- Column A contains Months.
- Column B shows 2019-Sales.

The steps to create a **Horizontal Box Plot in Excel **are,

** 1: **In the dataset, determine the

**Minimum, Q1, Median, Q3, and Maximum.**Enter the values in cells

**B17:B21**and apply their respective formulas in cells

**C17:C21**, as shown below.

** 2: **Calculate the differences between the five parameters in cells

**B17:B21.**The differences are in cells

**B26:B29**, with the minimum value in cell

**B25**being the same as in cell

**B17**. And the formulas to apply are in cells

**C25:C29**.

** 3: **Next, to create a

**Horizontal Box Plot in Excel,**select the cell range

**B24:B29**> select the “

**Insert”**tab > go to the “

**Charts**” group > click on the “

**Insert Column or Bar Chart**” drop-down > select the “

**2-D Bar”**option, as shown below.

The stacked bar chart will appear as depicted below.

** 4: **The above plot automatically takes the cell range

**B25:B29**. To create an accurate

**Box Plot in Excel**, right-click on the chart and click on the “

**Select Data”**option.

** 5: **The “

**Select Data Source”**window pops up > click on the “

**Switch Row/Column”**option > click on the “

**OK”**button.

Once we reverse the axes to stack the boxes horizontally, we see the following chart.

**6: **Now, choose the first segment of the chart from the left and right-click to select the “**Format Data Series”** option.

** 7: **The “

**Format Data Series”**pane opens on the right > click on the “

**Fill**” option > select the “

**No fill”**option > next, click on the “

**Border**” option > select the “

**No line”**option.

The first segment in the chart gets hidden. Now, we shall create the whiskers.

** 8: **Choose the first segment from the right in the chart and right-click to select the “

**Format Data Series” option >**The “

**Format Data Series”**pane opens on the right > click on the “

**Fill**” option > select the “

**No fill”**option.

** 9: **Keep the first segment from the right, which is selected, and click on the ‘

**+**’ icon. The “

**Chart**

**Elements”**window pops up > “

**check/tick**” the “

**Error Bars”**checkbox > click on the arrow next to it > click on the “

**More Options…”**, as shown below.

** 10: **The “

**Format Error Bars”**pane will appear on the right side > set the “

**Horizontal Error Bar”**as follows, “

**Direction**” as

**Minus**, “

**End Style”**as

**Cap**, and the “

**Error Amount”**as

**Percentage**with its value as

**100%**in the respective field.

After we set the selections, the chart will appear with the top whisker.

** 11: **Select the left-most segment in orange, then select the “

**Design”**tab > go to the “

**Chart Layout**” group > click on the “

**Add Chart Element”**drop-down > click on the “

**Error Bars”**arrow option > select the “

**More Error Bars Options…”**option, as shown below.

** 12: **Choose the same settings as done in step 10. [The “

**Format Error Bars”**pane will appear on the right side > set the “

**Horizontal Error Bar”**as follows, “

**Direction**” as

**Minus**, “

**End Style”**as

**Cap**, and the “

**Error Amount”**as

**Percentage**with its value as

**100%**in the respective field].

** 13: **Hide the currently chosen segment as explained in step 7. [The “

**Format Data Series”**pane opens on the right > click on the “

**Fill**” option > select the “

**No fill”**option > next, click on the “

**Border**” option > select the “

**No line”**option]. The box plot will appear as shown below.

** 14: **Next, choose each segment, one at a time, and right-click to select the “

**Format Data Series”**option. In the “

**Fill & Line”**section of the “

**Format Data Series”**pane, select “

**Solid fill”**and set the required colors. Also, choose “

**Solid line”**as the

**Border**.

** 15: **Click on the chart area and set the “

**Chart Title”**and the horizontal axis title using the “

**Chart Elements”**option. Also, select the vertical axis to delete the term “

**Values”**.

Once we apply all the settings, the box plot will appear as shown below:

**Interpretation**

In the above example, the interpretation of the **Box Chart in Excel **are as follows:

- The top-most data point in the upper whisker is between
**60000**and**70000**. Thus, it equals the**Maximum**value of the dataset,**65350**. - The bottom-most data point in the lower whisker is close to
**30000**. And it equals the**Minimum**value of the dataset,**27540**. - The bottom-most horizontal line, forming the first box in the plot, shows the
**First Quartile**, with the value**38135**. - The second horizontal line in the plot shows the
**Median**, with the value being**42650**. And, since it does not run through the middle of the box, it indicates a skewed data distribution. - The top-most horizontal line, forming the second box in the plot, shows the
**Third Quartile**, with the value being**54222**.**5**. - Since there are no
**Outliers**, the box plot does not highlight such data points. - The tiny ‘
**x**‘ in the middle of the box represents the dataset’s**Mean**. If you apply the**AVERAGE()**to the dataset, you will get the value**45044.166**.

Thus, the plotted **Box Chart in Excel** is accurate and on par with the five-number summary technique.

In conclusion, we read a** Box Chart in Excel **as follows:

- 25% of the monthly sales are below
**$38135,**and 75% are below**$54222.5**. - 50% of the monthly sales are between
**$38135**and**$54222.5**. - The
**Median**appears to be closer to the**First Quartile**. It indicates a positively-skewed distribution, showing that the data contains a higher frequency of higher sales values.

[**Special Note**: Alternatively, if we want to create a **Vertical Box Plot in Excel**, or the default Box Plot, for the same 2019 sales data. Here are the steps:

__Step 1__**: **Select the cell range **B1:B13** > select the “**Insert” **tab > go to the “**Charts**” group > click on the “**Insert Statistic Chart**” drop-down > select the “**Box and Whisker”** option, as shown below.

We get the following **Vertical Box Plot in Excel.**

__Step 2__**: **Update the **Chart Title**, and using the **Chart Elements**, insert the vertical axis title. Also, delete the term **1** in the horizontal axis and change the segment colors.

The final **Vertical Box Plot in Excel **is shown in the above image].

**Important Things to Note**

- We must calculate the five plot elements first, i.e., the Minimum and Maximum values, the
**First Quartile**and**Third Quartile**, and the**Median**of the dataset. - The
**Median**is also the**Second****Quartile**which denotes the data’s 50^{th} - We cannot consider the
**Outliers**while determining a dataset’s**Maximum**and**Minimum**

**Frequently Asked Questions**

**Where is Box and Whisker Plot in Excel?**

The **Box** **and** **Whisker Plot in Excel **is in the “**Chart**” group of the “**Insert”** tab.

**Can you make a Horizontal Box Plot in Excel?**

We can create a **Horizontal** **Box Plot in Excel **using the following steps:

1. First, calculate the **Minimum** and **Maximum** values, the **First **and **Third Quartile**, and the **Median** of the dataset.

2. Next, calculate the differences between the above five-number summary statistical values, and plot a 2-D stacked bar chart.

3. Then, change its **Shift Row/Column** data settings to reverse the axes and get a horizontally stacked bar plot.

4. Adjust the plot segments and error bars to include whiskers, and create a **Horizontal Box Plot in Excel**.

**What does a box and whisker plot show in Excel?**

A **Box and Whisker Plot in Excel** shows the data distributed into quartiles, highlighting the **Median** and **Outliers**. Additionally, the boxes have lines extended from their borders, known as whiskers. They represent the data variableness outside the quartiles.

**Download Template**

This article must help understand the **Box Plot in Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Box Plot in Excel. Here we create box and whiskers plots vertically & horizontally with examples & downloadable template. You can learn more from the following articles –

## Leave a Reply