Spreadsheet Practice

NA function

Quick answer

NA() returns the #N/A error on purpose, to mark a value as not available.

Syntax
=NA()
Most common use
=IF(B2="",NA(),B2) returns 120 (A month with data passes through unchanged)

Its main use is chart data: line and scatter charts skip #N/A points instead of plotting them as zero, so months with no data don't show as a misleading drop to zero.

Available in
Every current version
Category
Information functions
Returns
The #N/A error value.
Google Sheets
Available in Google Sheets.

Syntax

=NA()

ArgumentRequired?What it is

Examples

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

CellFormulaResultWhat it shows
C2=IF(B2="",NA(),B2)120A month with data passes through unchanged
C3=IF(B3="",NA(),B3)#N/A#N/A for the missing month, so a line chart skips it
C4=IF(B4="",NA(),B4)128The same formula filled down
E2=SUM(IFNA(C2:C4,0))248IFNA turns the #N/A into 0 so the total still works

Try it live

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table
MonthSalesFor chartTotal, ignoring gaps
Jan120120248
Feb#N/A
Mar128128

Practice NA

Graded instantly, then re-checked on changed data.

Chart-ready sales

In C2, return the sales from B2, or #N/A (using NA()) when B2 is blank. Fill down to C7.

Common mistakes

What you seeWhy, and how to fix it
Totals show #N/AAny SUM or AVERAGE that includes an #N/A returns #N/A. Use =SUM(IFNA(C2:C13,0)), or AGGREGATE, which can ignore errors.
Typing N/A as textThe text "N/A" is not the error. Charts plot text as zero, so use NA() or type #N/A exactly.
Left in a report people readReaders see #N/A as broken. Use NA() in chart data and a separate display column, with IFNA, for anything people read.

Tips

  • Keep NA() values in a hidden helper column that feeds the chart, and show a friendlier column in the table.

Questions

What does NA() do in Excel?

It returns the #N/A error on purpose, to mark a value as not available. Line and scatter charts skip #N/A points instead of plotting them as zero.

How do I stop a line chart dropping to zero for missing data?

Return NA() instead of 0 or "" for missing months: =IF(B2="",NA(),B2). The chart leaves a gap or joins the points on either side.

In other languages

NA is NOD in Spanish Excel and NV in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.

Updated Checked by running: every formula on this page was run by our formula engine on .