how to create a box and whisker plot in excel

HOW TO CREATE A BOX AND WHISKER PLOT IN EXCEL HOW TO

“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.”, “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.”ĭoes not sound pretty right to me. 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 Microsoft (2016) Create a box and whiskers chartĪbout the definition of the whisker lines: Issues that arise when some of the data is negative are also explored in a little more depth there.

HOW TO CREATE A BOX AND WHISKER PLOT IN EXCEL HOW TO

See Creating Box Plots with Outliers in Excel for how to create a box plot with outliers manually, using only Excel charting capabilities. If some outlier is negative but none of the lower whiskers are negative, then a second y-axis is not needed. Keep in mind, though, that a second y-axis is only employed when the lower whisker of at least one of the box plots is negative. Negative numbers are handled in a manner similar to that for Box Plots without Outliers (often using a second y-axis). Note too that if you leave this field blank, the outlier multiplier factor defaults to 2.2. enter 150 if you want a 1.5 outlier multiplier factor. If the Percentage option is set on the Configuration dialog box, then you should enter a value 100 times the desired value in the Outlier Multiplier field e.g. 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). The Outlier Multiplier is shown in cell F2 of the output displayed in Figure 3. 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 output for Example 1 of Creating Box Plots in Excel is shown in Figure 3.įigure 3 – Output from Box Plots with Outliers toolĪs you can see, the output is similar to that shown in Figure 1, except that this version is available in other releases of Excel prior to Excel 2016. To produce such a box plot, proceed as in Example 1 of Creating Box Plots in Excel, except that this time you should select the Box Plots with Outliers option of the Descriptive Statistics and Normality data analysis tool. The Real Statistics Resource Pack also provides a way of generating box plots with outliers. 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). Note that we could also use the array formula

The only outlier is the value 1850 for Brand B, which is higher than the upper whisker, and so is shown as a dot. The boundaries of the box and whiskers are as calculated by the values and formulas shown in Figure 2. Values outside this range are considered to be outliers and are represented by dots. 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. The box part of the chart is as described above, except that the mean is shown as an ×. You can add a legend as well as chart and axis titles as usual. The chart shown on the right side of Figure 1 will appear.įigure 1 – Excel’s Box and Whiskers chart To access this capability for Example 1 of Creating Box Plots in Excel, highlight the data range A2:C11 (from Figure 1) and select Insert > Charts|Statistical > Box and Whiskers. Starting with Excel 2016 Microsoft added a Box and Whiskers chart capability.

How to create a box and whisker plot in excel