At a glance
Excel has a number of IS functions. These are functions that are typically used with an IF function to identify certain types of values in cells. The cell values may be entered, or the result of a formula. The IF function can do one thing if a cell is a certain type and something else if it isn’t.
Worked example
Blank cells can be problematic in Excel, because blank cells cause an error in a VLOOKUP function.
Figure 1 shows the formula progression from row 2 to row 5, to develop a robust VLOOKUP formula that handles all eventualities. Rows 7 and 8 have an alternative final formula.
Column G shows the formulas in column F. Cell F2 has the standalone VLOOKUP function, which works correctly as long as the entry in cell D2 is valid.
Unfortunately, a number of things can cause the VLOOKUP to display an error. To handle these issues, you can add extra levels to the original VLOOKUP function.
ISBLANK function
The first possibility we need to cover is an empty or blank cell. The ISBLANK function returns TRUE if a cell is blank or empty.
Adding the IF function in row 3 enables us to see if cell D3 is blank. If D3 is blank, a zero is returned. If not, the VLOOKUP is calculated.
Using IS functions with the IF function
Since IS functions return TRUE or FALSE, you can use them as the first argument in an IF function, as seen in cell F3.
=IF(ISBLANK(D3),0,VLOOKUP(D3,$A$2:$B$6,2,0))
You don’t have to use
=IF(ISBLANK(D3)=TRUE,0,VLOOKUP(D3,$A$2:$B$6,2,0))
The =TRUE is not required, because the ISBLANK function (like all IS functions) only returns TRUE or FALSE. This makes all the IS functions ideal for the first argument in an IF function.
IFNA function
The second issue that can occur is that the entry may be invalid, as per row 4. SA is not in the table, and it would return the #N/A error.
The IFNA function identifies #N/A errors and enables us to handle that specific error slightly differently to other errors by displaying a different error message. An #N/A error usually means that either the entry is invalid, or that the table needs to be updated to include the new entry.
IFERROR function
The final issue can be one of the many formula errors that Excel can generate. The IFERROR function enables you to handle all of Excel’s errors. Because the IFNA is inside the IFERROR, the #N/A error will be handled first. Any other errors will be handled by the IFERROR function.
The ISBLANK function helped us to initially identify if a cell is blank. The ISBLANK function does have a blind spot. In row 6, cell D6 has the following formula
=IF(D1=D2,1,"")
ISBLANK does not recognise a cell with a formula that returns a blank cell as a blank. A cell that contains a formula is not treated as blank by ISBLANK.
Rows 7 and 8 have an alternative solution that handles a blank cell, as well as a formula that returns a blank cell.
It is common in an IF function to display a blank cell using the double quotation marks when no calculation has been made. It is useful to be able to identify that type of blank cell using a formula as per cells F7 and F8.
Other IS functions
Figure 2 has a table with examples of ISBLANK and other IS functions. I have not included the ISERROR, ISNA or ISERR functions. The IFERROR and IFNA functions, used earlier are now the best way to handle Excel’s various formula errors. I have included those two functions in the table.
All the formulas in the table in Figure 2 refer to the entry on the same row in column A. For example, the formula in cell D2 is
=ISNUMBER(A2)
I have not changed the alignment format of the cells in column A. In general, left-aligned entries are always treated as text by Excel.
One important thing to note about all IS functions is that they ignore formula errors. Most functions will return an error if the cell(s) they refer to have an error, not the IS functions.
The ISBLANK function only returns TRUE when the cell is empty – see row 2.
If there is a formula that returns a blank cell, as in cell A3, then ISBLANK returns FALSE. Column H has an alternate solution for blanks, but it has its own issues with errors. The formula in H2 is
=A2=””
The two quotation marks used together signify a blank entry. This technique doesn’t handle formula errors in column A. See rows 13 and 14 in column H.
ISNUMBER function
This returns TRUE for numbers and dates. In Excel, dates are treated as numbers. Each date has an underlying whole number. In columns J and K, you can see some of the underlying numbers for the dates in rows 7 and 19.
If you need to find out if a cell could be numeric, like a text number or a text date, then column I has a formula that may help.
The formula in cell I2 is
=ISNUMBER(A2*1)
Multiplying an entry by 1 converts text numbers to real numbers. This also converts a text date (A8) into a real date. This is called “coercing”. If you multiply text by 1, it generates an error, but since ISNUMBER ignores errors, it still works as expected.
The only unusual entry in column I is row 2. Cell A2 is a blank cell. When you multiply a blank cell by 1 it returns a zero, which is a number and so the ISNUMBER returns TRUE.
ISTEXT function
If an entry is left-aligned, that is Excel’s way of showing you it is treating the entry as text. The formula in cell A3 returns text, but since it displays a blank cell, you can’t see it.
ISFORMULA function
Any cell entry that starts with the = sign is treated as a formula. Note that cell A12 is a formula, but its value would never change, because it does not refer to any other cells.
ISLOGICAL function
This function only returns TRUE for cells that contain TRUE or FALSE. Everything else returns FALSE, as you can see from the table in Figure 2.
IFERROR and IFNA functions
I have added these to the table as they replace and simplify the error handling that used to be done with ISERROR, ISERR and ISNA functions. The formulas used in those columns are
=IFERROR(A2,”Error found”)
=IFNA(A2,”NA found”)
Notice that the IFNA function only handles the #N/A error (row 20).
Cell A20 has the formula
=NA()
This function returns the #N/A error. It is typically used when creating data for charts. If you do not want to plot something on the chart, you can use the NA() to generate an error. In general, Excel will not plot #N/A errors on a chart.
The companion video will show some more applications of the IS functions.
Opposites attract
What if you need the opposite? Let’s say you need to know if a cell isn’t text. There are at least two options.
To display TRUE if a cell isn’t text, use
=NOT(ISTEXT(A2))
Or
=ISTEXT(A2)=FALSE
Both formulas return the same results.
The NOT function changes TRUE to FALSE and FALSE to TRUE. The NOT function can be confusing, so checking if the IS function equals FALSE in the second formula can be easier to understand.
The companion video and Excel file will go into more detail to demonstrate these techniques.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting. Questions can be sent to [email protected]
