What Most People Miss About Is Blank Excel

Yes, ISBLANK(A1) returns TRUE if A1 is truly empty. But if A1 contains a formula that outputs "" (like =IF(B1="", "", B1)), ISBLANK lies to you — and you won’t notice until your dashboard breaks on Friday at 4:58 PM.

The Problem

You’re auditing a vendor payment list from procurement. It came in as a CSV dump — some rows have missing PO numbers, others show "" after a VLOOKUP, and one cell even has a non-breaking space (Alt+0160) that looks identical to blank but isn’t. You apply conditional formatting with =ISBLANK(C2) to flag missing POs — and miss 7 of the 12 problem rows.

Here’s what your raw data actually looks like — not cleaned, not idealized:

Vendor	PO Number	Amount	Status	ISBLANK(PO)
Acme Corp	PO-2024-7781	$12,450	Approved	FALSE
Nexus Labs		$8,920	Pending	TRUE
Stellar Dynamics	=IF(E2="","",E2)	$0	Draft	FALSE
Veridian Systems		$3,200	Approved	FALSE
Orion Group		$14,600	On Hold	FALSE
TerraLink Inc	=TRIM(D2)	$5,180	Approved	FALSE
Quantum Edge	#N/A	$0	Error	FALSE
LumenCore	” ”	$7,320	Approved	FALSE

Notice how row 3 (Stellar Dynamics) and row 8 (LumenCore) both *look* blank but return FALSE for ISBLANK(). That’s because Excel treats cells with formulas — even ones returning "" — as non-blank. And those spaces? They’re characters, not emptiness.

We’ve all been there: you build a report using ISBLANK(), ship it, then get a Slack message at 7:03 AM: “Why does the ‘Missing PO’ count say 2 when I just found 9?” (Trust me, I learned this the hard way — it was a client who’d paid $28K for the dashboard.)

The Solution

There’s no universal fix — but there *is* a reliable method stack. We’ll start simple, then layer in nuance.

Step 1: Use =C2="" instead of =ISBLANK(C2)
This catches both truly blank cells *and* cells containing "" from formulas. In C2:C1000, enter =C2="" and drag down. It returns TRUE for rows 2, 3, 4, 5, 6, and 8 above — all the visually empty ones. Why? Because "" is a string, and comparing any cell to "" evaluates whether its displayed value equals nothing.
Step 2: Trap invisible characters with =TRIM(C2)=""
That fixes rows 4 and 5 (the spaces). TRIM() removes leading/trailing spaces *and* collapses internal multiple spaces to single ones — but crucially, it leaves non-breaking spaces untouched. So for row 4 ( ), TRIM(C4) becomes "", and =TRIM(C4)="" returns TRUE.
Step 3: Handle non-breaking spaces with =SUBSTITUTE(C2,CHAR(160),"")=""
Add this inside the TRIM if you suspect imported web data or copied PDF text. CHAR(160) is the non-breaking space. Wrap it: =TRIM(SUBSTITUTE(C2,CHAR(160),""))="". Now row 4 and row 5 are covered — and so is that sneaky LumenCore entry (row 8) if it used CHAR(160).
Step 4: Combine into one bulletproof test
In column E, starting at E2: =TRIM(SUBSTITUTE(C2,CHAR(160),""))="". Copy down. This catches blanks, "", regular spaces, and non-breaking spaces — everything that *looks* empty to a human.

Here’s the cleaned result — same rows, now correctly flagged:

Vendor	PO Number	Amount	Status	Is Visibly Blank?
Acme Corp	PO-2024-7781	$12,450	Approved	FALSE
Nexus Labs		$8,920	Pending	TRUE
Stellar Dynamics	=IF(E2="","",E2)	$0	Draft	TRUE
Veridian Systems		$3,200	Approved	TRUE
Orion Group		$14,600	On Hold	TRUE
TerraLink Inc	=TRIM(D2)	$5,180	Approved	TRUE
Quantum Edge	#N/A	$0	Error	FALSE
LumenCore	” ”	$7,320	Approved	TRUE

Now your ‘Missing PO’ count is accurate. And yes — this works with array formulas. If you’re on Excel 365 or 2021, you can even wrap it in FILTER(): =FILTER(A2:A9,E2:E9,"No blanks found").

Going Further

Real-world data rarely stays tidy. Let’s go beyond the basic test.

Is not blank excel — and why `<>""` is safer than `NOT(ISBLANK())`

You’ll see =NOT(ISBLANK(C2)) recommended everywhere. Don’t use it for visibility checks. It fails on formula-generated "", just like ISBLANK(). Instead, use =C2<>"". It’s shorter, faster, and matches human perception. For example, in G2: =C2<>"" returns TRUE for Acme, Nexus, Stellar, etc. — all except the visually blank ones.

And here’s the counterintuitive tip: <>"" *also* returns TRUE for errors like #N/A or #VALUE!. So if you want “not blank AND not an error”, layer in ISERROR(): =AND(C2<>"",NOT(ISERROR(C2))). That excludes both blanks *and* errors — perfect for clean summary counts.

Is not empty excel vs. is empty excel — they’re the same thing in Excel

Here’s what most tutorials get wrong: IS EMPTY doesn’t exist as a built-in function. There’s no ISEMPTY() in Excel like there is in VBA. When people search “is not empty excel” or “is empty excel”, they’re almost always trying to replicate what =C2<>"" or =C2="" already do. So stop hunting for ISEMPTY() — it’s a mirage.

But — and this matters — VBA *does* have IsEmpty(). It behaves differently: it returns TRUE only for uninitialized variables or cells with *no content at all*, not even a formula. So if you paste values over formulas, IsEmpty() may flip from FALSE to TRUE. Don’t assume Excel’s worksheet logic mirrors VBA’s. They’re separate universes.

Conditional formatting that actually works

To highlight all visibly blank cells in C2:C1000: Select the range → Home tab → Conditional Formatting → New Rule → “Use a formula…” → enter =TRIM(SUBSTITUTE(C2,CHAR(160),""))="" → set fill color. Done. No more missed rows.

Power Query: the real long-term fix

If this is recurring (and it will be), bring the data into Power Query. Under Transform → Format → Clean, it removes all leading/trailing spaces *and* non-breaking spaces in one click. Then add a custom column: =if [PO Number] = null or [PO Number] = "" then true else false. Power Query treats null (true blank) and "" (empty string) as distinct — but you can handle both explicitly. Export back to Excel, and your ISBLANK() problems vanish upstream.

Array performance note

Using TRIM(SUBSTITUTE()) across 100K rows? It’s fine — but avoid nesting more than two SUBSTITUTE calls unless necessary. For extreme scale (500K+ rows), pre-clean in Power Query or use TEXTJOIN() + FILTERXML() tricks (though that’s overkill for 95% of cases). Stick with the four-step method — it’s readable, auditable, and fast enough.

When NOT to Use This

This approach is powerful — but misapplied, it creates new problems.

Avoid it inside SUMIFS / COUNTIFS criteria ranges — Excel’s criteria evaluation in these functions doesn’t support full expressions like TRIM(SUBSTITUTE()). Use helper columns instead. Trying =COUNTIFS(C2:C1000,"="&TRIM(SUBSTITUTE(C2,CHAR(160),""))) will fail with #VALUE!. Build the clean column in D2:D1000 first, then reference D2:D1000,"=".
Don’t use it on dates or numbers formatted as text — if C2 contains "2024-03-15" (as text), =C2="" correctly returns FALSE. But if it contains 0 (which displays as blank when formatted as Date), =C2="" returns FALSE while the cell *looks* empty. To catch zero-dates, add OR(C2="",C2=0) — but test carefully: 0 could legitimately mean “zero units”, not “missing”.
Never use ISBLANK() in data validation rules expecting formula-generated blanks — e.g., a dropdown where selection triggers =IF(A2="Vendor",B2,""). Data validation with =ISBLANK(C2) will always allow input, because C2 *contains a formula*. Use =C2="" instead — or better yet, skip validation and use conditional formatting to warn users.
Watch for merged cells — ISBLANK() only checks the top-left cell of a merged range. So if A1:B1 is merged and contains data in A1, ISBLANK(A1) = FALSE, but ISBLANK(B1) = TRUE (even though B1 is part of the merge). Our =C2="" method avoids this by targeting the logical cell — but if your sheet uses merges, unmerge first. Merged cells break 90% of Excel logic, not just blank detection.

Also — don’t waste time debugging ISBLANK() inside IF() chains like =IF(ISBLANK(C2),"Missing","OK"). Rewrite it as =IF(C2="","Missing","OK"). You’ll save 3 seconds per formula — and over 500 formulas, that’s 25 minutes you get back. Not magic. Just math.

Keyboard Shortcuts

These shortcuts cut through the noise when verifying blanks manually or cleaning data:

Action	Shortcut (Windows)	Notes
Select all blank cells in a range	Ctrl + G → Alt+S → K	Go To Special → Blanks. Fastest way to highlight or delete true blanks.
Find non-breaking spaces	Ctrl + H → Alt+0160 in Find, leave Replace blank	Then click 'Find All' — reveals every hidden CHAR(160).
Toggle formula view	Ctrl + ` (backtick)	See formulas instead of results — instantly spot `""` outputs.
Clear contents (not formats)	Alt+H → E → C	Home → Clear → Clear Contents. Safer than Delete when cleaning blanks.
Open Go To dialog	F5 or Ctrl + G	Essential for navigating large datasets with gaps.