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

You need to right-click your plot first to access that option

Answer 2

Hi Louwie,

thank you very much for your reply. Did you replicate this on a whisker plot? I am aware how to access the Data Source menu, and that this option works on most graphs. Actually I included a pic of this popup in my screenshots image indicating that the "hidden and empty cells" is grayed out and inactive for a whisker plot.

Could you verify that your solution works on this new whisker plot type which is only available since the 2016 version of office and behaves fundamentally different than other plots in many ways?

All the best,

Thomas

Answer 3

Hi,

in this case, as we have no access to this option for whisker charts. We can only sort range with data in it and manually select the data for this chart to avoid inclusion of blanks, zeros, and #N/A.

Regards

Answer 4

Hi Louwie,

thank you very much for your reply! I would consider this a bug then, as I do not see why anyone would want to include empty cells or #N/A as an own category in a whisker chart.

Manual sorting is not an option for me, as I filter data from another sheet with an if condition to remove the rows I want to exclude from the plot. This also means the number of entrys (rows) is different each time depending on my if condition and I would have to change the plot data source each time to match the number of rows that fulfill my if condition.

All the best,

Thomas

Answer 5

You're welcome