Why is my box and whisker plot not calculating Q1 or Q3? i.e. why hasn't Excel calculated a Box?

Question

I have a population of 27k unique values ranging from 0 to 300 across a few categories that I'm trying to develop box and whisker plots for, however Excel doesn't calculate a Box, rather it plots the various points. How can I correct this?

About the data - there are a large number of instances of 0 (36% of the dataset). Would this have an impact? I'd rather not remove those 0s.

Answer 1

Hello Andrea,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

In Excel, a box in a box and whisker plot represents the interquartile range (IQR), which is the range between the first quartile (Q1) and third quartile (Q3). When Excel fails to display the box, it's often because there isn't enough variation in the data between Q1 and Q3 to display it visually. In your case, having a large number of zeros (36% of the dataset) could certainly impact this, as it may lead to Q1, the median, and Q3 all being zero or very close to zero.

Here's what you can do to correct the issue or understand it better:

1. Check the Calculation: Ensure Excel is calculating the quartiles correctly. You can do this by using the =QUARTILE. INC() or =QUARTILE. EXC() functions on your dataset to see what the actual quartile values are.
2. Adjust the Scale: If the quartiles are there but too close together relative to the scale of the plot, you may need to adjust the axis scale to see the box. This is less likely to be the issue if the box is not showing up at all, but it's worth checking.
3. Consider Data Transformation: If many values are clustered at one point (like zero), it might be helpful to use a transformation on your data, such as a log transform, to spread out the values for the purpose of visualization. However, be cautious as this changes the interpretation of the data.
4. Check for Outliers: Excel might be considering many of your data points as outliers, which could be plotted as individual points rather than part of the box. This can happen when the data is highly skewed due to the large number of zeros.
5. Data Cleaning: Double-check your data to ensure there are no errors in data entry or calculation that could be affecting the quartiles.

If you're certain that the data is correct and the quartiles are meaningful, but Excel is still not showing the box, you may need to explore alternative ways of visualizing your data that can handle large clusters of similar values, such as a histogram or a density plot, which might require additional tools outside of Excel.

I hope this helps.

Best Regards, Ibhadighi