#N/A No Longer a Waste of Space

Every now and then an improvement comes through in Excel that is truly newsworthy.  Most features and functions we put in the Thought section in due course – but this one is different.  Loads of users want this.  I am talking about gaps in charts.

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.  Microsoft has finally added the functionality for #N/A to be rendered as a blank:

Let me back up a little. By default, data that is hidden in rows and columns in the worksheet is not displayed in a chart, and empty cells or null values are displayed as gaps. For most chart types, you can display the hidden data in a chart.

For line, scatter, and radar chart types, you can also change the way that empty cells and cells that display the #N/A error are displayed in the chart. Instead of displaying empty cells as gaps, you can display empty cells as zero values or you can span the gaps with a line. For #N/A values, you can choose to display those as an empty cell or connect data points with a line. The following examples show Excel’s behaviour with each of these options.

Options for showing empty cells

OK, so this has been around for a while:

Options for cells with #N/A

But this hasn’t!

To change the way that empty cells, null (#N/A) values and hidden rows and columns are displayed in a chart:

• Click the chart you want to change
• Go to Chart Tools on the Ribbon, then on the ‘Design’ tab, in the ‘Data’ group, click ‘Select Data’:

• Click Hidden and Empty Cells:

• In the ‘Show empty cells as:’ options box, click ‘Gaps’, ‘Zero’, or ‘Connect data points with line’ as required
• It should be noted that on a scatter chart that displays only markers (without connecting lines), you can display empty cells as gaps or zero only — you cannot connect the data points with a line
• Click the Show #N/A as an empty cell’ option if you don’t want Excel to plot those points.

This feature is only available if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office. In order to maintain backwards compatibility with other versions of Excel, this feature is off by default.