NA Function

Returns the #N/A error on purpose.

Category

Information

Available in

All versions, Microsoft 365

Returns

The #N/A error

Want to practice NA in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

NA() takes no arguments and simply returns #N/A — Excel's “no value available” error.

That sounds odd, but it's useful: charts skip #N/A points instead of plotting them as zero, and an explicit #N/A shows that data is missing rather than zero.

Syntax

=NA() Explain

This function takes no arguments — the brackets stay empty.

Examples

Example 1 — Gaps in a chart instead of zeros

February hasn't been reported yet. Returning 0 would make the line dip to zero; #N/A leaves the point out.

C2fx=IF(B2="",NA(),B2)
ABC
1MonthReportedChart value
2Jan120120
3Feb#N/A
4Mar135135
=IF(B2="",NA(),B2) Explain

Common mistakes

  • Totals turn into #N/A. SUM of a range containing #N/A is #N/A. Use =AGGREGATE(9,6,C2:C13) to add up while ignoring errors.

Notes

  • You can also type #N/A straight into a cell.
  • Hide the #N/A text with Conditional Formatting (=ISNA(C2), white font) if it looks untidy.
  • Related functions: ISNA, IFNA, AGGREGATE.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.