The ISBLANK, ISNUMBER and ISERROR functions in Excel
The IS functions ask a yes/no question about a value: is it empty (ISBLANK), is it a number (ISNUMBER), is it an error (ISERROR)? Each returns TRUE or FALSE.
On their own they just show TRUE or FALSE. Inside IF they decide what a formula does with missing, text or broken values.
Syntax
=ISBLANK(value)
| Argument | What it means |
|---|---|
| value | The cell or value to test. The same argument is used by ISNUMBER, ISTEXT and ISERROR. |
Examples
1. Is the cell empty?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Age | Age missing? | |
| 2 | Mia | 25 | ||
| 3 | Noah | TRUE | ||
| 4 | Zoe | n/a |
Noah's age cell is empty, so ISBLANK returns TRUE.
2. Is the value a number?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Age | Valid age? | |
| 2 | Mia | 25 | ||
| 3 | Noah | |||
| 4 | Zoe | n/a | FALSE |
Zoe's age is the text "n/a", which is not a number.
3. Show a message for missing data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Age | Review | |
| 2 | Mia | 25 | ||
| 3 | Noah | Ask for age | ||
| 4 | Zoe | n/a |
When the age is blank the formula shows a reminder; otherwise it returns the age.
4. Is the result an error?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Revenue | Units | Error? | |
| 2 | 100 | 0 | TRUE |
Dividing by 0 gives #DIV/0!, and ISERROR turns any error into TRUE.
Common mistakes
Testing a formula that returns an empty text.
=ISBLANK(C2)is FALSE if C2 contains a formula that returns "".Fix: Test with
=C2=""instead, which is TRUE for both empty cells and empty text.Using ISERROR to hide everything. It also hides real mistakes in the formula.
Fix: Use IFNA for lookups, and IFERROR only where any error can be expected.
Frequently asked questions
What other IS functions exist?
ISTEXT, ISLOGICAL, ISNA, ISERR, ISEVEN, ISODD, ISFORMULA and ISREF. They all take one value and return TRUE or FALSE.
Is a cell with a space blank?
No. A cell holding only a space looks empty but ISBLANK returns FALSE. TRIM and a test for "" catch it.
Related functions
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.
- IFIF checks a condition and returns one value if it's true and another if it's false.
- COUNTCOUNT counts cells that contain numbers; COUNTA counts every non-empty cell; COUNTBLANK counts empty ones.