Excel Whisker Plot Empty Cells Impossible to Ignore

Question

Hi,

is there an option to ignore empty cells in a whisker plot? It creates an annoying gap in the data. All other plots can do this with either using #N/A or in the plot data source menu.

Answer 1

Hi Thomas,

I'm Jennifer, Independent Advisor here and a Microsoft user like you. I'm happy to work with you on this issue. Let me see if I can help you with this.

Please try to refer on below resolution.

https://support.microsoft.com/en-us/help/884406...

Hope this helps you. :)

*Note: Please confirm if this resolved your issue if not don't hesitate to ask if further assistance is needed.

Stay safe & Smile :)

Thank you,
Jennifer J.

Answer 2

Hi Jennifer,

thank you very much for your reply! Your solution refers to using #N/A or alternatively the data source menu - both options I alread noted to not be working on whisker plots in my original post. For a Whisker Plot the "Hidden and Empty Cells" is gray and inactive (sorry for the german version in my screenshot). When using #N/A it at least does not plot a zero as it does for a string... But the empty row is still there in the plot.

Whisker plots seem to be fundamentally differnent from typical Excel line or scatter plots. They also don't allow manual positioning of the legend for example. Also no drag and drop to adjust the data source.

So far I don't see a way to remove the empty rows in the plot, except hiding all empty cells with a filter. But this only partially takes care of the problem, as if there is empty cells in the data range at the bottom of the sheet those cells will not be hidden. So the empty row in the plot just moves to the end. And filters don't auto update unless with macros which is also not satisfying. 

All the best,

Thomas

*Note: issue not resolved

Answer 3

Hi Thomas,

Apology if this was not resolved your issue.

I will release this so that other experts might help you.

Thank you,

Jennifer J.

Answer 4

Hi

let's try this,

Select Data > Select Data Source > Hidden and Empty Cells

-Options will show up there with Gaps, Zero and Connect data points with line. The default position is Gaps.

Regards,

Louwie