It’s 3:12 PM. You’re auditing a supplier payment log (Sheet1!A2:E500) before sending it to Finance. Column D has delivery dates — but some rows show blank cells, others show '–', and two have invisible spaces you only spotted after filtering. Your =IF(ISBLANK(D2),"Pending","Shipped") formula marks three 'Shipped' entries as 'Pending'. You just lost 47 minutes rechecking manually.
Quick Answer
ISBLANK() returns TRUE only if a cell contains absolutely nothing — no text, no space, no formula result (even if that result is ""). It returns FALSE for cells with spaces, apostrophes, zero-length strings from formulas like ="", or non-breaking spaces (CHAR(160)). That’s why it often misfires in real-world data.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| =ISBLANK(A1) | Type directly or use Formula Bar. No extra steps. | Cells you control — e.g., manual entry fields where users never paste. | Fails on " ", ="", CHAR(160), or cells formatted as Text with hidden characters. |
| =A1="" | Enter in any cell. Evaluates visual emptiness. | Reports, dashboards, and conditional formatting where "looks blank" matters more than technical emptiness. | Returns TRUE for ="" and cells with spaces — may over-report blanks. |
| =LEN(TRIM(CLEAN(A1)))=0 | Wraps A1 in CLEAN (removes non-printing chars), TRIM (removes spaces), then checks length. | Imported data, CSVs, web scrapes, and ERP exports full of hidden junk. | Slightly slower on huge ranges; requires Ctrl+Shift+Enter in pre-365 versions for array use. |
| =COUNTA(A1)=0 | Counts non-empty cells. Returns TRUE only if zero items found. | Single-cell checks where you want strict logic but avoid nested functions. | Doesn’t distinguish between "" and " " — both count as non-blank in older Excel versions depending on context. |
| Conditional Formatting + ISBLANK | Home → Conditional Formatting → New Rule → Use formula: =ISBLANK($D2). Apply to D2:D100. | Highlighting truly empty delivery date cells before sending reports. | Won’t highlight cells with trailing spaces — so you’ll miss them unless you combine with TRIM first. |
Method 1 Deep Dive
Let’s test =ISBLANK() on real data from a vendor shipment log (Sheet1!A1:E8):
| A (Vendor) | B (PO#) | C (Qty) | D (Ship Date) | E (ISBLANK(D?) |
|---|---|---|---|---|
| Acme Corp | PO-7721 | 12 | TRUE | |
| Beta Logistics | PO-7722 | 8 | FALSE | |
| Cygnus Ltd | PO-7723 | 15 | =IF(B3="","",TODAY()-1) | FALSE |
| Delta Inc | PO-7724 | 6 | FALSE | |
| Echo Systems | PO-7725 | 22 | 2024-03-15 | FALSE |
Row 2 (Beta Logistics) shows why ISBLANK fails: that cell looks blank, but it contains a single space — entered accidentally while pasting. ISBLANK returns FALSE. Row 3? The formula returns "" when B3 is empty, but ISBLANK sees the formula — not its output — so it says FALSE. That’s the counterintuitive part: ISBLANK checks structure, not content. To catch those, use =D2="" instead. Try it in E2: =D2="" returns TRUE for rows 2 and 3 — because it evaluates what’s displayed.
Method 2 Deep Dive
Now let’s fix the messy imported data. You pasted a CSV into Sheet2!A1:C12. Column C has delivery notes — but some rows say "N/A", others are truly blank, and three contain non-breaking spaces (common in web forms). You need to flag all *visually* empty rows.
Use this in D2 and drag down:
=LEN(TRIM(CLEAN(C2)))=0
CLEAN removes CHAR(1), CHAR(7), and other non-printing ASCII codes. TRIM wipes leading/trailing spaces and collapses internal spaces to one. LEN measures what’s left. If it’s zero — it’s safe to call blank.
Test it on these actual values from C2:C6:
- C2: "" → CLEAN+TRIM = "" → LEN = 0 → TRUE
- C3: " " (space) → CLEAN unchanged → TRIM = "" → LEN = 0 → TRUE
- C4: CHAR(160)&"" (non-breaking space) → CLEAN leaves it → TRIM doesn’t remove it → LEN = 1 → FALSE (so you’ll see it)
- C5: "N/A" → LEN = 3 → FALSE
- C6: " Delivered " → TRIM = "Delivered" → LEN = 9 → FALSE
That last one matters: you’re not trying to find *all* empty-looking cells — just ones that should be empty. So if your business rule is "no note = pending", this catches exactly what you need. Bonus: press Alt+M+V to open Evaluate Formula and step through each layer — great for debugging.
Cheat Sheet
| Task | Formula | Shortcut / Tip | When to Use It |
|---|---|---|---|
| Check if cell is truly empty (no formula, no chars) | =ISBLANK(A1) | No shortcut — but fast to type | Input forms where users type manually — never paste |
| Flag cells that look blank (including "" and spaces) | =A1="" | Ctrl+H → Find what: " " → Replace with: (blank) → Replace All, then re-check | Dashboards, status reports, quick filters |
| Catch hidden junk (CHAR(160), tabs, line breaks) | =LEN(TRIM(CLEAN(A1)))=0 | Alt+M+V to debug step-by-step | CSV imports, ERP exports, web data pulls |
| Conditional format truly blank cells only | New Rule → Format only cells that contain → Blanks | This built-in option *does* respect ISBLANK logic — unlike custom formulas | Auditing raw entry sheets before processing |
| Count how many cells are *effectively* blank | =SUMPRODUCT(--(LEN(TRIM(CLEAN(A2:A100)))=0)) | Paste as array in Excel 2019 or earlier (Ctrl+Shift+Enter) | Monthly QA checks on master data sheets |
