IS function in Excel

Data Validation is useful but imperfect. It is still possible for users to end up with incorrect values in cells or for you to forget to apply Data Validation to an important input cell. Excel has a set of information functions designed to validate the data in a specific cell or set of cells and return TRUE if the cell contents are of a particular type and FALSE if not.

These functions are known as IS functions:

ISNUMBER

A simple example of IS Functions is ISNUMBER, which returns TRUE if the cell referred to is a number; otherwise it returns FALSE. Therefore:

=ISNUMBER(B2)

returns FALSE below because that cell contains text:

The function ISTEXT works the same way:

ISERROR()

Like ISNUMBER() and ISTEXT() introduced in the previous module, ISERROR is another type of IS function that checks a cell for a type of value, in this case any of Excel’s error values. It will return TRUE if the cell referenced contains #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!; otherwise it will return FALSE.

The following table contains two error values: #VALUE! (because cell B4 in the Cost column doesn’t contain a number) and #DIV/0! (because the quantity of 0 in C6 can’t be used to divide the cost). ISERROR() checks the calculated values in column D and marks those containing an error with TRUE:

ISBLANK

The ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE.

You can use an IS function to get information about a value before performing a calculation or other action with it. For example, you can use the ISERROR function in conjunction with the IF function to perform a different action if an error occurs:
=IF(ISERROR(A1), “An error occurred.”, A1 * 2)

This formula checks to see if an error condition exists in A1. If so, the IF function returns the message “An error occurred.” If no error exists, the IF function performs the calculation A1*2.

Below is a list of all of the possible is functions available in Excel:

Function Returns TRUE if
ISBLANK Value refers to an empty cell.
ISERR Value refers to any error value except #N/A.
ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL Value refers to a logical value.
ISNA Value refers to the #N/A (value not available) error value.
ISNONTEXT Value refers to any item that is not text. (Note that this function returns TRUE if the value refers to a blank cell.)
ISNUMBER Value refers to a number.
ISREF Value refers to a reference.
ISTEXT Value refers to text.

AAT members have free access to this and many other Excel Tips through their MyAAT account. To continue exploring more Excel tips please click here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.

Related articles