Select the upper box, click Layout > Error Bars > More Error Bars Options
Output: Stacked boxes for boxplot Insert the whiskers Switch Row/Column option in Design tabįigure 8. Select cells D10:E12, click Insert tab > Column > Stacked ColumnĪ chart showing three columns will be created.Ĭlick the chart, Design tab > Switch Row/Columnįigure 6. Plot the three boxes stacked on top of each other With all the relevant values prepared, we can now create a box and whisker plot in Excel version 2016, 2013, or lower.
Box and whiskers plot excel how to#
However, it is still better to learn how to create a boxplot using only the Column Chart. In newer Excel versions 20, the box and whisker plot chart type is already available. Chart values for boxplot How to create a boxplot? Hidden box, whose value or height is equal to Q1įigure 3.We then compute our required data using the functions MEDIAN, QUARTILE, MIN and MAX, as shown in Column F.įrom the above data, we are able to prepare the charts that we are going to plot. Suppose we have below table of values in cells B3:B13. Maximum – the largest value in our data set.
Final result: Box plot Preparing data to create a boxplot It quickly shows how our data is spread out, if it is symmetric or skewed.įigure 1. Again, you can verify this number by using the QUARTILE.EXC function or looking at the box and whisker plot.A box and whisker plot (box plot) shows data distribution in terms of median, minimum and maximum values, and the two quartiles: first and third. This makes sense, the median is the average of the middle two numbers.Ħ. You can verify this number by using the QUARTILE.EXC function or looking at the box and whisker plot.ĥ. In this example, n = 8 (number of data points).Ĥ. This function interpolates between two values to calculate a quartile. For example, select the even number of data points below.Įxplanation: Excel uses the QUARTILE.EXC function to calculate the 1st quartile (Q 1), 2nd quartile (Q 2 or median) and 3rd quartile (Q 3). Most of the time, you can cannot easily determine the 1st quartile and 3rd quartile without performing calculations.ġ. As a result, the whiskers extend to the minimum value (2) and maximum value (34). As a result, the top whisker extends to the largest value (18) within this range.Įxplanation: all data points are between -17.5 and 34.5. Therefore, in this example, 35 is considered an outlier. A data point is considered an outlier if it exceeds a distance of 1.5 times the IQR below the 1st quartile (Q 1 - 1.5 * IQR = 2 - 1.5 * 13 = -17.5) or 1.5 times the IQR above the 3rd quartile (Q 3 + 1.5 * IQR = 15 + 1.5 * 13 = 34.5). In this example, IQR = Q 3 - Q 1 = 15 - 2 = 13. Q 3 = 15.Įxplanation: the interquartile range (IQR) is defined as the distance between the 1st quartile and the 3rd quartile. The 3rd quartile (Q 3) is the median of the second half. The 1st quartile (Q 1) is the median of the first half. The median divides the data set into a bottom half. The x in the box represents the mean (also 8 in this example). On the Insert tab, in the Charts group, click the Statistic Chart symbol.Įxplanation: the middle line of the box represents the median or middle number (8).