# NA function in Excel

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

Source: https://spreadsheetpractice.com/functions/na · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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)
- **Available in:** Every current version
- **Category:** [Information functions](https://spreadsheetpractice.com/functions/category/info)
- **Returns:** The #N/A error value.
- **Google Sheets:** Available in Google Sheets.

## What it does

NA() returns the #N/A error on purpose, to mark a value as not available. 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.

## Syntax

`=NA()`



## Examples

Each result was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Month | Sales | For chart |  | Total, ignoring gaps |
| 2 | Jan | 120 | 120 |  | 248 |
| 3 | Feb |  | #N/A |  |  |
| 4 | Mar | 128 | 128 |  |  |

## 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.

## Related functions

- [ISNA](https://spreadsheetpractice.com/functions/isna): ISNA returns TRUE only for the #N/A error, the one MATCH, VLOOKUP and XLOOKUP return when a value isn't found.
- [IFNA](https://spreadsheetpractice.com/functions/ifna): IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing.
- [IFERROR](https://spreadsheetpractice.com/functions/iferror): IFERROR returns a value you choose when a formula results in an error, and the formula's normal result otherwise.

## 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.

## Practice

A graded NA exercise ("Chart-ready sales") is on [the NA page](https://spreadsheetpractice.com/functions/na). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
