SAP - Advanced Excel Chart

Back to Course

Lesson Description

Lession - #493 Advanced Excel Charts – Box chart

Box and Whisker maps, also appertained to as Box Plots are generally used in statistical analysis. For illustration, you can use a Box and Whisker map to compare experimental results or competitive test results.

What's a Box and Whisker Chart?
In a Box and Whisker map, numerical data is divided into quartiles and a box is drawn between the first and third quartiles, with an fresh 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.

Advantages of Box and Whisker Charts
You can use Box and Whisker map wherever to understand the distribution of data. And the data can be different that's drawn from any field for statistical analysis. Examples include the following –

• check 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's 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.

Preparation of Data
Suppose you're given the following data-

produce 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.

• Minimum Value.

• First Quartile.

• Median Value.

• Third Quartile.

• Maximum Value.
Step 2 Create a third table from the second table, computing the differences-

• Retain the first row – Minimum Value as it is.

• In the second row – compute values as First Quartile- Minimum Value.

• In the third row – compute values as Median Value-First Quartile.

• In the fourth row – compute values as Third Quartile-Median Value.

• In the fifth row – compute values as Maximum Value- Third Quartile.

You'll get the third table as shown below.

You'll use this data for the Box and Whisker map.

Creating a Box and Whisker Chart
Following are the steps to create a Box and Whisker map.

Step 1 Select the data obtained as the third table in the previous section.
Step 2 Insert a Stacked Column map.
Step 3 Click the DESIGN tab on the Ribbon.
Step 4 Click Switch Row/ Column button in the Data group.

Your map 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 produce the Whiskers.

• Right click on the Top Data Series.

• Change Fill to No Fill.

• Click the DESIGN tab on the Ribbon.

• Click Add Chart Element in the Chart layouts group.

• Click Error Bars in the dropdown list and select Standard Deviation.
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.

o Select Minus under Direction.

o Select No Cap under End Style.

o 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.

• Select Solid line under LINE.

• Select the color as dark blue.

• Type1.5 in the Width box.
Step 11 Repeat the above given steps for the second lower bottom Series.
Step 12 Next, format the boxes as follows –

• Right click on one of the Box series.

• Click Fill.

• Choose color as light blue.

• Click Outline.

• Choose the color as dark blue.

• Click Weight.

• Select 1 ½ pt.

Step 13 Repeat the steps given above for the other Box series.

Your Box and Whisker map is ready.