How to Remove #N/A Change to 0 in an Excel Spreadsheet

By Stephanie Ellen

A Microsoft Excel spreadsheet will return an #NA error when it can't make sense of the input you've given it. For example, if a blank cell is right in the middle of your data, or if a function isn't returning an expected value, the function will error out and return a #NA instead of the zero you may be expecting. To correct the error, use the NA() function in place of any empty cells that might be included in a formula or check your worksheet for bad inputs.

The NA() Function

Step 1

Locate an empty cell on your worksheet.

Step 2

Enter the following function into the empty cell: NA()Make sure to include the parentheses.

Step 3

Repeat Steps 1 and 2 for each empty cell in the spreadsheet.

Checking for Bad Inputs

Step 1

Locate the first function in your worksheet.

Step 2

Check the inputs for the function. For example, if you have the following SUM function:=SUM(A1:A10)then check cells A1 through A10, making sure the inputs are valid.

Step 3

Repeat steps 1 and 2 for all functions on your worksheet.

Tips & Warnings

  • #NA instead of 0 is a common error in HLOOKUP, LOOKUP, MATCH and VLOOKUP functions. Make sure to check these first if they are in your worksheet.
  • Microsoft provides a list of Excel functions on its "Help and How to" portion of its website.