How to make a box and whisker plot in Excel?

How To Make A Box And Whisker Plot In Excel

A box and whisker plot, often known as a box plot, is a graph that displays a five-number data summary. This sort of graph is useful for displaying statistical data which includes test results or grades, new processes or changes before and after, or other numerical data analogies, which require a better presentable data form.

Steps To Create Box And Whisker Plot In Excel

This all begins with the data you provide, just like it’s done in any other sort of graphical data representation in Microsoft Excel.

In Excel, select the workbook and sheet that contains your data set. After that, to make the box and whisker plot, follow the step-by-step instructions given below.

  1. Select your data values for which you wish to plot the Box and Whisker graph. Select by dragging the left mouse click to select the data or click on the upper left cell of your data, while pressing down the shift key, and then press the bottom-right cell,
  2. Now select the ‘Insert‘ option from the above option/home bar.
  3. Choose ‘box and whisker plot‘ from the insert statistical chart menu in the Ribbon’s Chart Section.

In your spreadsheet, your new box and whisker plot will appear as shown below.

select box and whisker plot in Excel
select box and whisker plot

Double-Check The Data In Your Box Plot

Excel will plot the data with the exact values for you. But, if you want to double-check the data or just require it for yourself, Microsoft Excels’ built-in features can help you out.

Return to your data collection and use the steps given below to calculate the minimum, first quartile, median, third quartile, and maximum values.

Double Check the data in your box plot in Excel
Double Check the data in your box plot

Calculate Minimum, Median, And Maximum

  1. Begin by selecting the cell where you’d like the initial function to be performed. Let’s start with the bare minimum.
  2. Select formulas from the drop-down menu.
  3. From the Ribbon, select More Functions and hover your mouse to Statistical.
  4. Swipe down the list in the pop-out box to MIN and click on it.
  5. Whenever the function displays in the cell, you have the option of dragging over the data set or entering the cell labels on the Function Arguments box that opens by hitting OK.

Simply repeat the process for the Median and Maximum by selecting MEDIAN and MAX from the list of all functions.

Calculate minimum, median, maximum in Excel.
Calculate minimum, median, maximum

Quartile Function

  1. Select the cell in which you want the first quartile to appear.
  2. Select formulas from the drop-down menu.
  3. From the Ribbon, select More Function and hover your mouse over statistical.
  4. Select EXC from the list by Scrolling down.
  5. The function arguments will display whenever the function shows up in the cell. As with MIN, either choose the required data set or type it into the Array Box in the arguments window.
  6. Then in the arguments window, in the Quartile field, put the quartile number. This will be number one for the first quartile in this situation.
  7. Click the OK button.

You’ll use the identical steps as before to insert the function again for the third quartile, although in the Quart box, type 3.

Calculate Quartile using Quartile function in Excel
Calculate Quartile using Quartile function


That’s It! You, can now easily plot a Box and Whisker graph in Excel for your data and make it look more presentable and easier to interpret.

We hope you learned and enjoyed this lesson and we’ll be back soon with another awesome Excel tutorial!