Part of the free Module 5: Excel Formulas and Functions · Function 61 of 105 · Full Excel course
The Excel NA function returns the error value #N/A, which means “value not available”. It takes no arguments. NA in Excel is used deliberately: to mark cells where data is missing so that totals and lookups flag the gap instead of silently treating it as zero, and to create gaps in charts, because line and scatter charts skip #N/A points.
NA syntax
=NA()Arguments
| Argument | Required | Meaning |
|---|---|---|
| (none) | – | NA() accepts no arguments; the empty parentheses are required. |
Typing #N/A directly into a cell produces the same error value. The function form is clearer inside formulas and survives copy-paste and Find and Replace better.
Step-by-step example
=IF(A1="", NA(), A1)- Excel tests whether A1 is empty.
- If it is, the formula returns #N/A, a visible marker that the value is missing.
- Otherwise it returns the content of A1. A SUM over this column will now show #N/A until every input is filled, which prevents an incomplete total being reported as final.
Practical use cases
1. Create gaps in a line chart
=IF(B2=0, NA(), B2)Zero would plot as a dip to the axis; #N/A leaves a gap (or, with the “connect data points with line” option, bridges it).
2. Flag negative values as invalid
=IF(C2<0, NA(), C2)3. Force a placeholder that cannot be summed by accident
=NA()Put it in a budget cell awaiting a figure; every dependent total inherits #N/A until the real number arrives.
4. Standardise lookup failures
=IFERROR(VLOOKUP(D2, E:F, 2, FALSE), NA())Converts any error from the lookup (including #REF!) into #N/A so downstream IFNA or ISNA logic handles them uniformly. Use with care, because it also hides genuine faults.
5. Conditional formatting for missing data
=ISNA(A2)Colours every deliberate #N/A so reviewers can see what is outstanding.
Common mistakes and errors
- Passing an argument – NA(0) or NA(“x”) is invalid. The function takes none.
- #N/A spreading through totals – that is the intended behaviour. If you need totals to ignore the markers, use AGGREGATE(9, 6, range) or SUMIF(range, “<>#N/A”).
- Text “#N/A” – typing the characters inside quotes creates text, not an error. ISNA returns FALSE for it and charts plot it as zero.
- Column charts – #N/A hides a bar entirely; only line and scatter charts treat it as a gap.
- Old Excel array entry – none required; NA() works identically in every version.
Tips and best practices
- Use NA() for chart source data whenever a zero would misrepresent missing values.
- Mark outstanding inputs with NA() in templates so incomplete reports fail loudly rather than quietly.
- Style deliberate #N/A cells with conditional formatting (ISNA) so they read as “pending”, not “broken”.
- Sum around markers with AGGREGATE(9, 6, range) in totals that must still display.
- Prefer NA() to typing #N/A; the function is unambiguous in formulas and survives Find and Replace.
Related functions
- ISNA – detects the #N/A that NA() produces.
- IFNA and IFERROR – replace #N/A when you want to display something else.
- ERROR.TYPE – returns 7 for #N/A.
- IF – the function NA() usually sits inside.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
Does the NA function take any arguments?
No. Write it as =NA() with empty parentheses. Adding anything inside produces an error message.
Why would I deliberately create a #N/A error?
To mark missing data so totals cannot be mistaken for complete, and to make line charts show a gap instead of plotting zero for absent values.
How do I sum a range that contains NA() markers?
Use AGGREGATE(9, 6, range), which ignores errors, or SUMIF(range, “<>#N/A”). Plain SUM returns #N/A, which is often the point of using NA() in the first place.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.