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

**Table of Contents**hide

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