Microsoft Excel: How to create a box plot

Microsoft Excel can make it simple to arrange, present, or analyze data by using various charts. One of the most powerful charts is the box-and-whisker plot, also known as a Box plot. It helps you to see the distributions within a data set.

This article will show you how to create a box plot using Microsoft Excel 365. It also covers older Excel versions for people who have yet to update.

What are Box Plots?

The box plot allows you to see statistical properties like spread and variability. This plot depicts a single line (the box), which has been divided in half. Lines (the whiskers), extend to each side of the box.

These elements represent the summation of five numbers from a collection of numerical data. These elements look similar and can either be displayed vertically or horizontally:

Let’s look at some sample data to understand the summary of five numbers.

25, 26, 28, 30, 32, 34, 37, 38

1. The minimum. This is the minimum value of the data set. This represents the point at which the data set ends.

2. The first quartile. %20This%20is%20the%20value%20under%20which%2025%%20of%20data%20points%20are%20found.
3. The second quartile. This is called the mediant. It is also called the “middle price”.
4. The third quartile. This%20is%20the%20value%20above%20which%2075%%20of%20data%20points%20are%20found.
5. The maximum. This is the maximum value for each data set.

Excel 365: How to create a box plot

In Office 365, Microsoft Excel comes with box plots that can be used as chart templates. This makes it simple to make visual plots of your data. You can learn more about Excel by visiting the Excel Help Center, First, learn the basics .

You can create a box plot by:

1. Create a new spreadsheet and enter your data.
2. You can select the data you want by clicking and dragging.

3. Select the Ribbon Insert tab.

4. Click Insert Statistic Chart then Box and Whisker.

Note: In order to insert data into an empty chart you will need the following: Chart Design Clicking and tab Select Data.

Excel can now make a basic box and whisker charts. This Excel chart can be customized to present your statistical data in the exact way that you prefer.

Excel 365: Formatting a Box Plot

Excel gives you the ability to modify the design of the box chart by changing the data points or adding titles.

The Chart Design Tab allows you to insert chart elements, such as chart titles, gridlines and labels, change the chart type or layout, change the color and box appearances using the built-in charts style templates, and even change the colors of the whiskers and boxes.

The Format Tab allows you to adjust your color choices, add text and create effects for your chart elements.

Add additional elements to the display by clicking on the box and whisker map and choosing “Add Display Elements” Format Data SeriesSelect the drop-down menu.

There are many options:
Show outlier points. It displays abnormally high and low data points as outliers, which are shown in circles just outside of the plot.
Show mean markers. The chart’s mean value is displayed as a cross.
Show mean line. The line shows the average points from multiple data sets.
Quartile calculation. You can calculate the quartiles if you have odd numbers of data points by including or excluding their median. You should choose the exclusive interquartile range for larger data sets. The inclusive median method, however, is more precise.

In older Excel versions, how to create a box and whisker plot

As older Versions of Excel Excel 2013 (and Excel 2016) do not include templates for box and whisker charts. Creating one is more challenging.

To begin, use the following formulae to calculate your quartiles and then create a table:

1. Minimum value: MIN(cell range)
2. First quartile: QUARTILE.INC(cell range,1)
3. Median: QUARTILE.INC(cell range, 2)
4. Third quartile: QUARTILE.INC(cell range, 3)
5. Maximum value: MAX(cell range)

Note: Drag and choose your data sets to determine cell range.

Next, you will need to determine the quartile difference in a separate table. These relate to the box heights:

1. The Q1 value
2. The Q1 is the median
3. Q3 is less than the median
4. Maximum value, minus Q3
5. Q1 plus the minimum amount

The following values are then used to make a chart:

1. Click the Insert Select tab and then choose Insert Column or Bar Chart.
2. Click Stacked Column Chart. Chart Design Click tab, then click Switch Row/Column.

3. Click the right-clicked part of the graph which represents Box 1 – Hidden and click Fill then click No Fill.

For the top whisker, click her:

1. Click the top box Select the Chart Designtab.
2. Click Add Chart Element.

3. Click Error Bars >> More Error Bars Options.

4. Under Direction, Plus. Error Amount click Custom > Specify Value.

5. Replace the Positive Error Value Enter the calculation value Whisker Top.

You can also add the bottom whiske:

1. Click the hidden box.
2. Under the Chart Designtab, select Add Chart Element.
3. Click Error Bars >> More Error Bars Options.
4. Under Direction, Minus Both over and below Error Amountclick Custom > Specify Value.

5. Replacing the text in the dialog box is possible Negative Error Value Enter the calculation value Whisker Bottom.

Now you have the basic box-and-whisker plot of your data. This can be further customized by changing the colors and styles, as well as adding or removing a dot or mean line.

Statistics Analysis is now easier than ever

With the newer and more powerful version of Excel, it is much easier to visualize and analyze data. You should now have an understanding of what a box plot and whisker plot are and how to create one in Excel.