Create a chart in Excel that recognizes #N/A or blank cells
Recently, we rleased a big UserVoice request that customers have been asking for over the last several years, #N/A being rendered as blank in charts. Thank you to those who voted and have been patiently waiting. These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. There's quite a lot of excitement around the feature! The new functionality proves to be the greatest value for line and scatter charts as they're the most commonly chart used in Excel documents.
What problem did we fix?
The #N/A error is produced when a formula can't find what it's been asked to look for. It is also produced from the function =NA() which indicates no value is available. With the following dataset, #N/A is currently shown in a line chart as a connecting line. We've added the functionality for #N/A to be rendered as a blank.
How can I access the feature?
In the August update, you should see the option to control the behavior for #N/A: Chart Tools> Design tab > Select Data button > Hidden and Empty Cells button.