Cellwise Start free

Cleanup function

Excel NA function

NA takes no arguments and returns the #N/A error, which means "value not available". You use it on purpose: a line chart does not plot a point for #N/A, while an empty-looking "" counts as zero and drags the line to the floor. It is also a clear marker for data that has not arrived yet.

=NA()

Totals and counts over #N/A placeholders

One #N/A in a range makes SUM, AVERAGE and MAX return #N/A too. In the sample sheet below, MAX(C2:C7) would fail. AGGREGATE skips errors when its second argument is 6: =AGGREGATE(4,6,C2:C7) is the largest running total, =AGGREGATE(9,6,C2:C7) a sum and =AGGREGATE(1,6,C2:C7) an average.

To count how many weeks are still waiting, count the placeholders with =COUNTIF(C2:C7,"#N/A") or =SUMPRODUCT(--ISNA(C2:C7)).

NA examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1WeekSalesRunning total
2Wk 142004200
3Wk 238508050
4Wk 3510013150
5Wk 4467517825
6Wk 5#N/A
7Wk 6#N/A
  1. =IF(B5="",NA(),SUM($B$2:B5))

    Result: 17825

    Week 4 has sales, so the formula returns the running total: 17,825.

  2. =IF(B6="",NA(),SUM($B$2:B6))

    Result: #N/A

    Week 5 has no sales yet, so the running total is #N/A instead of repeating 17,825. On a line chart, the line simply stops at week 4.

  3. =IF(B2:B7="",NA(),B2:B7)

    Result: spills 6 rows × 1 column

    4200
    3850
    5100
    4675
    #N/A
    #N/A

    In Excel 365 and 2021 one formula builds the whole chart-ready column: four weeks of sales, then #N/A for the two weeks still to come.

  4. =IFNA(IF(B6="",NA(),SUM($B$2:B6)),"Not in yet")

    Result: Not in yet

    For a printed report, IFNA swaps the placeholder for a label. Keep the plain #N/A version in the column the chart uses.

Reference: Empty text vs #N/A

How to use NA

  1. Decide which cells should be "not available" rather than zero.
  2. Return NA() for those cases, for example =IF(B2="",NA(),B2).
  3. Point the chart at that column; Excel skips the #N/A points.
  4. Use AGGREGATE or IFNA where a total or a printout must ignore the placeholders.

Common NA mistakes

Using "" for missing chart data

A formula that returns "" is text, and a line chart plots it as zero. Return NA() instead so the point is not plotted.

Totals turning into #N/A

SUM, AVERAGE and MAX return #N/A if the range holds one. Use AGGREGATE(9,6,range) for a sum or AGGREGATE(1,6,range) for an average that skips errors.

Typing NA without brackets

NA needs empty brackets: =NA(). Without them Excel looks for a name called NA and shows #NAME?.

NA FAQ

How do I stop a chart from showing zeros for missing data?

Make the formula return NA() instead of 0 or "" when the data is missing. Excel does not plot #N/A points, so the line ends where the data ends.

How do I hide #N/A in Excel?

Use IFNA(value,"") to show an empty-looking cell, or a conditional formatting rule with ISNA that sets the font color to match the background.

What does #N/A mean in Excel?

Value not available. Lookups return it when they find no match, and NA() returns it on purpose.

Guides that use NA

Related functions

Updated