Mastering MS Excel’s IF Statements: A Comprehensive Guide

Quick Links

• The IS Functions in Excel
• IS Function Examples

Key Takeaways

Excel’s IS functions return “True” or “False” depending on the contents of a cell. For example, =ISBLANK(A1) will return “TRUE” if cell A1 is blank and “FALSE” if it is not. Excel provides the following IS functions: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, and ISTEXT.

When you want to test your data and receive a simple True or False result, the IS functions in Excel are just what you need. You can check for blank cells, errors, text, numbers, references, and more quickly and easily.

There are currently nine IS functions, also known as information functions, in Excel. This includes ISBLANK, ISERROR, ISTEXT, and more.

When you use these functions in conjunction with others, like the IF function , you can put parameters around your true/false statements. But, you can also use the IS functions on their own if it’s beneficial. Let’s take a look at how they work.

The IS Functions in Excel

The syntax for each function’s formula is the same with just one argument:

    `ISLBLANK(value)`

,

    `ISERR(value)`

,

    `ISERROR(value)`

, and so on. The

    `value`

can be a cell reference, text, or number, and the result is either True or False.

Here are the nine IS functions and brief explanations of each one.

• ISBLANK: Returns True for a blank cell or False for a cell with content.
• ISERR: Returns True for any error value except #N/A or False for the #N/A error.
• ISERROR: Returns True for any error value or False for no error.
• ISLOGICAL: Returns True for a logical value or False if not a logical value.
• ISNA: Returns True for the #N/A error or False if not an #N/A error.
• ISNONTEXT: Returns True for a value that’s not text (or a blank cell) or False for a value that’s text.
• ISNUMBER: Returns True for a value that’s a number or False if it’s not a number.
• ISREF: Returns True if the value is a reference or False if it’s not a reference.
• ISTEXT: Returns True if the value is text or False if it’s not text.

IS Function Examples

As you review the above list, you can see where some of the IS functions can come in handy. At the same time, you may see a couple that you’ll probably rarely use. So, let’s look at common uses for a few of the functions.

ISBLANK

The ISBLANK function is one you can use to locate empty cells where you expect data. You can use this with the IF function to display a particular value for blank and non-blank cells.

Here, we have ISBLANK to determine if cell A1 is blank.

=ISBLANK(A1)

The result is True for a blank cell. You can copy the same formula down to check additional cells using the fill handle. As you see below, we have True for blanks and False for non-blanks.

Click here

Now we’ll include the IF function to display a question mark (?) for a blank cell and a hyphen (-) for a cell containing data.

=IF(ISBLANK(A1),”?”,”-“)

As you can see, we now have the result of a question mark instead of True for blank cells.

ISERROR

The ISERROR function is ideal if you want to locate errors in your sheet for constantly changing data. It works well with the IF function in the same way that IFERROR works on its own.

As an example, we’ll use ISERROR to determine if a calculation results in an error. We’ll divide the value in cell A1 by that in cell B1.

=ISERROR(A1/B1)

As you can see, we have an error because the result displays True. The error would be #DIV/0! because you cannot divide by zero.

Now we can add the IF function to display something other than True or False. If the calculation results in an error, we’ll display 1, otherwise, we’ll display 2.

=IF(ISERROR(A1/B1),A,B)

As you can see, our result is 1 because the calculation returns an error.

Again, you can use the newer IFERROR function instead of the IF and ISERROR combination in most cases. But you can also use IF and ISERROR with other functions like VLOOKUP to display certain results for both errors and non-errors, whereas IFERROR displays only the value for the error.

For more on this specific topic, take a look at our guide on using the IFERROR function to hide errors in your sheet.

Related: How to Hide Error Values and Indicators in Microsoft Excel

ISNUMBER and ISTEXT

Next, we’ll look at examples using the ISNUMBER and ISTEXT functions. These similarly display True if the value is a number or text, respectively.

Here, we want to ensure we have a number in our cell .

=ISNUMBER(A1)

Our result is False because the value in cell A1 is text, not a number.

Now, we’ll add the IF function to display nothing if the cell contains a number but “Please enter a number” if the cell doesn’t contain a number.

=IF(ISNUMBER(A1),” “,”Please enter a number”)

By using the above formula, we can let the user know they must enter a number.

We can do the same thing with the ISTEXT function. Here we have our ISTEXT function to see if the value is text or not.

=ISTEXT(A1)

And with the IF function, we can display “Please enter text” if the value is something other than text.

=IF(ISTEXT(A1),” “,”Please enter text”)

These are basic examples of how to use the IS functions in Excel. They are available for simple checks of data on their own or more complex tests when combined with other functions.

| | Mastering Excel Functions | | |
| —————————- | —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | |
| Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH ·MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | |
| Types | Basic · Budgeting · Data Entry · Logical · Text · Time and Date | |
| Explained | Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas | |

Also read: