We place the formula =IF(A4>F$15,A4,IF(A4 Data|Select Data. One approach for doing this is shown in Figure 7. We now proceed to add the outliers to the chart, but first, we need to identify the outliers. The chart changes to the more acceptable format shown in Figure 6.įigure 6 – Box Plot after adding brand means Identifying Outliers Then press the OK button on the Change Chart Type dialog box. Now click on the dropdown menu for m (headed by Stacked Column) and select the first Scatter Plot option. The dialog box shown in Figure 5 will now appear.Īs we can see from Figure 5, the means are treated as a Stacked Column.
#Make box and whisker plot series#
Next, we select Change Series Chart Type … from the menu that appears. We do this by first clicking on the OK button of the Select Data Source dialog box (see Figure 2) that reappears to close this dialog box, and then right-clicking on any of the blue bars shown in Figure 4. Thus, we need to tell Excel that this added series should be interpreted instead as a scatter plot. We get this result because Excel interprets the added means as stacked bar charts. When you press the OK button, the chart changes to that shown in Figure 4. Fill in the dialog box that appears as shown in Figure 3. This will bring up the dialog box shown in Figure 2.Ĭlick on the Add button (on the left side of the dialog box). We use the following array formulas to calculate the values for Lower (i.e.
The Q1, Median, Q3 and Mean values for Brand A in the range F12:F17 are calculated by the formulas =QUARTILE(A4:A13,1), =MEDIAN(A4,A13), =QUARTILE(A4:A13,3) and =AVERAGE(A4:A13).
To create this box plot manually, you need to first create the values in range F12:F17.
#Make box and whisker plot how to#
We now show how to construct this output manually using standard Excel capabilities. ExamplesĮxample 1: Repeat Example 2 of Special Charting Capabilities, showing outliers. The other difference is that we need to manually add a small circle or each data value greater than Q3 + 1.5*IQR or less than Q1 – 1.5*IQR. Similarly, the bottom whisker ends at the smallest data value greater than or equal to Q1 – 1.5*IQR. One key difference is that instead of ending the top whisker at the maximum data value, it ends at the largest data value less than or equal to Q3 + 1.5*IQR. The procedure for manually creating a box plot with outliers (see Box Plots with Outliers) is similar to that described in Special Charting Capabilities.