Box and Whisker charts, also referred to as Box Plots are commonly used in statistical analysis. For example, you can use a Box and Whisker chart to compare experimental results or competitive exam results.
In a Box and Whisker chart, numerical data is divided into quartiles and a box is drawn between the first and third quartiles, with an additional line drawn along the second quartile to mark the median. The minimums and maximums outside the first and third quartiles are depicted with lines, which are called whiskers. Whiskers indicate variability outside the upper and lower quartiles, and any point outside the whiskers is considered as an outlier.
A Box and Whisker chart looks as shown below.
You can use Box and Whisker chart wherever to understand the distribution of data. And the data can be diverse that is drawn from any field for statistical analysis. Examples include the following −
Survey responses on a particular product or service to understand the user’s preferences.
Examination results to identify which students need more attention in a particular subject.
Question-Answer patterns for a competitive examination to finalize the combination of categories.
Laboratory results to draw conclusions on a new drug that is invented.
Traffic patterns on a particular route to streamline the signals that are enroute. The outliers also help in identifying the reasons for the data to get outcast.
Suppose you are given the following data −
Create a second table from the above table as follows −
Step 1 − Compute the following for each of the series – 2014, 2015 and 2016 using Excel Functions MIN, QUARTILE and MAX.
The resulting second table will be as given below.
Step 2 − Create a third table from the second table, computing the differences −
You will get the third table as shown below.
You will use this data for the Box and Whisker chart.
Following are the steps to create a Box and Whisker chart.
Step 1 − Select the data obtained as the third table in the previous section.
Step 2 − Insert a Stacked Column chart.
Step 3 − Click the DESIGN tab on the Ribbon.
Step 4 − Click Switch Row / Column button in the Data group.
Your chart will be as shown below.
Step 5 − Right click on the bottom Data Series. Click Fill and select No Fill.
The bottom Data series becomes invisible.
Step 6 − Deselect Chart Title and Legend in Chart Elements.
Step 7 − Change the Horizontal Axis Labels to 2014, 2015 and 2016.
Step 8 − Now, your Boxes are ready. Next, you have to create the Whiskers.
Step 9 − You got the top Whiskers. Next, format Whiskers (Error Bars) as follows −
Right click on the Error Bars.
Select Format Error Bars.
Select the following under ERROR BAR OPTIONS in the Format Error Bars pane.
Select Minus under Direction.
Select No Cap under End Style.
Select Percentage under Error Amount and type 100.
Step 10 − Click the Fill & Line tab under ERROR BAR OPTIONS in the Format Error Bars pane.
Step 11 − Repeat the above given steps for the second lower bottom Series.
Step 12 − Next, format the boxes as follows.
Step 13 − Repeat the steps given above for the other Box series.
Your Box and Whisker chart is ready.