Excel 2016 has added a Box and Whiskers chart capability. To access this capability for Example 1 of, highlight the data range A2:C11 (from Figure 1) and select Insert Charts Statistical Box and Whiskers. The chart shown on the right side of Figure 1 will appear. Figure 1 – Excel’s Box and Whiskers chart You can add a legend as well as chart and axis titles as usual.
Simple Box Plot in Excel. Do the Calculations. To build a box plot, you’ll need to do a few calculations for each set of data: Min, Quartile 1, Median, Quartile 3, and Max. From those calculations, you can figure out the height of each box, and the length of the whiskers. The diagram below shows where each measure appears in the box plot. Excel Box and Whisker Diagrams (Box Plots) – Peltier Tech Blog – Box plots are a useful statistical graph type, but they are not offered in Excel's chart types. This tutorial shows how to create box plots in Excel.
The box part of the chart is as described above, except that the mean is shown as an ×. The whiskers extend up from the top of the box to the largest data element that is less than or equal to 1.5 times the interquartile range (IQR) and down from the bottom of the box to the smallest data element that is larger than 1.5 times the IQR. Values outside this range are considered to be outliers and are represented by dots. The boundaries of the box and whiskers are as calculated by the values and formulas shown in Figure 2.
The only outlier is the value 1850 for Brand B, which is higher than the upper whisker, and so is shown as a dot. Figure 2 – Formulas for the Box Plot Note that we could also use the array formula =MAX(IF(C2:C11=H8,C2:C11,MAX(C2:C11))) to calculate a value for cell H10. In fact, since the Excel Box Plot is only available in Excel 2016, we can also use the Excel 2016 (non-array) formulas =MAXIFS(C2:C11,”=”&H8). Real Statistics Data Analysis Tool: The Real Statistics Resource Pack also provides a way of generating box plots with outliers. To produce such a box plot, proceed as in Example 1 of, except that this time you should select the Box Plots with Outliers option of the Descriptive Statistics and Normality data analysis tool. The output for Example 1 of is shown in Figure 3.
Figure 3 – Output from Box Plots with Outliers tool As you can see, the output is similar to that shown in Figure 1, except that this version is available in other releases of Excel besides Excel 2016. Also the Outlier Multiplier is not fixed at 1.5, but can be set to another value by the user (in the dialog box for the Descriptive Statistics and Normality data analysis tool).
The Outlier Multiplier is shown in cell F2 of the output displayed in Figure 3. This value is used in calculating the Min and Max values (which are the values at the bottom of the lower whisker and the top of the upper whisker). Cell F12 contains the array formula =MIN(IF(ISBLANK(A4:A13),””,IF(A4:A13=F13-$F2.(F15-F13),A4:A13,””))) and cell F16 contains the formula =MAX(IF(ISBLANK(A4:A13),””,IF(A4:A13.
Hi Charles, A quick question about outliers: When I ask for a box plot with outliers, the outliers list often includes one or more zero values (sometimes many more–76 in the output that inspired me to ask this question) even though the data set in question has a minimum value much greater than zero. Is this some sort of bug? Or, are these being generated correctly by some stats operation that I’m not familiar with? And then, of course, there is always user error! I can just delete these values, but don’t want to if it isn’t appropriate. Please advise when convenient.
FYI, I am using Excel 2013 on a Windows 10 machine. Thanks in advance for your attention to this question. This add on and your website to support it are really excellent! Regards, Chris V. Hi Charles, About the definition of the whisker lines: I agree with the formulas in Figure 2 (and also with the array formulas) but, even if (or just because?) I am not a native English speaker, the sentence “The whiskers extend up from the top of the box to the smallest data element that is less than or equal to 1.5 times the interquartile range (IQR) and down from the bottom of the box to the largest data element that is larger than 1.5 times the IQR.” does not sound pretty right to me. Shouldn’t be instead: “The whiskers extend up from the top of the box to the nth smallest data element where n is the number of elements that are less than or equal to 1.5 times the interquartile range (IQR) and down from the bottom of the box to the nth largest data element with n being the number of elements that are larger than 1.5 times the IQR.”, if one would trace the precise syntax of the formula?
Or even more simply: “The whiskers extend up from the top of the box to the LARGEST data element that is less than or equal to 1.5 times the interquartile range (IQR) and down from the bottom of the box to the SMALLEST data element that is larger than 1.5 times the IQR.” or something alike just because, strictly speaking, “the smallest data element that is less than or equal to 1.5 times the interquartile range (IQR)” is simply the smallest data element. In any case, thank you for your work! Best regards Federico. Godfrey, It really depends on how you define “outlier”.
If an outlier is a data value that is much higher or lower than the mean or median, then in a large sample you should expect to have some outliers, and typical statistical tests should be able to deal with these. If an outlier is a data value that is much higher or lower than the other data values, then this may violate one of the assumptions of the statistical test (e.g. The homogeneity of variance assumption for ANOVA). In any case, you shouldn’t automatically resort to triming or winsorizing the data. This may be appropriate sometimes, but then you are essentially testing the trimmed or winsorized data and not the original data. Sometimes it is best to report the results of your test both with the original data and then with the trimmed or winsorized data (or simply with the original data with the outlier(s) removed). Generally, the first thing you should do is to try to determine whether the outlier occurs because this is a reasonable data value (in which case you don’t want to remove it) or because some error has been made (miscopying the value, leaving out the decimal point, faulty measurement device, etc.), in which case you should either correct the error or leave out this data element(s).
![Box And Whiskers Excel 2013 Box And Whiskers Excel 2013](/uploads/1/2/5/3/125352477/184419501.png)
As usual in statistical analysis, judgement is required.