Advanced Excel - Box and Whisker Chart

Advanced Excel – Box and Whisker Chart

Field and Whisker charts, additionally known as Field Plots are generally utilized in statistical evaluation. For instance, you should utilize a Field and Whisker chart to match experimental outcomes or aggressive examination outcomes.

What’s a Field and Whisker Chart?

In a Field and Whisker chart, numerical information is split into quartiles and a field is drawn between the primary and third quartiles, with a further line drawn alongside the second quartile to mark the median. The minimums and maximums exterior the primary and third quartiles are depicted with strains, that are known as whiskers. Whiskers point out variability exterior the higher and decrease quartiles, and any level exterior the whiskers is taken into account as an outlier.

A Field and Whisker chart seems to be as proven beneath.

Box and Whisker Chart

Benefits of Field and Whisker Charts

You should utilize Field and Whisker chart wherever to know the distribution of knowledge. And the information might be numerous that’s drawn from any subject for statistical evaluation. Examples embody the next −

  • Survey responses on a specific services or products to know the consumer’s preferences.
  • Examination outcomes to determine which college students want extra consideration in a specific topic.
  • Query-Reply patterns for a aggressive examination to finalize the mixture of classes.
  • Laboratory outcomes to attract conclusions on a brand new drug that’s invented.
  • Site visitors patterns on a specific path to streamline the indicators which might be enroute. The outliers additionally assist in figuring out the explanations for the information to get outcast.

Preparation of Information

Suppose you’re given the next information −

Preparing Box and Whisker Chart Data

Create a second desk from the above desk as follows −

Step 1 − Compute the next for every of the sequence – 2014, 2015 and 2016 utilizing Excel Capabilities MIN, QUARTILE and MAX.

  • Minimal Worth.
  • First Quartile.
  • Median Worth.
  • Third Quartile.
  • Most Worth.

Step Compute Series

The ensuing second desk can be as given beneath.

Resulting Second Table

Step 2 − Create a 3rd desk from the second desk, computing the variations −

  • Retain the primary row – Minimal Worth as it’s.
  • Within the second row – compute values as First Quartile – Minimal Worth.
  • Within the third row – compute values as Median Worth – First Quartile.
  • Within the fourth row – compute values as Third Quartile – Median Worth.
  • Within the fifth row – compute values as Most Worth – Third Quartile.

You’re going to get the third desk as proven beneath.

Get Third Table

You’ll use this information for the Field and Whisker chart.

Making a Field and Whisker Chart

Following are the steps to create a Field and Whisker chart.

Step 1 − Choose the information obtained because the third desk within the earlier part.

Step 2 − Insert a Stacked Column chart.

Select and Insert Data

Step 3 − Click on the DESIGN tab on the Ribbon.

Step 4 − Click on Change Row / Column button within the Information group.

Click Switch Row/Column

Your chart can be as proven beneath.

Chart Shown

Step 5 − Proper click on on the underside Information Collection. Click on Fill and choose No Fill.

Right Click on Bottom Data

The underside Information sequence turns into invisible.

Bottom Data Invisible

Step 6 − Deselect Chart Title and Legend in Chart Components.

Step 7 − Change the Horizontal Axis Labels to 2014, 2015 and 2016.

Change Horizontal Axis

Step 8 − Now, your Bins are prepared. Subsequent, it’s important to create the Whiskers.

  • Proper click on on the Prime Information Collection.
  • Change Fill to No Fill.
  • Click on the DESIGN tab on the Ribbon.
  • Click on Add Chart Aspect within the Chart layouts group.
  • Click on Error Bars within the dropdown record and choose Normal Deviation.

Boxes Ready

Step 9 − You bought the highest Whiskers. Subsequent, format Whiskers (Error Bars) as follows −

  • Proper click on on the Error Bars.
  • Choose Format Error Bars.
  • Choose the next underneath ERROR BAR OPTIONS within the Format Error Bars pane.
    • Choose Minus underneath Course.
    • Choose No Cap underneath Finish Type.
    • Choose Proportion underneath Error Quantity and kind 100.

Error Bar Options

Step 10 − Click on the Fill & Line tab underneath ERROR BAR OPTIONS within the Format Error Bars pane.

  • Choose Stable line underneath LINE.
  • Choose the colour as darkish blue.
  • Sort 1.5 within the Width field.

Click Fill and Line Tab

Step 11 − Repeat the above given steps for the second decrease backside Collection.

Repeat Steps for Bottom Series

Step 12 − Subsequent, format the bins as follows.

  • Proper click on on one of many Field sequence.
  • Click on Fill.
  • Select shade as gentle blue.
  • Click on Define.
  • Select the colour as darkish blue.
  • Click on Weight.
  • Choose 1½ pt.

Format Box Series

Step 13 − Repeat the steps given above for the opposite Field sequence.

Box and Whisker chart

Your Field and Whisker chart is prepared.