Do not chart null values
Chart defaults to plotting null or ' values as zero. The default should be to not plot null values
Thank you for your suggestion! While there are currently no plans for changing the default behavior, you can modify your settings to not plot null values in your chart. You can do this by navigating to the Chart Design ribbon and opening the Select Data dialogue. From there, select “Hidden and Empty Cells” and then choose to show empty cells as gaps.
Thank you again,
Michael Daughety commented
As Kenneth Barber said, charts can be set to not show #NA values. I've had to do this for years for leadership reporting.
this would be v useful
I can do this with my Office 365 version at home but not the version recently deployed by my employer.
Warren Wilson commented
I had the option to do this in my previous version of Excel but it doesn't seem to be there in Excel 2016. This is not a good situation. For some of my work, which involves plotting data that may or may not be collected at fixed time intervals, it is essential that nothing be plotted (i.e. don't even plot a zero) on occasions when no information was recorded.
For line charts, I always want null (empty cells) to not display at all (for example giving a break in a line) or to allow the line to interpolate through the blank (i.e. not breaking the line but having no 'data point' in that position).
I have never, ever wanted empty's to display as zero in a line chart - yet this is the excel way - doh! A zero works well for a bar, column or pie chart but not for all chart types. Options here would be good.
There are workarounds to get the behaviour I want (putting errors into my blank cells with =na() or converting my line chart to an X-Y scatter plot. The workarounds sometimes create other issues - they are not ideal.
Ryan MacGregor commented
I received the same MSFT email message as Joseph Byrne yesterday and, if I understand Joseph's concerns correctly, I had similar concerns when I read the email. Not exactly clear from this email what MSFT's intentions are with respect to NULL(). Regardless, MSFT, please do not implement any charting changes related to null values until you have consulted with Jon Peltier (charting guru and Excel MVP), who is among the most vocal supporters of NULL().
Joseph Byrne commented
I received from MS indicating they're considering implementing "do not chart null" but not NULL() owing to the foreseen practical use of NULL() being to avoid charting nulls. I see this as shortsighted. NULL() can achieve the "do not chart" goal as well as other goals, including some not foreseen. However, "do not chart" achieves only a single goal.
Charts plot any text, including "" (except NA()) as zero. I think that chart should plot these as gaps instead of zero.
Charley Kyd commented
The easiest way to implement this would be to introduce a BLANK() worksheet function that would cause Excel to treat the cell as blank in charts.
Kenneth Barber commented
The workaround is to fill the "nulls" with #N/A by using the NA function.
Jon Peltier commented
Charts do not plot true nulls, that is, blank cells. Bet there is no way to make a formula return a null value, only something that looks blank, like "".
Go vote for "Give us a proper NULL() worksheet function."