How to Remove #N/A Change to 0 in an Excel Spreadsheet
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
Locate an empty cell on your worksheet.
Enter the following function into the empty cell: NA()Make sure to include the parentheses.
Repeat Steps 1 and 2 for each empty cell in the spreadsheet.
Checking for Bad Inputs
Locate the first function in your worksheet.
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.
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.