Statistics How To

Box and Whiskers Chart: What is it?

Statistics Definitions > What is a Box and Whiskers Chart?

Box and Whiskers Chart: Overview

A box and whiskers chart, sometimes called a boxplot, is a way to highlight the middle fifty percent of a data set, including the median. The middle fifty is also called the interquartile range. It’s called a box and “whiskers” chart because the graph looks (vaguely!) like a box with the whiskers of a cat.

Box and whiskers chart, or boxplot.

The box and whiskers chart shows you how your data is spread out. Five pieces of information (the “five number summary“) are generally included in the chart:

  • The minimum (the smallest number in the data set). The minimum is shown at the far left of the chart, at the end of the left “whisker.”
  • The first quartile, Q1, is the far left of the box (or the far right of the left whisker).
  • The median is shown as a line in the center of the box.
  • The third quartile, Q3, shown at the far right of the box (at the far left of the right whisker).
  • The maximum (the largest number in the data set), shown at the far right of the box.

box and whiskers graph in excel 7

You can easily read a boxplot to find the five number summary. For example, the above image shows a box and whiskers chart with the following information:

  • Minimum: 20
  • Q1: 160
  • Median: 200
  • Q3: 330
  • Maximum: 590

box-and-whiskers-graph-
Exception: If your data set has outliers (values that are very high or very low and fall far outside the other values of the data set), the box and whiskers chart may not show the minimum or maximum value. Instead, the ends of the whiskers represent one and a half times the interquartile range (1.5*IQR).

Read more about how to read a boxplot to get the five number summary.

How to Make a Box and Whiskers Chart in Excel

Watch this video on How to Make a Box and Whiskers Chart in Excel, or read the steps below:

Excel does not have a box and whiskers “Insert Graph” function, so you have to build one using stacked bar charts. This is a little more time-consuming than inserting a simple graph, but it you will end up with a great looking box and whiskers graph.

Note: This is an advanced Excel technique, so if you are new to Excel you may want to watch the video, which has more information and visuals for each step to make a box and whiskers chart in Excel.

How to Make a Box and Whiskers Chart in Excel: Steps

Sample problem: Make a box and whiskers chart in Excel for the following data set: 25, 145, 145, 148, 178, 178, 198, 201, 222, 210, 565, 589, 485, 333, 358, 158, 257.

Step 1: Type your data into one column in an Excel worksheet. For this example, type your data into cells A1:A11.

Step 2: Click an empty cell type “MIN, Q1, MED, Q3 and MAX” in a single column. In the next column over, type formulas for MIN, Q1, MED and MAX. The formulas are:

  • =MIN(A1:A17)
  • =QUARTILE(A1:A17,1)
  • =MED(A1:A17)
  • =QUARTILE(A1:A17,3)
  • =MAX(A1:A17)

box and whiskers chart in excel

Step 3: In the next column over, type your Min (in this case, 25) into the cell next to the Min you calculated.

box and whiskers graph in excel 2

Step 4: Subtract each value in the previous column from the next value. In other words, subtract the Min from Q1, the Q1 from the Med, the Med from Q3 and Q3 from the Max.

box and whiskers graph in excel 3

Step 5: Highlight the column of differences you calculated in Step 4, then click “Insert,” then click “Bar” and then click “Stacked Bar.”

box and whiskers graph in excel 4

Step 6: Click the graph and then click the “Switch Row/Column” button.

box and whiskers graph in excel 5

Step 7: Select the left-hand blue box, right-click and then click “Format Data Series.”

box and whiskers graph in excel 6

Step 8: Click “Fill,” then click the “No Fill” radio button and then click “Close.”

Step 9: Add the whisker to the left-hand side using the following method:

  1. Click the left-hand red box.
  2. Click the “Layout” tab, then click “Error Bars” and then click “More Error Bar Options.”
  3. Click “Error Bars with Standard Error.”
  4. Click the “Layout” tab again, then click “Error Bars” and then click “More Error Bar Options.”
  5. Click “Mins” and then type the difference between Q1 and the Min into the “Fixed Value” box. For this sample problem, that value is 133.
  6. Click “Close.”
  7. Remove the red color (fill) using the technique outlined in Steps 7 and 8 above.

Step 10: Add the whisker to the right-hand side of the box and whiskers chart Excel using the following method:

  1. Click the purple box.
  2. Click the “Layout” tab, then click “Error Bars” and then click “More Error Bar Options.”
  3. Click “Error Bars with Standard Error.”
  4. Click the “Layout” tab again, then click “Error Bars” and then click “More Error Bar Options.”
  5. Click “Plus” and then type the difference between Q3 and the Max into the “Fixed Value” box. For this sample problem, that value is 256.
  6. Click “Close.”
  7. Remove the light-blue color (fill) using the technique outlined in Steps 7 and 8 above.

box and whiskers graph in excel 7
That’s it: You’ve created a box and whiskers chart in Excel!

Step 11: (Optional)Delete the “Series” information from the right: Click the name and then press the “Delete” key.

Tip: If you don’t see an error bar show up, that’s likely because you need to change the line color to “Black” from the More Error Bar Options window.

Check out our YouTube channel for more Excel help and tips!

------------------------------------------------------------------------------

If you prefer an online interactive environment to learn R and statistics, this free R Tutorial by Datacamp is a great way to get started. If you're are somewhat comfortable with R and are interested in going deeper into Statistics, try this Statistics with R track.

Comments are now closed for this post. Need help or want to post a correction? Please post a comment on our Facebook page and I'll do my best to help!
Box and Whiskers Chart: What is it? was last modified: October 12th, 2017 by Stephanie Glen