Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

[Fixed!] Box and Whisker Plot Not Showing in Excel

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.

box and whisker plot excel not showing


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.

Set Table for 5 Statistics Numbers


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.

Input Formulas

  • 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.

Create New Table to Find Differences

  • 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.

Insert 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.

Modify Chart to Add Whisker Plot to Box

  • 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)

Embed Average Marker in Box and Whisker Plot

  • 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.

Read More: How to Create Box and Whisker Plot in Excel with Multiple Series


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.


Related Articles

Aung

Aung

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo