📎 This article includes 1 downloadable practice file ↓
In this article
NA produces #N/A deliberately. Charts skip #N/A points instead of plotting them as zero.
Syntax
=NA()Examples
Example 1
=IF(C2="", NA(), C2)Future months stay off the line chart instead of dropping to zero.
NA in practice
Where you will use it
- Leave gaps in line charts instead of zeros
- Mark missing data deliberately
- Force lookups to fail visibly
Worked example
| Sales | Formula | Result |
|---|---|---|
| 1200 | =IF(A2="",NA(),A2) | 1200 |
| (blank) | =IF(A3="",NA(),A3) | #N/A |
| 1500 | =IF(A4="",NA(),A4) | 1500 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Using 0 for missing | Zeros drop chart lines to the axis |
| Summing columns with #N/A | Use AGGREGATE(9,6,range) |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Information (IS…) functions15 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 34 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong