QuickTrick: Remove Unwanted #NAs From Spreadsheets

Problem

The #NA is an indication that a formula can’t find something you told it to use in order to return the result you requested.

The #NA is not inherently harmful and it disappears as soon as the cell formula has the information needed to fulfill its mission. There may actually be some situations where you want to see the #NA to prompt you to correct an error. However, a spreadsheet full of needless #NAs can ruin the aesthetics of your hard work, and they can also potentially cause you to overlook errors that need correcting.

Solution

The solution is easy, simply wrap the formula producing or expected to produce the #NA results in an “IF” function testing for #NA regarding the formula in question. While each spreadsheet application may have other solutions, this solution will probably work in any of them.

Let’s look at an example:

In the spreadsheet above, a lookup table is created for use with the VLOOKUP function. As you can see in the tests that follow the lookup table, a normal VLOOKUP formula returns “slices” when “Orange” is used as the criteria, but returns “#NA” when “Pear” is used as the criteria since “Pear” does not exist in the lookup table.

(The #NA would also result if there were not yet any data in the lookup table. As you can imagine, a spreadsheet full of VLOOKUP formulas referencing a lookup table with no data or a developing lookup table with incomplete data would result in a spreadsheet full of #NAs.)

When, on the other hand, the VLOOKUP formula is wrapped in the IF function testing for ISNA, we can control the result to display anything we want. In our example, the result displays nothing at all instead of #NA.

Final Comments

Current versions of Microsoft Excel, LibreOffice Calc, and Google Sheets include an IFNA function that combines the “IF(ISNA(“ approach used above. I have chosen the latter approach because the IFNA function may not be available in all spreadsheets applications or in older versions of certain spreadsheet applications. For instance, IFNA is not available in Microsoft Excel prior to Excel 2013.

While the issue of older versions may be a problem for users of Microsoft Excel versions prior to 2013, it is not a concern for Google Sheets users since it is a server-based software application, so you are always using the most current version. This issue is also of little concern for LibreOffice Calc users since LibreOffice is free, so most users will be using the most current version. Regardless, the “IF(ISNA(“ approach just discussed provides greater compatibility across spreadsheet applications and across application versions than the IFNA approach.

I hope you enjoyed this QuickTrick article and found it useful. If you have a question about spreadsheet software such as Microsoft Excel, LibreOffice Calc, or Google Sheets, leave a Comment. Your question could become the subject for a new QuickTrick.