How To Make a Box Plot in Excel in 2 Simple Methods

By Indeed Editorial Team

Published April 8, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

It can be challenging to quickly interpret large amounts of numerical data. Some people rely on Excel to turn their data into a box plot. Understanding how to create this type of diagram can help you visually analyze how much of your data appears in different quartiles. In this article, we explain how to make a box plot in Excel via two simple methods.

What is a box plot?

A box plot, or a box and whisker plot, is a diagram that visually summarizes the distribution of numerical values in a data set. The rectangular component of a box plot represents all the numbers in the second quartile. This component also has a line going through the middle to indicate the data set's median value.

Lines extending from the top and bottom of the rectangle represent all the numbers in the first and third quartiles. The end of each of these lines has a "whisker" that indicates the maximum and minimum values, respectively. Box plots may also have dots beyond the whiskers to indicate outliers.

Related: How To Use Conditional Formatting in Excel in 5 Steps

Why use a box plot?

Box plots can help you visualize the distribution of values, identify outliers and compare different categorical values. These diagrams are practical in various statistical applications, but consider the example where a principal creates a box plot. They might input test score data from three different teachers. If they notice that a large amount of Teacher A's test scores appear in the first quartile range, they might help the teacher with their lesson plans to improve future test scores. The box plot could also reveal high outlier scores and encourage the teachers to present students with more challenging work.

Related: 7 Types of Statistical Analysis Techniques (With the Statistical Analysis Process)

How to make a box plot in Excel

Here's how to make a box plot in Excel:

1. Enter data into the spreadsheet

Open a spreadsheet and enter your data. Many people copy and paste values from another document, though you can also type each value in separate adjacent cells. When creating box plots, it doesn't matter whether you organize the data by rows or columns.

Related: How To Add a Column in Excel (With Reasons and Tips)

2. Select your data

Highlight the data you entered. You can make your selection by clicking on a cell and dragging your cursor across other cells. If you want to select additional data that's not adjacent to your current selection, press the "Crtl" key while making a separate selection.

3. Create a box plot

Go to the "Insert" tab. In the "Groups," click on the icon that says "Insert Statistic Chart" when you hover your cursor over it. Click "Box and Whisker."

4. Edit the box plot

You can personalize the box plot to better communicate your data. Consider double-clicking on the title or x- and y-axis fields to edit the name. You can also click the paintbrush icon to change the colors and other stylistic elements.

How to make a box plot in Excel manually

The "Box and Whisker" feature makes it easy to quickly create a box plot, but older versions of Excel may not have this feature. Here's how to make a box plot in Excel manually:

1. Set up your spreadsheet

Enter your data into a spreadsheet by copying and pasting values or typing values into adjacent cells. Leave space to enter values for the "Minimum," "Q1," "Median," "Q3" and "Maximum" values. Below is an example to consider as you review these steps. Column A contains a small set of data, and column C is for entering the values that column B indicates:

ABCD110Minimum

214Q1

315Median

416Q3

520Maximum

625

2. Calculate the 5 values

Calculate the minimum value by typing "=QUARTILE.INC" into C1 and pressing "Enter." Select the data set, which is A1 through A6 in this example. Add a comma, and enter "0" as the second argument to indicate that you want to calculate the minimum value. Type a closing parenthesis and press "Enter."

Repeat this process for the other four values using the formulas below as a reference. Recognize that the first argument is always the data set, and the second argument is a number between zero and four that represents which percentile or value to calculate. Q1 has "1" as the second argument, Median has 2"," Q3 has "3" and Maximum has "4."

  • Q1: "=QUARTILE.INC(A1:A6, 1)"

  • Median: "=QUARTILE.INC(A1:A6, 2)"

  • Q3: "=QUARTILE.INC(A1:A6, 3)"

  • Maximum: "=QUARTILE.INC(A1:A6, 4)"

Related: How To Make a Timeline in Excel in 8 Steps

3. Calculate two differences

The next step is to calculate the difference between the Q1 and minimum values. You can enter the equation next to the cell that contains the minimum value. Type an equal sign into this cell and select the cell that contains the Q1 value. Type a minus sign, select the cell that contains the minimum value and press "Enter." In the example above, these steps would result in cell D1 containing this formula:

=C2-C1

Following a similar procedure, calculate the difference between the maximum and Q3 values. Select the cell next to the maximum value, type "=" and select the cell that contains the maximum value. Type a minus sign, select the cell that contains the Q3 value and press "Enter." In the example above, these steps would result in cell D5 containing this formula:

=C5-C4

4. Insert a column chart

Select the Q1, Median and Q3 fields and their respective values. In the example above, you would select B2:C4. Go to the "Insert" tab and view the "Charts" group. Open the "Column" drop-down menu and select "Clustered Column" underneath "2-D Column."

5. Set up the chart

Right-click the chart's legend and select "Select Data." Click "Switch Row/Column" and select "OK." Right-click any of the bars and select "Format Data Series." Under the "Series Overlap" section, drag the slider so that the series becomes 100% overlapped. Click "Close."

You can do a few things to make the chart look more organized. Consider clicking the label at the bottom of the x-axis and pressing the "Delete" button on your keyboard. You may also right-click on the chart and select "Format gridlines." Click the radio button next to "No line" in the "Line Color" tab and click "Close."

6. Create the rectangular component of the box plot

Right-click the single rectangle in the diagram and click "Select Data." In the "Legend Entries" section, click on an item and use the up and down arrows to reorganize the entries so that they appear in the following order:

  • Q3

  • Median

  • Q1

Click "OK" to finalize the changes. Right-click the bottom rectangle and select "Format Data Series." Click the "Fill" tab, select "Solid fill" and change the color to white. Click "Close."

Related: 12 Types of Error in Analysis

7. Add the whiskers

Select the chart. Click the "Layout" tab in the ribbon at the top of the spreadsheet. In the "Analysis" group, select "Error Bars." Click "More Error Bars Options" in the drop-down menu. Select "Q1" and click "OK." Click "Minus" under "Vertical Error Bars and choose "Cap" as the "End Style." Select the "Custom" radio button under "Error Amount" and click "Specify Value." Highlight the "Negative Error Value" field, select the cell that contains the difference between the Q1 and minimum values and click "OK."

Repeat this process to add the top whisker. After clicking the "More Error Bars Options," select "Q3" instead of "Q1." Click "OK," select "Plus" and choose "Cap." Click the "Custom" radio button, click "Specify Value" and highlight the "Positive Error Value" field. Select the cell that contains the difference between the maximum and Q3 values. Click "OK."

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles