Create a chart in Excel that recognizes #N/A or blank cells

[Maha Alam]

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.

 Thanks for reading!

[emmanuel chaslin]

Hey thanks for this.

However, I dont know why, in my Excel file (Excel 2010) I cannot prevent the graph from showing blanks or #N/A as 0 values.

The "show #N/A as empty" doesnt option doesnt appear on the data selection window, and I can try any option ("gaps", "zero", "connect data.."), the graph just appears the same, with empty values as 0.

How can I solve that ?

Thanks in advance.

[Maha Alam]

Hi Emmanuel, thanks for writing! The situation you're describing is the expected backwards compatibility behavior. The update is available to O365 subcribers; find out how to get the latest updates here.