Excel Box and Whisker Plot

A box and whisker plot in Excel is an exploratory chart that shows statistical highlights and data set distribution. This chart is used to indicate a five-number summary of the data. These five-number summaries are “Minimum Value, First Quartile Value, Median Value, Third Quartile Value, and Maximum Value.” Using these statistics, we display the distribution of the dataset. Below is a detailed explanation of these statistics.

For example, suppose you have a data set and need to show data distribution into quartiles, highlighting the mean and outliers. In addition, we may use a box and whisker chart to compare and analyze statistically, e.g., medical results, test scores, etc.

  • Minimum Value: The minimum or smallest value from the dataset.First Quartile Value: The value between the minimum and median values.Median Value: Median is the middle value of the dataset.Third Quartile Value: The value between the median and maximum values.Maximum Value: The highest value of the dataset.

One of the problems with the box and whisker plot chart is that it is not familiar to use outside the statistical world may be due to a lack of awareness among its users in the Excel community. On the other hand, it could also be the reason for the lack of knowledge on interpretation of the chart.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Box and Whisker Plot in Excel (wallstreetmojo.com)

How to Create a Box and Whisker Plot in Excel?

For a better explanation of the box and whisker plot chart with Excel, we are taking the sample data of the examination of the previous 3 years. Below is some data.

We must first calculate the five statistics numbers from the above data or each year for each year. Five numbers of statistics are “Minimum Value, First Quartile Value, Median Value, Third Quartile Value, and Maximum Value.”

For this, create a table like the one below.

First, we must calculate the “Minimum Value” for each year.

Then, we need to calculate the “First Quartile Value.”

Then, we will calculate the “Median Value.”

Next, we should calculate the “Third Quartile” value.

Then, the final statistics are the “Maximum Value” from the loss.

Now, we have completed performing the five number statistics. However, we need to create one more similar table to find the differences. We need to retain only the minimum value as it is.

To find the difference for “First Quartile” is First Quartile – Minimum Value.”

To find the difference for “Median Value” is “Median Value – First Quartile.”

To find the difference for the “Third Quartile” is “Third Quartile – Median Value.”

To find the difference for “Maximum Value” is the “Maximum Value – Third Quartile.”

Our final table is ready to insert a chart for the data. Now select the data to Insert Stacked Column Chart in ExcelInsert Stacked Column Chart In ExcelA stacked column chart in Excel is a column chart where multiple series of the data representation of various categories are stacked over each other. The stacked series are vertical.read more.

Now, we will have a chart like the one below.

We must select the data under the “Design Ribbon” and select “Switch Row / Column.”

Our rows and column data in the chart are switched, so our modified chart may look as follows.

We will select the bottom-placed bar, i.e., the blue-colored bar, and make the fill “No Fill.”

Therefore, now, the bottom bar is not visible in the chart.

Change the horizontal axis labels to 2017, 2018, and 2019.

The box chart is ready. Next, we need to create a whisker for these boxes. Now, selecting the top bar of the chart will make no fill.

We will go to the “Design” tab and “Add Chart Elements” by selecting the same bar.

Under “Add Chart Elements,” click on “Error Bars > Standard Deviation.”

Now, we have whisker lines on top of the bars.

Now, select newly inserted Whisker lines and click “Ctrl + 1” to open the format data series option to the right of the chart.

Under Format Error Bars Format Error BarsThe error bars in Excel are the graphical representation that helps visualize the variability of data given on a two-dimensional framework. It helps indicate the estimated error or uncertainty to give a general sense of how accurate a measurement is.read more” we need to make the following changes.

Direction “Minus”

End Style “No Cap”.

Error Amount > Percentage > 100%.

Now, the whisker lines will look as shown below:

We will select the bottom-placed bar and fill in “No Fill.”

Then, we would follow the same steps as above to add the whisker line at the bottom of the box. So now our box and whisker Excel chart will look as follows.

This article is a guide to Box and Whisker Plot in Excel. We discuss how to create a box and whisker plot chart in Excel, examples, and an Excel template. You may learn more about Excel from the following articles: –

  • Box Plot in ExcelMake Scatter Plots in ExcelCreate Dot Plots in ExcelCreate 3D Scatter Plot in Excel