When done, close browser tab or window.
Start video.
McGraw Hill red cube logo. Because learning changes everything™
Title screen: Building a Box & Whisker Chart in Excel. Copyright ©2017 McGraw-Hill Education. All rights reserved.
Narrator: "In this video, I'll walk through the simple process of creating a box plot using Excel's new Box & Whisker Chart, and then we'll interpret the wealth of data available from a BoxPlot. This set of data represents the online-investment-portfolio values for 70 adults age 40 to 50 years old. The numbers are in thousands of dollars. A BoxPlot is now very easy to build in Excel. Select column 'A.' Go to Insert, Chart, All Charts, Box & Whisker, and click 'Okay.'"
With column A selected, the bottom right corner of the Charts section, found in the Insert tab, is selected to open the Insert Chart dialog box. At the top of the dialog box, the All Charts tab is clicked to select Box & Whisker.
Narrator: "We're almost done with the formatting. Let's change the title to 'Portfolio Value for Clients 40 to 50 Years Old.'"
The title of the chart is edited by clicking the default title text.
Narrator: "Let's also add values to our box so that we can see the numbers we're looking at. I'll go ahead and left-click here, and on the plus sign, Data Labels."
With the chart selected, the plus sign outside the top right corner of the chart is clicked. The Chart Elements list of checkboxes appears. Data Labels is checked.
Narrator: "Now I've got the numbers showing up inside my BoxPlot. I'll expand it a little bit. Let's look at the rich information that we can get from a BoxPlot. First, you can see two measures of central tendency and immediately tell whether your data are skewed or symmetric. The median is the line through the blue box. The 'X' is our mean."
The median of the Box Plot is 199.75, the mean is 242.7328571.
Narrator: "When the mean is greater than the median, we say that our data is right, or positively skewed. The values will trail to the right of our peak. The top of the blue box is our third quartile. The bottom is the first quartile."
Third quartile, 316.9; First quartile, 82.675.
Narrator: "The median, as you know, is the second quartile. And the difference between the first and third quartile is the interquartile range. If the upper value on the box is our third quartile, which is 316. 9, then everything above that third quartile represents 25% of our data—the top 25%. But when Excel crunches the data for us, it looks for what values should be in that 25%. Anything outside of it is called an outlier. In this case, the inner-quartile range is the 234.225."
Text: IQR equals 316.9 minus 82.765 equals 234.225.
Text: 3rd Q plus 1.5 (IQR) equals approximately 668. That's the 'local maximum'. The nearest data value under that, 645.2, becomes our upper whisker.
Narrator: "1. 5 times that amount is added to the third-quartile value of 316. 9. Our upper boundary would be 668. 2375. When we look at our sorted data, the closest value below that is 645. All the values above this are called outliers. And as you can see when we look at the data that's shown above the top whisker, which is also called our local maximum, we have those four values."
Values above the top whisker: 669.9, 716.4, 899.5, 1002.2.
Narrator: "If we don't have outliers, then the local maximum will in fact be the actual maximum value of our data. When we look at the lower whisker, we can see 3. 3 is actually our lowest value in the data set."
Text: 1st Q - 1.5 (IQR) would be negative, our data set does not contain negative numbers. Thus no outliers below 'local minimum.'
Narrator: "Therefore, we set the local minimum to be our smallest data value, and there are not outliers below it. Recall I said our data is skewed to the right. The outliers are a good indicator of that. So is the longer line leading to our local maximum, versus the shorter line leading to the minimum, and the mean being greater than the median. In this video, we have walked through how to insert a Box & Whisker Chart, also known as a BoxPlot, and how to use the BoxPlot to identify mean, median, first and third quartile, interquartile range, outliers, and skewness in a set of data."
Video has ended.
Described transcript ©2023 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.