creating a box and whisker plot in excel

#CREATING A BOX AND WHISKER PLOT IN EXCEL HOW TO#
#CREATING A BOX AND WHISKER PLOT IN EXCEL PC#
#CREATING A BOX AND WHISKER PLOT IN EXCEL SERIES#

We show how to do this in Excel 2013 and later versions. The last step is to change the marker symbol used to indicate the outliers. The result is shown in Figure 9.įigure 9 – Box Plot with Outliers Changing the Outlier Markers We add the outlier -300 in a similar fashion.

#CREATING A BOX AND WHISKER PLOT IN EXCEL SERIES#

Since the outlier 1850 (shown in cell R10 of Figure 7) is in Brand B, we must insert 2 in the Series X values field. Note that this time the default chart is a scatter chart (the last chart type selected) and so we are prompted for both X and Y values (unlike the prompt in Figure 3). Fill in the dialog box that appears as shown in Figure 8.įigure 8 – Add new series for one outlier This will bring up the dialog box shown in Figure 2. 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. 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. 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. 'Microsoft Agents' work for Microsoft Support.The procedure for manually creating a box plot with outliers (see Box Plots with Outliers) is similar to that described in Special Charting Capabilities. 'Independent Advisors' work for contractors hired by Microsoft.

#CREATING A BOX AND WHISKER PLOT IN EXCEL PC#

I Just Fell For a PC Support Scam, Now What? I am an unpaid volunteer and do not work for Microsoft. Schemes and other forms of online fraud - Office Support It is an attempt to take over your computer, steal your data, your

Questions and comments are answered in the forums themselves, not elsewhere.Ī request asking you to contact someone is fake. Do not click links to phony agents in your web browser. Do not call phone numbers.ĭo not reply to email messages. No legitimate person or entity will ever ask you to call or contact them. Just in case, here's my warning:ĭo NOT respond to replies asking you to contact Microsoft Support, Executive, agent, tech, MVP, advisor or anyone The message (it was deleted from the forum), but if you had subscribed to the thread you would have received an email with the scam message. This is a standard warning that I give whenever I see a thread had a message from a support scammer.

Creating a box and whisker plot in excel