NA function
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.
- 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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.