Learning Excel: IS functions
Empty cells and IS functions
Laura has nothing in the Visits column, and Hannah's age is behaving oddly. "How do I know what's really in a cell?" asks Dave. With a handful of functions that start with IS.
Empty is not zero
D6 is empty. Compare an empty cell with 0 and Excel says TRUE: in a calculation, empty counts as 0.
To find out whether a cell is really empty, use ISBLANK. =ISBLANK(D6) is TRUE: Laura's visits weren't filled in, they weren't 0.
=ISBLANK(D6)
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Age | Pass | Visits | Paid | Category | Per visit | |
| 2 | Jack | 9 | Single | 3 | £15.00 | |||
| 3 | Nora | 34 | Annual | 22 | £240.00 | |||
| 4 | Mila | 15 | Monthly | 9 | £25.00 | |||
| 5 | Rita | 67 | Annual | 15 | £240.00 | |||
| 6 | Laura | 41 | Single | £10.00 | TRUE | |||
| 7 | Rory | 7 | Monthly | 6 | £25.00 | |||
| 8 | Jim | 72 | Annual | 18 | £240.00 | |||
| 9 | Will | 28 | Single | 4 | £20.00 | |||
| 10 | Hannah | 12 | Monthly | 11 | £25.00 | |||
| 11 | Sara | 55 | Annual | 0 | £240.00 | |||
| 12 |
Number or text?
ISNUMBER asks whether a cell holds a number, ISTEXT whether it holds text. Both give TRUE or FALSE.
Hannah's age in B10 looks like 12, but it's text. So =ISNUMBER(B10) gives FALSE. A tip: numbers sit on the right of a cell, text on the left.
=ISNUMBER(B10)
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Age | Pass | Visits | Paid | Category | Per visit | |
| 2 | Jack | 9 | Single | 3 | £15.00 | |||
| 3 | Nora | 34 | Annual | 22 | £240.00 | |||
| 4 | Mila | 15 | Monthly | 9 | £25.00 | |||
| 5 | Rita | 67 | Annual | 15 | £240.00 | |||
| 6 | Laura | 41 | Single | £10.00 | ||||
| 7 | Rory | 7 | Monthly | 6 | £25.00 | |||
| 8 | Jim | 72 | Annual | 18 | £240.00 | |||
| 9 | Will | 28 | Single | 4 | £20.00 | |||
| 10 | Hannah | 12 | Monthly | 11 | £25.00 | FALSE | ||
| 11 | Sara | 55 | Annual | 0 | £240.00 | |||
| 12 |
Try it yourself
In H2, put TRUE if the age in B2 is text, and fill down to H11.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Age | Pass | Visits | Paid | Category | Per visit | ||
| 2 | Jack | 9 | Single | 3 | £15.00 | ||||
| 3 | Nora | 34 | Annual | 22 | £240.00 | ||||
| 4 | Mila | 15 | Monthly | 9 | £25.00 | ||||
| 5 | Rita | 67 | Annual | 15 | £240.00 | ||||
| 6 | Laura | 41 | Single | £10.00 | |||||
| 7 | Rory | 7 | Monthly | 6 | £25.00 | ||||
| 8 | Jim | 72 | Annual | 18 | £240.00 | ||||
| 9 | Will | 28 | Single | 4 | £20.00 | ||||
| 10 | Hannah | 12 | Monthly | 11 | £25.00 | ||||
| 11 | Sara | 55 | Annual | 0 | £240.00 | ||||
| 12 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson