Excel IFNA Function

Excel IFNA Function

The IFNA in Excel is used to remove the #N/A type of errors in excel values cells with our choice of value. IFNA function is from Excel 2013 onwards, so it is not available in earlier versions of Excel.




Value(required)- This parameter represents the first cell value, which is checked if there is an error if the cell.

Value_if_error( optional)- This parameter represents the error value, which will be replaced by this value If in the cell there is a value of #N/A.


The function returns the specified value if initially supplied the argument or formula returns an error, otherwise returns the result of the formula supplied in the first argument.

Example 1

Objective: Using IFNA function, represent the Col A with #N/A value as 9999.

Col A With IFERROR &Formula
1 1  =IFNA(A19,9999)
#N/A 9999  =IFNA(A20,9999)

Example 2

Objective: Find the monthly salary of each employee using data in Annual Salary, and if #N/A appears, then change it to ERROR IN SALARY.

Emp Name Designation Annual Salary Monthly Salary Formula Used
Ashish AVP #N/A -  =IFNA(A20,"-")
Sohrab Analyst £50,000 4167  =IFNA(A21,"-")
Paul Engineer #N/A -  =IFNA(A22,"-")
Farouk Analyst £40,000 3333  =IFNA(A23,"-")