How to Mark Missing Data with NA in Excel

Use NA() to mark missing or invalid readings as #N/A so charts leave a gap instead of plunging to zero.

You will learn to

• Return #N/A for a missing reading
• Mark impossible values as #N/A too
• Average a series that contains #N/A

How it works

NA() takes no arguments and returns the #N/A error. That sounds odd, but it is the standard way to say "no value here". Line charts skip #N/A points instead of plotting them as zero, and lookups or formulas that depend on the cell clearly show that data is missing rather than quietly using 0.

A typical pattern is =IF(B2="",NA(),B2). The trade-off is that SUM and AVERAGE return #N/A when a range contains it, so summarise such a column with AGGREGATE(1,6,range), which averages while ignoring errors.

=IF(B2="",NA(),B2)