Aung Shine
Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store information. Sometimes, we insert graphs or charts to better analyze data. The Box and Whisker Plot is such a kind where we perform and present statistical analysis. However, the box and whisker plot may not show in the charts. This article will show you the step-by-step procedures if Box and Whisker Plot is Not Showing in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
Step by Step Procedures If Box and Whisker Plot Is Not Showing in Excel
The Box and Whisker Plot comes in handy when we have a large dataset but we only need to show the statistical highlights. For that purpose, we only have to use 5 statistical numbers to show the distribution of the data values. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains Monthly Sales in the years 2020 and 2021. Instead of showing all 12 sales amounts in a year, we’ll present 5 values to show the distribution. Therefore, go through the steps below carefully to perform the task.
STEP 1: Set Table for 5 Statistics Numbers
The 5 Statistics Numbers are Minimum, First Quartile, Median, Third Quartile, and Maximum values. In our first step, we’ll prepare a table to find these 5 numbers for both years. See the figure below where we have the table ready.
STEP 2: Input Formulas
Now, we’ll insert formulas to get the statistical numbers.
- First, select cell G5.
- We’ll use the MIN function to determine the minimum value.
- Then, type the formula:
=MIN(C5:C16)- Next, press Enter.
- Thus, you’ll get the lowest value.
- Apply AutoFill to the right side.
- After that, select cell G6.
- Here, we’ll use the QUARTILE function to get the quartile values.
- So, input the formula:
=QUARTILE(C5:C16,1)- Press Enter to get the result.
- Use AutoFill to the right side.
- Subsequently, click cell G7.
- To get the median, we’ll apply the MEDIAN function.
- Insert the formula:
=MEDIAN(C5:C16)- Press Enter.
- Drag AutoFill to get for 2021.
- Afterward, in cell G8, input the formula:
=QUARTILE(C5:C16,3)- Return the result by pressing Enter.
- Apply AutoFill for 2021.
- Now, select cell G9.
- To get the maximum value, you can use the MAX function.
- Type the formula:
=MAX(C5:C16)- Click Enter.
- Drag AutoFill to the right.
STEP 3: Create New Table to Find Differences
This step is about creating a new table for finding the differences. We’ll then use the dataset of this table to have the box and whisker plot. So, follow the process below to perform the task.
- Firstly, in G12 and H12 cells, input the minimum values.
- Look at the below picture to make yourself clear.
- After that, select cell G13.
- Then, type the formula:
=G6-G5- Apply AutoFill to the right and also to the under for getting the outputs of the whole range.
- See the following dataset to understand better.
STEP 4: Insert Column Chart
- Now, select the range G12:H16.
- Next, go to Insert ➤ 2-D Column chart.
- As a result, you’ll see a chart.
- Click on the chart.
- Then, go to the Chart Design tab and press Switch Row/Column.
- Thus, the column chart is ready.
STEP 5: Modify Chart to Add Whisker Plot to Box
Now, we need to modify the chart to show the box and whisker plot. Therefore, learn the process below to add the box and whisker plot in excel.
- First of all, right-click on the minimum box which is in deep blue in the column.
- Choose Format Data Series from the Context Menu.
- Under the Fill & Line, check the circle for No fill.
- Hence, we won’t get to see the minimum box even though it’s there.
- Also fix the X-axis to input 2020 and 2021 by selecting data instead of 1 and 2.
- Follow the same steps to make the maximum box, which is the top bar in the column, invisible.
- Now, right-click on the maximum box.
- Next, go to Chart Design ➤ Add Chart Element ➤ Error Bars ➤ Standard Deviation.
- Thus, you’ll see the whisker lines.
- Click the whisker line and press the Ctrl and 1 keys together.
- Consequently, the Format Error Bars pane will appear.
- There, choose Direction ➤ Minus.
- End Style ➤ No Cap.
- Error Amount ➤ Percentage ➤ 100%.
- Repeat the steps above to choose no fill for the Q1 box which is in the orange color.
- Insert the whisker line for the Q1 bar by performing the above-described process.
- Hence, you’ll get your chart like the one shown below.
STEP 6: Embed Average Marker in Box and Whisker Plot
Moreover, we’ll embed the average marker in the plot.
- Firstly, select cell G17.
- Then, input the formula:
=AVERAGE(C5:C16)- Press Enter.
- Apply AutoFill for 2021.
- The AVERAGE function returns the average of a range.
- Now, copy the range F17:H17 and paste it into the chart.
- Next, right-click on the newly added boxes.
- Choose Change Series Chart Type.
- subsequently, go to the Combo tab.
- Select Line with Markers as Chart Type for the Average series.
- Press OK.
- Double-click on the freshly inserted line.
- Under the Line & Fill, choose No line.
- Moreover, choose the Black color for the Marker.
Final Output
Finally, delete the gridlines. Thus, the Box and Whisker Plot are ready for demonstration. The following figure is our ultimate output.
Conclusion
Henceforth, you will be able to fix if Box and Whisker Plot is Not Showing in Excel by following the above-described steps. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
