How to Make a Box and Whisker Plot in Excel

Box plots

The emergence of Excel has made life easier for all analysts and statisticians. It is so much easier to present data in various forms on Excel. There are pivot tables, bar graphs and box and whisker plot, which help in representing the data in a more understandable format. This article will give you detailed information about how to make a box and whisker plot. But, before we start on with that, we need to understand the basic question – “What is a box and whisker plot?” and how does that help us.

What is a Box and Whisker Plot?

It is a graphical tool which makes it easy for a person to visually compare the variations between all the data sets that are evaluated. These are the simple ways for representing the statistical data on plot. In this, the rectangles are drawn for showing the second and the third quartiles. And, there is a vertical line which shows the Median value. Both the upper and the lower quartiles are displayed as horizontal lines on both the sides of rectangle. It has the name of box and whisker because of the lines which extend vertically from boxes. These lines indicated the variability outside upper quartiles and lower quartiles.

Uses of Box and Whisker Plot

There are various uses of this plot. Given below are some of the main features and advantages:

  • Easy to use: It is a convenient way for depicting the numerical data groups in a graphical manner. And, this is done with the help of quartiles. These can be drawn vertically as well as horizontally.
  • No Assumptions: These display the variations in samples without doing any kind of assumptions on the statistical distributions.
  • Skewness and dispersion: The box plats are not parametric. The spacing between various parts of the box shows the scenes and dispersion in the data.
  • Various statistical values: Other than the points, these plots also helps one is estimating range, mid-range, mid-hinge, inter-quartile ranges, standard deviations and tri-mean. These plots can be used for finding out the Median, Maximum, Minimum, 1st Quartile (25%), 3rd Quartile (75%). So, one can easily find out even the 2nd, 9th, 25th, 50th, 75th, 98th and 99thpercentile along with the Median.
  • Uniformity: These plots are quite uniform. The top and the bottom of the box are the first and the third quartiles. The bands inside box are the second quartile or the Median.
  • Easier comparisons: It also makes the comparisons between the data sets easier and quicker.
  • Equal spacing: If there is a normal distribution of data, all the marks on the plot will be equally spaced.

Some Additional Features

  • The data which is not included in between whiskers are to be plotted as outliers with dots, small circles or stars.
  • Some of the box plots also make use of an additional character for representing mean of the sample data.
  • Some of the box and whisker plots make use of cross-hatch that is placed on the whiskers. All the box plots will definitely have whiskers.

How to Make a Box and Whisker Plot?

Now, coming to the creation of box and whisker plots, you will need to follow the points given below:

  • Gather the data: Get the data for which you need to create plots.
  • Organize this data: Order the data by sorting them in numerical order. This should be done in ascending or increasing order.
  • Calculate Medians: Find out the Median of this data range. This Median will divide the data into halves. And, for finding out the quartiles, the Medians of the 2 halves will have to be found out. If the data set has odd set of numbers, the Median would have an equal amount of numbers on both sides. And, if the data set has even set of numbers, the 2 numbers in the middle would have to be added up and averaged.
  • Calculate 1st and 3rd quartiles: This can be found out by finding out the Median of each of the halves.
  • Draw plot lines: Now, you will need to draw plot lines and mark the Median and quartiles.

This is the basic methodology used for making the box and whisker plots.

How to Make Box and Whisker Plots in Excel?

Given below are the steps for making these plots on Excel:

  1. Type the sample data in the worksheet.
  2. Select the cells where the data is entered. Now, click on Chart on the Insert menu.
  3. On the tab, Standard types, click on Stock under the Chart Type. Now, click on the fourth chart from there.
  4. Now, click on the Next tab and click on Data Series in Rows under Data Range.
  5. On the tab Legend, uncheck the Show Legend check box.
  6. On the tab Axes, uncheck the Value (Y) Axis under the Secondary Axis and then click on Finish.

This way, you would have created your box and whisker plots. Hope you have learned how to make box and whisker plots and would be able to do them on your own.

0 comments… add one

Leave a Comment