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()
| Argument | Required? | What it is |
|---|
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | =IF(B2="",NA(),B2) | 120 | A 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) | 128 | The same formula filled down |
| E2 | =SUM(IFNA(C2:C4,0)) | 248 | IFNA 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
| Month | Sales | For chart | Total, ignoring gaps | |
| Jan | 120 | 120 | 248 | |
| Feb | #N/A | |||
| Mar | 128 | 128 |
Practice NA
Graded instantly, then re-checked on changed data.
Chart-ready sales
C2, return the sales from B2, or #N/A (using NA()) when B2 is blank. Fill down to C7.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Totals show #N/A | Any 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 text | The 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 read | Readers 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 .