Stop Using ISBLANK() — Try This Instead for 'is null excel'

Why does =ISBLANK(A2) return FALSE when A2 looks empty? Why does IF(A2="","yes","no") fail on imported data from SQL? Why does your filter hide rows that *should* be visible but aren’t?

The answer isn’t more functions — it’s understanding what Excel actually considers ‘null’. And spoiler: Excel doesn’t have NULL at all. Not really.

The Problem

You copy-paste a customer list from Power Query. Some cells in column C (‘Contract End Date’) look blank — but formulas like =IF(C2="","Missing","Active") label them ‘Active’. You sort by that column and half your ‘missing’ dates vanish. You check cell C5: it’s empty. You press F2, then Enter — and suddenly the formula works. What just happened?

Excel treats truly empty cells, cells with zero-length strings (""), and cells with whitespace (like a space or non-breaking space) as completely different things. Your import likely filled missing dates with "" — not emptiness. And ISBLANK() only catches the first case.

Customer	Region	Contract End Date	Status (Broken)
Sarah Chen	APAC	2024-11-30	Active
Rajiv Mehta	EMEA		Active
Lena Dubois	EMEA	""	Active
Takumi Sato	APAC		Active
Aisha Johnson	AMER	2025-02-14	Active

See row 3? That "" came straight from your SQL export — it’s a zero-length string. Row 4 has a non-breaking space (Alt+0160), invisible unless you select the cell and look at the formula bar. ISBLANK(C3) returns FALSE. So does C3="" — because it *is* "". But ISBLANK(C4) also returns FALSE, even though it *looks* empty. Confusing? Yes. Fixable? Absolutely.

The Solution

We’ll use =TRIM(C2)="" as our foundation — then layer in robustness. Here’s how to fix it in 4 steps:

Select your status column (say, D2:D10). Type =IF(TRIM(C2)="","Missing","Active") in D2.
Press Ctrl+Enter (not Enter) to fill the formula down without changing the active cell — keeps your hands on the keyboard.
To catch hidden spaces, wrap TRIM inside LEN: =IF(LEN(TRIM(C2))=0,"Missing","Active"). This handles tabs, line breaks, and non-breaking spaces.
For database-style NULLs, add an OR for TRUE blanks: =IF(OR(ISBLANK(C2),LEN(TRIM(C2))=0),"Missing","Active").

This last version is your go-to ‘is null excel’ test — it catches all three cases: truly blank cells, zero-length strings, and whitespace-only cells.

Customer	Region	Contract End Date	Status (Fixed)
Sarah Chen	APAC	2024-11-30	Active
Rajiv Mehta	EMEA		Missing
Lena Dubois	EMEA	""	Missing
Takumi Sato	APAC		Missing
Aisha Johnson	AMER	2025-02-14	Active

(Trust me, I learned this the hard way during a Q3 audit — spent 90 minutes chasing phantom data before realizing our ERP exported NULLs as "".)

Going Further

You can adapt this pattern for other scenarios:

Conditional formatting for ‘null-like’ cells: Select C2:C10 → Home → Conditional Formatting → New Rule → “Use a formula…” → enter =OR(ISBLANK(C2),LEN(TRIM(C2))=0) → set fill color to light yellow.
Filtering out ‘nulls’: Add a helper column with =OR(ISBLANK(C2),LEN(TRIM(C2))=0), then filter that column for TRUE.
Power Query prep: In PQ Editor, right-click the column → ‘Replace Values’ → replace "" with null (not blank), then replace leading/trailing whitespace using Transform → Format → Clean.
Array version for whole ranges: If you need to count ‘nulls’ across B2:E100, use =SUMPRODUCT(--(LEN(TRIM(B2:E100))=0)) + SUMPRODUCT(--ISBLANK(B2:E100)). Yes — it’s messy, but it works.

Here’s the counterintuitive tip: Never use =C2="" alone. It fails on whitespace, and worse — if C2 contains a formula returning "", Excel sometimes treats it differently than a hardcoded "". TRIM + LEN avoids both.

When NOT to Use This

This approach isn’t universal. Avoid it when:

You’re working with numbers formatted as text (e.g., “00123”). TRIM() won’t hurt, but LEN(TRIM())=0 still works — just make sure you’re not confusing empty with zero.
You need to distinguish between "" and #N/A. Our formula treats both as ‘missing’, but maybe you want to flag errors separately. Add ISERROR(C2) to the OR group.
Your data contains legitimate single spaces (rare, but possible in legacy systems). Then TRIM() will erase meaning. Test first — run =EXACT(C2," ") on a sample.
You’re using Excel Online or older Excel versions (<2010). TRIM() handles non-breaking spaces inconsistently there — stick with CLEAN() + TRIM() combo: =LEN(TRIM(CLEAN(C2)))=0.

Also — don’t apply this blindly to date columns expecting to catch 0 (which displays as 1900-01-00). That’s a different problem entirely.

Keyboard Shortcuts

Speed matters when cleaning 10k-row reports. These Alt sequences cut time:

Action	Shortcut	Notes
Open Find & Replace	Alt+H+F	Great for replacing "" with real blanks before applying logic
Apply AutoFilter	Ctrl+Shift+L	Filter on helper column after building your ISNULL test
Fill formula down	Ctrl+Enter	Select D2:D100 first, type formula in D2, then Ctrl+Enter
Toggle formula view	Ctrl+` (backtick)	See "" vs true blank instantly — no guessing