The #N/A! Is one of the most common errors in Excel. But it helps users to identify missing values. But when you want to make a presentable spreadsheet it’s good to deal with this error for a good user experience.
There are two ways you can fix the #N/A! Error.
- Hide the error from the user
- Remove the error
In this article, I will guide you in various ways to solve #N/A! Error in Excel.
What is #N/A Error in Excel?
In Excel, the #N/A error stands for “Not Available” error. It appears when a formula or function cannot find a specified value. This can occur when using lookup functions like LOOKUP, VLOOKUP, HLOOKUP, or INDEX-MATCH, and it indicates that the value being searched for is not found within the dataset.
Ways to fix #N/A error in Excel!
There are different ways that you can follow to solve this issue. These errors involve displaying an alternative message instead of the error, checking for extra spaces, and whether numbers are formatted as text.
Return a different message rather than the error
Returning a different message rather than the error can be very useful. For example, if the error was returned because it did not find a specific product, then you can return “out of stock” which is more understandable and clear feedback.
IFerror
1. Identify the cell or formula causing the #N/A error in Excel.
2. Use the IFERROR function to handle errors and replace them with a custom message.
3. Example formula: =IFERROR(original_formula, “Custom Message”)
For example, in the image below we have used the following formula to replace the #N/A error with a custom-friendly message.
=IFERROR(VLOOKUP(E6,B3:C11,2,0), “Team Not Listed”)
4. Replace “original_formula” with the formula that may result in the #N/A error.
5. “Custom Message” is the message you want to display instead of the error.
IFNA
1. Locate the cell or formula that might generate the #N/A error.
2. Utilize the IFNA function to handle specifically #N/A errors.
3. Example formula: =IFNA(original_formula, “Custom Message for #N/A”)
4. Replace “original_formula” with the actual formula you are using.
Take a look at the image example and see how I have replaced the error with a custom message
=IFNA(VLOOKUP(E6,B3:C11,2,0), “Team Not Available”)
5. “Custom Message for #N/A” is the message you want to display if the formula returns #N/A.
Return no message
1. Identify the cell or formula causing the #N/A error.
2. Use the IFERROR function: =IFERROR(original_formula, “”)
3. Replace “original_formula” with the formula that may result in #N/A.
4. The formula will return an empty string (“”) instead of an error message when encountering #N/A.
The formula used in this example below is:
=IFERROR(VLOOKUP(E6,B3:C11,2,0), “”) but you can also use IFNA to achieve same results
Download Practice workbook
Check if the data contains extra spaces
1. Select the column or range of cells where you suspect extra spaces may exist.
2. Use the TRIM function to remove leading, trailing, and extra spaces.
Example formula: =TRIM(A1)
3. Replace “A1” with the reference to the cell or range you want to clean.
4. The TRIM function will return the text without any extra spaces
Read More: Common Excel Errors
Check if numbers are formatted as Text
If you are looking up numbers but they are formatted as text, then you will definitely face this error.
1. Choose the column or range where numbers might be incorrectly formatted as text.
2. Use the VALUE function to convert text-formatted numbers to actual numeric values.
Example formula: =VALUE(A1)
3. Replace “A1” with the reference to the cell or range containing the text-formatted number.
4. The VALUE function will convert the text to a numerical value.
Final Thoughts
That’s how to remove or hide the #N/A! in Excel. For a presentable spreadsheet, it’s better to display a custom message that explains in detail what exactly is happening.
