Excel information functions
Information functions answer a yes-or-no question about a value: is it blank, a number, text, an error? On their own they return TRUE or FALSE. Their real use is inside IF, SUMPRODUCT and conditional formatting, where they flag bad imports, skip missing data and keep one error from breaking a whole report.
- Functions
- 10
- With a graded exercise
- All 10
- Start with
ISBLANK
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Flag rows with a missing value | ISBLANK | TRUE only for truly empty cells, so you can mark gaps before they break totals. |
| Find numbers stored as text | ISNUMBER | FALSE for imported digits that Excel treats as text, which quietly drop out of SUM. |
| Check whether a cell contains some text | ISNUMBER | Wrap SEARCH in ISNUMBER for a contains test that returns TRUE or FALSE. |
| Tell "not found" apart from a real error | ISNA | Catches only #N/A, so typos and broken references still show. |
| Catch any error before it spreads | ISERROR | Covers every error type when you need a safe fallback. |
| Split items or rows by odd and even | ISEVEN | Alternate shifts, sides of a street or row shading with one test. |
Try ISBLANK live
The most useful information function, on a sample sheet you can edit. Change a value and watch the results update.
E2=IF(ISBLANK(B3),"Needs owner","Assigned") Needs owner Book venue has no owner yetF2=SUMPRODUCT(--ISBLANK(B2:B5)) 2 Counts the empty owner cellsG2=IF(OR(ISBLANK(B4),ISBLANK(C4)),"Incomplete","Complete") Incomplete Send invites is missing a due date
All 10 information functions
| Function | What it does | Practice exercise |
|---|---|---|
ISBLANK function | ISBLANK returns TRUE when a cell is completely empty. | Flag missing emails |
ISERR function | ISERR returns TRUE for any error except #N/A. | Cost per attendee |
ISERROR function | ISERROR returns TRUE for any error value, such as #N/A, #VALUE!, #REF!, #DIV/0! or #NAME?. | Price per unit, safely |
ISEVEN function | ISEVEN returns TRUE when a number is even. | Day or night shift |
ISLOGICAL function | ISLOGICAL returns TRUE when a value is one of the logical values, TRUE or FALSE. | Count real checkboxes |
ISNA function | ISNA returns TRUE only for the #N/A error, the one MATCH, VLOOKUP and XLOOKUP return when a value isn't found. | New or listed? |
ISNUMBER function | ISNUMBER returns TRUE when a value is a real number, which includes dates and times. | Totals only for real quantities |
ISODD function | ISODD returns TRUE when a number is odd. | Two teams |
ISTEXT function | ISTEXT returns TRUE when a value is text, including numbers that were stored as text and the empty text "" that some formulas return. | Payable hours |
NA function | NA() returns the #N/A error on purpose, to mark a value as not available. | Chart-ready sales |