What's the difference between NA() and leaving a cell blank?

NA() explicitly marks a cell as containing unavailable data, while blank means the data either doesn't exist or hasn't been entered yet. NA() forces formulas to handle the missing value rather than treating it as zero, and makes data gaps visible in reports. Use NA() when data should exist but is genuinely missing.

Can I use NA() in Google Sheets?

Yes, NA() works identically in Google Sheets and Excel, returning #N/A in both. You can catch it with IFNA() or IFERROR() the same way, and it propagates through formulas consistently across both platforms.

How do I sum or average numbers when cells contain #N/A from NA()?

Use the AGGREGATE function, which can skip errors: =AGGREGATE(9, 6, F2:F4) for SUM or =AGGREGATE(1, 6, F2:F4) for AVERAGE. The 6 tells AGGREGATE to ignore error values. Alternatively, prevent #N/A from appearing by wrapping NA() in IFERROR() with a default value.

When should I use NA() instead of IF or blank cells?

Use NA() inside IF or IFERROR to mark when expected data is unavailable: =IF(ISBLANK(F2), NA(), F2) returns #N/A only if CSAT is missing, while =IFERROR(VLOOKUP(...), NA()) uses NA() as the error handler. Both approaches make data gaps explicit, forcing conscious handling instead of hiding them.

NA function

Returns the #N/A error value, used to explicitly mark cells or formulas with data that is unavailable or unknown.

=NA()

Generate a NA formula

Describe what you need. The generator will reach for NA where NA is the right tool, and tell you when it is not.

Arguments

Argument	Required	Description

Returns

Returns the #N/A error, a single value that propagates through dependent formulas unless handled with error-catching functions.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Flag missing customer satisfaction ratings

Ticket ID	Priority	CSAT
T-001	High	5
T-002	Medium	4
T-003	Low

=IF(ISBLANK(F2), NA(), F2)

Result: #N/A

For ticket T-003 with no CSAT score, NA() explicitly marks this as unavailable data rather than leaving the cell blank. This makes data quality issues visible and prevents calculations from incorrectly treating blank cells as zero values.

2. Mark unresolved support tickets

Ticket ID	Opened	Closed
T-001	2026-09-01	2026-09-02
T-002	2026-09-02	2026-09-05
T-003	2026-09-03

=IF(ISBLANK(D2), NA(), "Resolved")

Result: #N/A

Ticket T-003 has no close date and remains open. Returning NA() instead of blank or "Open" makes it explicit that critical resolution data is missing, which impacts SLA calculations and prevents misleading reporting metrics.

3. Catch missing agents in quality score lookup

Ticket ID	Agent
T-001	Alice
T-002	Unknown
T-003	Charlie

=IFERROR(VLOOKUP(B2, {"Alice",4.8; "Bob",4.2; "Charlie",4.5}, 2, FALSE), NA())

Result: #N/A

Agent "Unknown" is not found in the quality scores table, causing VLOOKUP to error. IFERROR returns NA() instead of a generic error message, explicitly indicating the lookup failed because the agent record is missing from the database.

Common errors

Which NA error are you seeing?

Error	Why it happens	How to fix it
#N/A	NA() returns #N/A, which displays in the cell and breaks downstream calculations if not caught with IFERROR or IFNA.	Wrap NA() in IFERROR() to provide a fallback value: =IFERROR(IF(ISBLANK(F2), NA(), F2), "No data") to display "No data" instead of #N/A.
#N/A	#N/A from NA() propagates to dependent formulas; if F2 contains NA(), then =F2+0 or =TEXT(F2, "0.0") also return #N/A.	Handle NA() at its source before it affects calculations: =IFERROR(IF(ISBLANK(F2), NA(), F2), 0) or use AGGREGATE(9, 6, F2:F4) which skips errors.
#N/A	Using NA() in a range breaks aggregate functions; =AVERAGE(F2:F4) returns #N/A if any cell contains #N/A from NA().	Use AGGREGATE function to ignore errors: =AGGREGATE(1, 6, F2:F4) for average, or =AGGREGATE(9, 6, F2:F4) for sum (6 = ignore errors).

Tips and when to use something else

Use ISNA() to test if a cell contains #N/A before using its value—this lets you detect and handle NA() results in downstream formulas without breaking calculations.
IFNA() is more specific than IFERROR(): use IFNA() to handle only #N/A errors and let other errors (like #VALUE!) propagate for easier debugging.
Don't use NA() for blanks or optional data—use it only when data should exist but is genuinely missing or unknown, making gaps in your data explicit.
NA() is ideal for quality control: it's more informative than blank cells, forces formulas to handle missing values, and makes data gaps immediately visible in reports.

Related functions

ISNAISNA detects whether a value is the #N/A error, typically returned when a lookup function fails to find a match.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.IFNAIFNA returns the result of a formula unless it evaluates to #N/A, in which case it returns a specified alternate value.ISERRORISERROR returns TRUE if a value is an error like #N/A or #REF!, FALSE otherwise, enabling error detection and handling.ERROR.TYPEERROR.TYPE returns a number 1–7 identifying the type of error, letting you handle different errors individually or display a helpful message to users.

Frequently asked questions

What's the difference between NA() and leaving a cell blank?: NA() explicitly marks a cell as containing unavailable data, while blank means the data either doesn't exist or hasn't been entered yet. NA() forces formulas to handle the missing value rather than treating it as zero, and makes data gaps visible in reports. Use NA() when data should exist but is genuinely missing.
Can I use NA() in Google Sheets?: Yes, NA() works identically in Google Sheets and Excel, returning #N/A in both. You can catch it with IFNA() or IFERROR() the same way, and it propagates through formulas consistently across both platforms.
How do I sum or average numbers when cells contain #N/A from NA()?: Use the AGGREGATE function, which can skip errors: =AGGREGATE(9, 6, F2:F4) for SUM or =AGGREGATE(1, 6, F2:F4) for AVERAGE. The 6 tells AGGREGATE to ignore error values. Alternatively, prevent #N/A from appearing by wrapping NA() in IFERROR() with a default value.
When should I use NA() instead of IF or blank cells?: Use NA() inside IF or IFERROR to mark when expected data is unavailable: =IF(ISBLANK(F2), NA(), F2) returns #N/A only if CSAT is missing, while =IFERROR(VLOOKUP(...), NA()) uses NA() as the error handler. Both approaches make data gaps explicit, forcing conscious handling instead of hiding them.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17