The first thing most people do when they need to test for truly empty cells is write =A1="". That’s wrong — and dangerous. It returns TRUE for cells with zero-length strings (like ="" entered via formula), spaces, or even non-breaking spaces. ISBLANK doesn’t. But even then, people slap ISBLANK into IF statements without checking what it *actually* sees — and get burned when cells contain invisible characters or formulas returning "".
The Setup
You’re auditing a vendor payment log in Sheet1. Column A holds invoice IDs, B has vendor names, C contains payment dates, D shows amounts, and E is meant to hold reconciliation notes — but many rows are incomplete. Your job: flag rows where reconciliation notes are missing *and only missing*, not just blank-looking.
| A1: Invoice ID | B1: Vendor | C1: Payment Date | D1: Amount | E1: Reconciliation Notes |
|---|---|---|---|---|
| INV-7821 | Acme Corp | 2024-03-15 | $12,450 | |
| INV-7822 | Nexus Labs | 2024-03-16 | $8,920 | Approved — matched PO#4491 |
| INV-7823 | Skyline Logistics | 2024-03-17 | $3,200 | |
| INV-7824 | Veridian Systems | 2024-03-18 | $15,670 | =IF(F2="Y","Pending review","") |
| INV-7825 | TerraFirm Inc | 2024-03-19 | $6,140 | |
| INV-7826 | Orion Dynamics | 2024-03-20 | $22,800 | |
| INV-7827 | Lumina Group | 2024-03-21 | $9,330 | \u00A0 |
| INV-7828 | StellarWorks Ltd | 2024-03-22 | $4,750 | =TRIM(G2) |
| INV-7829 | Crestwood Partners | 2024-03-23 | $18,210 | — |
The Challenge
You need to identify rows where column E is *truly empty* — no characters, no formulas, no spaces, no non-breaking spaces. Not "looks blank", but *is* blank. ISBLANK(E2) seems perfect. But here’s the trap: it returns FALSE for cells containing formulas that output "" — like E4 and E8 above — even though those cells *appear* blank. And it returns TRUE only for cells that are genuinely empty (E1, E6) — but you’ll miss E3, E5, and E7 because they contain space characters or . So your audit will be incomplete unless you combine ISBLANK with other tests.
Walking Through It
Start in F2. Type: =ISBLANK(E2). Press Enter. Copy down to F10.
| E2:E10 (Notes) | F2:F10 (=ISBLANK(E2)) |
|---|---|
| TRUE | |
| Approved — matched PO#4491 | FALSE |
| FALSE | |
| =IF(F2="Y","Pending review","") | FALSE |
| FALSE | |
| TRUE | |
| \u00A0 | FALSE |
| =TRIM(G2) | FALSE |
| — | FALSE |
That’s step one — but it only catches *two* of the nine rows that need attention. Now add a second test. In G2, enter: =AND(ISBLANK(E2),LEN(TRIM(E2))=0). No — don’t do that. That’s redundant. ISBLANK already ignores whitespace. The real fix is simpler: use =OR(ISBLANK(E2),TRIM(E2)="") — but wait. That still fails on non-breaking spaces. So do this instead:
In G2, type: =AND(LEN(SUBSTITUTE(SUBSTITUTE(E2,CHAR(160),"")," ",""))=0,NOT(ISFORMULA(E2))). Press Ctrl+Enter to keep it in place. Then copy down.
But that’s overkill for most cases. For daily work, use this: =ISBLANK(E2)+LEN(TRIM(CLEAN(E2)))=0. That’s not valid syntax — so don’t. Do this: =IF(ISBLANK(E2),"YES",IF(LEN(TRIM(CLEAN(E2)))=0,"YES","NO")). Yes, it’s long. But it works.
Here’s the practical version. In G2: =IF(OR(ISBLANK(E2),AND(LEN(E2)>0,TRIM(CLEAN(E2))="")),"MISSING","OK"). Copy to G10.
The Result
| Invoice ID | Reconciliation Notes | Status (G2:G10) |
|---|---|---|
| INV-7821 | MISSING | |
| INV-7822 | Approved — matched PO#4491 | OK |
| INV-7823 | MISSING | |
| INV-7824 | =IF(F2="Y","Pending review","") | OK |
| INV-7825 | MISSING | |
| INV-7826 | MISSING | |
| INV-7827 | \u00A0 | MISSING |
| INV-7828 | =TRIM(G2) | OK |
| INV-7829 | — | OK |
What Could Go Wrong
Mistake #1: Assuming ISBLANK works on formula results
ISBLANK(E4) returns FALSE even though E4 displays nothing — because it contains a formula. You’ll mark it as “filled” and skip validation. Always pair ISBLANK with ISFORMULA if blank appearance matters.
Mistake #2: Using =E2="" instead of ISBLANK(E2)
This returns TRUE for cells with ="" or =CHAR(160). That’s fine if you want to catch all blanks *including* formula-blanks — but ISBLANK won’t. Pick one behavior and stick with it. Don’t mix them across reports.
Mistake #3: Applying conditional formatting with =ISBLANK($E2) and expecting it to highlight cells with spaces
It won’t. Conditional formatting sees the same thing ISBLANK does: physical emptiness. To highlight cells that *look* blank, use =TRIM(CLEAN($E2))="" instead. Alt+O+D opens Conditional Formatting. Alt+H+L opens Home → Fill Color — use that to mark mismatches.
One last thing: ISBLANK returns #VALUE! if you feed it a range like E2:E10. It only accepts single-cell references. If you need array behavior, wrap it: =SUMPRODUCT(--ISBLANK(E2:E10)) counts truly blank cells in the range.
| Task | Correct Formula | Shortcut / Tip |
|---|---|---|
| Test if cell is truly empty | =ISBLANK(A1) | Only works on single cells — never ranges |
| Count blank cells in A1:A100 | =COUNTBLANK(A1:A100) | Faster than SUMPRODUCT + ISBLANK |
| Flag cells that look blank (spaces, non-breaking spaces) | =TRIM(CLEAN(A1))="" | Use in CF or FILTER — not for data validation logic |
| Check if cell is blank OR contains only whitespace | =OR(ISBLANK(A1),TRIM(A1)="") | Add CLEAN() if dealing with copied web data |
