How to Make Box Plot in MS Excel

Box Plot – Introduction

The Box plot is also known as simply box or box chart and it is used to graphically represent the key values from summary statistics.

The Box plot is a new feature and available only in MS Excel 2016 therefore it’s not available in the Microsoft’s previous office versions like Office 2010 and Office 2013.

Note: We can also create boxplot effect chart in MS Excel 2010 and 2013 versions by using of some other methods but these versions doesn’t have exact boxplot features (Boxplot & Whisker) therefore in this tutorial we are going work with MS Excel 2016 version.

Box Plot – Example

This example will teach you, how to create a box and whisker plot in MS Excel 2016.

Select the data that you want to add in boxplot chart.

Click on the “Insert” menu.
Click on the “Recommended Charts” tool.

Click on the “All Charts” tab button.
Select the “Boxplot & Whisker” option from the list.

Now click on the “Ok” button.

Example Explanation: In the above image, the middle line of the box represents the median or middle value ($500).

The X sign in the box represents the mean value ($500 in this example). The median divides the data set into a bottom half ($200, $280, $300, $420, $450) and a top half ($550, $600, $650, $715, $750, $850).

The bottom line of the box represents the median of the bottom half of 1^st quartile ($300). The top line of the box represents the median of the top half or 3^rd quartile ($700). The Whiskers (Vertical lines) extends from the ends of the box to the minimum value ($200) and maximum value ($850).

How do you make a box and whisker plot on Excel?

Create a box and whisker chart

On the Insert tab, in the Illustrations group, click Chart.
In the Insert Chart dialog box, on the All Charts tab, click Box & Whisker.

How do you create a box plot for a data set?

To create a box–and-whisker plot, we start by ordering our data (that is, putting the values) in numerical order, if they aren’t ordered already. Then we find the median of our data. The median divides the data into two halves. To divide the data into quarters, we then find the medians of these two halves.

How do you make a Boxplot in Excel 2010?

Where is box plot in Excel?

A box and whisker plot shows the minimum value, first quartile, median, third quartile and maximum value of a data set.

Simple Box and Whisker Plot

For example, select the range A1:A7.
On the Insert tab, in the Charts group, click the Statistic Chart symbol.
Click Box and Whisker.

How do you make a Boxplot?

To construct a box plot, use a horizontal or vertical number line and a rectangular box. The smallest and largest data values label the endpoints of the axis. The first quartile marks one end of the box and the third quartile marks the other end of the box.

Why box plot is used?

A box and whisker plot (sometimes called a boxplot) is a graph that presents information from a five-number summary. It is often used in explanatory data analysis. This type of graph is used to show the shape of the distribution, its central value, and its variability.

How do you compare box plots?

Guidelines for comparing boxplots

Compare the respective medians, to compare location.
Compare the interquartile ranges (that is, the box lengths), to compare dispersion.
Look at the overall spread as shown by the adjacent values.
Look for signs of skewness.
Look for potential outliers.

How do box plots work?

A box and whisker plot—also called a box plot—displays the five-number summary of a set of data. In a box plot, we draw a box from the first quartile to the third quartile. A vertical line goes through the box at the median. The whiskers go from each quartile to the minimum or maximum.

How do you do a box plot in math?

Does a box and whisker plot show the mean?

A boxplot, also called a box and whisker plot, is a way to show the spread and centers of a data set. Measures of spread include the interquartile range and the mean of the data set. Measures of center include the mean or average and median (the middle of a data set).

What is the range of a box plot?

Range. If you are interested in the spread of all the data, it is represented on a boxplot by the horizontal distance between the smallest value and the largest value, including any outliers. Interquartile range (IQR). The middle half of a data set falls within the interquartile range.

How do you analyze box plots?

Box plots are useful as they show outliers within a data set.

Step 1: Compare the medians of box plots. Compare the respective medians of each box plot.
Step 2: Compare the interquartile ranges and whiskers of box plots.
Step 3: Look for potential outliers (see above image)
Step 4: Look for signs of skewness.

How do I calculate 1qr?

We can find the interquartile range or IQR in four simple steps:

Order the data from least to greatest.
Find the median.
Calculate the median of both the lower and upper half of the data.
The IQR is the difference between the upper and lower medians.

How do you find Q1 Q2 and Q3?

In this case all the quartiles are between numbers:

Quartile 1 (Q1) = (4+4)/2 = 4.
Quartile 2 (Q2) = (10+11)/2 = 10.5.
Quartile 3 (Q3) = (14+16)/2 = 15.

How do you calculate Q1 Q2 Q3 in Excel?

To calculate Q3 in Excel, simply find an empty cell and enter the formula ‘=QUARTILE(array, 3)’. Again, replacing the ‘array’ part with the cells that contain the data of interest. 3. Finally, to calculate the IQR, simply subtract the Q1 value away from the Q3 value.

What is the formula for Q1 and Q3?

Q1 is the middle value in the first half of the data set. Since there are an even number of data points in the first half of the data set, the middle value is the average of the two middle values; that is, Q1 = (3 + 4)/2 or Q1 = 3.5. Q3 is the middle value in the second half of the data set.

What is Q1 Q2 Q3 Q4?

The standard calendar quarters that make up the year are as follows: January, February, and March (Q1) April, May, and June (Q2) July, August, and September (Q3) October, November, and December (Q4)