Box and Whisker Plot Excel

Box and Whisker Plot Excel

A boxplot visualizes some descriptive data such as median, first and third quartile, and outliers of a variable. It is a fast and easy way to offer a first idea of the underlying distribution of a variable.

Intro Box and Whisker Plot 0:00 Creating a Boxplot 0:32 Box Plot Median and Quartile 2:16 Whisker 2:59 Boxplot Outliers 4:40

https://softtechtutorials.com/microsoft-office/excel/how-to-excel-box-plot/

To create the box plot, you select all observations including the titles. Next, you navigate to Insert and then to “Insert Statistics Chart”. Click on the down arrow and select “Box and whisker plot”.

The calculation method for the quartiles can be changed under the “three bars icon”. There you can choose to include or exclude the median from the calculation.

The two other icons at the top of the side panel can be used to adapt the look of the data. You can change the color, the size, the borders, and so on. When you click on the plot, you see “Chart Design” and “Format” appear in the ribbon.

Finally, let’s add data labels to the chart. You do this at the same location as the Legend option.

How to box and whisker plot Excel.

Now that we have shown how to construct a box plot in Excel, we will verify the figures shown on the plot.

The box itself on a box plot shows three descriptive data points: the median value, the first quartile, and the third quartile. With Excel's MEDIAN function, you can calculate the median. With the QUARTILE function, you can calculate the quartiles.

The horizontal line below the box is a whisker, this represents the minimum within a prespecified width from the first quartile. The other whisker represents the maximum within a prespecified width from the third quartile.

The upper whisker limit can then be computed by adding 1.5 times the interquartile range to the third quartile. The lower whisker calculates by subtracting 1.5 times the interquartile range from the first quartile.

To compute the whisker values now, it suffices to find the minimum and maximum values within the upper and lower whisker limits. You do this by using the MINIF and MAXIF functions.

excel box and whisker plot with outliers

We want to find all observations that fall outside our whisker limits. To find this we use a few functions: INDEX, SMALL, IF, and ROW.

•     The INDEX function returns the value on a given position in a range.
  

•     The SMALL function sorts a range and then returns the nth element
  

as you indicate with the second function argument.

•     The IF function returns a certain chosen value in case the given condition is true.
  

•     The ROW function returns the row number for a given cell.
  

This concludes our tutorial on Excel Box and Whisker Plot. I'm in ... https://www.youtube.com/watch?v=fm9zn-MP2As