FormuladrillISERROR, ISBLANK and IS functions
The IS functions test a value and return TRUE or FALSE: ISBLANK(A2) is TRUE for a truly empty cell, ISERROR(x) for any error, ISNA(x) only for #N/A, ISNUMBER(x) for numbers and ISTEXT(x) for text. Use them inside IF to branch, for example =IF(ISBLANK(A2), "", A2*2).
Syntax
ISBLANK(value) ISERROR(value) ISNA(value) ISNUMBER(value) ISTEXT(value)| Part | What it is |
|---|---|
value | A cell reference or formula to test. |
Examples
=IF(ISBLANK(A2), "", A2*2)Result: Empty text for an empty A2, otherwise double A2
=IF(ISERROR(A2/B2), "check", A2/B2)Result: check when the division errors (B2 is 0 or text)
IFERROR(A2/B2, "check") is shorter for this pattern.
=IF(ISNUMBER(SEARCH("urgent", A2)), "Yes", "No")Result: Yes when A2 contains the word urgent
SEARCH returns a number when found and #VALUE! when not; ISNUMBER turns that into TRUE or FALSE.
=SUMPRODUCT(--ISTEXT(C2:C100))Result: How many cells in C2:C100 hold text, which catches numbers stored as text
Common mistakes
- ISBLANK returns FALSE on a cell that looks empty: the cell holds a formula returning "" or a space. Test with A2="" or LEN(TRIM(A2))=0 instead.
- ISERROR hides everything including #REF!. For lookups use ISNA so broken references still show.
- ISNUMBER("123") is FALSE: the text 123 is not a number. Convert with VALUE first.
- Forgetting that IS functions return TRUE or FALSE, not the value; wrap them in IF to return something useful.
Practice ISERROR, ISBLANK and IS functions
These drills check your answer on live data, free, no account needed.
- No more #DIV/0!Revenue per unit is revenue divided by units. When units are 0 the cell must show 0 instead of an error. Fill D2 and D3.
Questions
How do I check if a cell is empty in Excel?
=ISBLANK(A2) for a truly empty cell, or =A2="" which also treats a formula returning an empty string as empty.
What is the difference between ISERROR and ISNA?
ISERROR is TRUE for every error type. ISNA is TRUE only for #N/A, the error lookups give when a value is not found.
How do I check if a cell contains a number?
=ISNUMBER(A2). It is FALSE for numbers stored as text; =ISNUMBER(VALUE(A2)) is TRUE for those as well.
Related
- IFERRORHow the Excel IFERROR function returns a value you choose instead of an error, with examples for division by zero and VLOOKUP, and when IFNA is better.
- IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.
- COUNT, COUNTA and COUNTBLANKHow COUNT, COUNTA and COUNTBLANK differ in Excel: COUNT counts numbers, COUNTA any non-empty cell, COUNTBLANK empty cells, with examples and the blank trap.
- TRIMHow the Excel TRIM function removes leading, trailing and repeated spaces, why lookups fail without it, and how to remove the non-breaking spaces it leaves.