It opens up a window within which specifies the lower whisker values (Q1 – Minimum B23:D23) under Negative Error Value and Click OK. Step 7: As soon as you click on More Options… Format Error Bars menu will appear > Error Bar Options > Direction : Minus Radio Button (since we are adding the lower whisker) > End Style : Cap radio button > Error Amount : Custom > Select Specify Value. Select the stack chart portion, which represents the Q1 (Blue bar) > Click on Plus Sign > Select Error Bars > Navigate to More Options… dropdown under Error Bars. I will start with the lower whisker first.

Step 5: Inside Insert Chart window > All Charts > navigate to Column Charts and select the second option, which specifies the Stack Column Chart and click OK. Step 4: Go to the Insert tab on the excel ribbon and navigate to Recommended Charts under the Charts section. Select the data from B24:D26 for boxes (remember Q1 – Minimum and Maximum – Q3 are for the Whiskers?) Step 3: Now, we are about to add the boxes as the first part of this plot. And combine together, it will form a Box-Whisker Plot. Q1, Q2-Q1, Q3-Q2 (Interquartile ranges) as Box.

Therefore, we use the differences between Q1 – Minimum and Maximum – Q3 as Whiskers. Step 2: Now, since we are about to use the stack chart and modify it into a box and whisker plot, we need each statistic as a difference from its subsequent statistic. See the screenshot below for five-number summary statistics. MIN function allows you to give your Minimum value MEDIAN will provide you the median Quarter.INC allows us to compute the quarter values, and MAX allows us to calculate the maximum value for the given data. Step 1: Compute the Minimum Maximum and Quarter values. In this example, we will plot the Box and Whisker plot using the five-number summary that we have discussed earlier. You can see a Box and Whisker plot as shown below.Įxample #2 – Box and Whisker Plot in Excel Right-click on the chart, select the Format Data Series option, then select the Show inner points option. Step 2: Select the Box and Whisker option, which specifies the Box and Whisker plot.

You will have several graphical options under the Charts section. Step 1: Select the data and navigate to the Insert option in the Excel ribbon. TEXT and String Functions in Excel (26+).Lookup and Reference Functions in Excel (36+).

Excel box and whisker 2016