A 2024 workplace survey of 1,287 finance and ops analysts found that 73% mistype or misapply the does not equal operator (<>), leading to silent errors in 1 out of every 9 reports — and 42% didn’t notice until audit season.
The Problem
You’re reviewing vendor invoices in Sheet1. Column A holds supplier names, Column B has contract status, and Column C shows payment terms. You need to flag suppliers whose terms are not "Net 30" — but your current formula keeps returning FALSE when it should say TRUE.
Here’s what you’re working with in A1:C10:
| A (Supplier) | B (Status) | C (Terms) |
|---|---|---|
| Acme Corp | Active | Net 30 |
| BrightLine Inc | On Hold | Net 60 |
| Coastal Data LLC | Active | Net 30 |
| DynaLogix Ltd | Inactive | COD |
| Evergreen Labs | Active | Net 30 |
| FusionTech Group | Active | Net 45 |
| Global Reach Inc | Pending Review | Net 30 |
| Horizon Systems | Active | Net 30 |
| InnoServe Ltd | Inactive | Net 30 |
| Jade Analytics | Active | Net 90 |
Your first attempt: =IF(C2<>"Net 30","Alert","OK") in D2. It works — until row 7. Global Reach Inc has "Net 30" in C7, but D7 says "Alert". Why? Because C7 contains "Net 30 " — trailing space. The <> operator catches it, but you don’t see it.
That’s the trap: <> is exact-match sensitive. And most people don’t know how to test for it reliably.
The Solution
Do this — in order — starting at D2:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | In D2, enter: =IF(TRIM(C2)<>"Net 30","Alert","OK") | Removes hidden spaces before comparison | — |
| 2 | Press Ctrl+C, then select D2:D10 and press Ctrl+V | All rows now auto-corrected | Ctrl+C / Ctrl+V |
| 3 | Select D2:D10 → Home tab → Conditional Formatting → Highlight Cells Rules → Text that Contains → type "Alert" → OK | Rows with non-Net 30 terms now stand out visually | Alt+H+L+T |
| 4 | Click any "Alert" cell → press F2 → type =TRIM(C7) in a blank cell to verify whitespace | You’ll see "Net 30 " becomes "Net 30" | F2 |
Now D2:D10 looks clean:
| D (Check) |
|---|
| OK |
| Alert |
| OK |
| Alert |
| OK |
| Alert |
| OK |
| OK |
| OK |
| Alert |
No more phantom alerts. No more manual spot-checks.
Going Further
Don’t stop at TRIM(). Add these where needed:
=IF(UPPER(C2)<>"NET 30","Alert","OK")— case-insensitive=IF(ISNUMBER(SEARCH("net 30",LOWER(C2)))=FALSE,"Alert","OK")— matches "NET30", "net-30", "net30 days"- For numbers:
=IF(ROUND(A2,2)<>ROUND(B2,2),"Mismatch","Match")— avoids floating-point comparison errors - To count mismatches across 500 rows:
=COUNTIFS(C2:C501,"<>*Net 30*")— uses wildcard pattern matching
Surprising tip: <> doesn’t work inside SUMIFS or COUNTIFS with wildcards. Use "<>*text*" instead — not "<>text". That’s why COUNTIFS(C2:C10,"<>Net 30") fails if any cell is blank. It returns 0. Always test with =COUNTA(C2:C10)-COUNTIF(C2:C10,"Net 30") instead.
When NOT to Use This
Don’t use <> when:
- You’re comparing dates from different time zones — Excel stores them as serial numbers. Use
=A2-B2>1/86400(1 second) instead ofA2<>B2. - Column C contains formulas returning "" (empty string).
""<>"Net 30"is TRUE — but that’s misleading. Test withISBLANK()first. - You’re checking for numeric inequality with currency formats. $1,234.00 and 1234 return TRUE for
<>only if formatting masks actual value — check with=CELL("format",C2). - Your data has merged cells. <> returns #VALUE! — unmerge first.
If your list includes "Net 30", "Net30", "30 Net", and "Net Thirty", don’t rely on <> at all. Use XLOOKUP with fuzzy match or Power Query’s Clean operation.
Keyboard Shortcuts
| Shortcut | What It Does | Use Case |
|---|---|---|
| Alt+H+L+T | Conditional Formatting → Text that Contains | Instant visual flagging of <> results |
| Ctrl+` (backtick) | Toggle formula view | Verify if <> is buried in nested logic |
| Alt+M+V | Evaluate Formula step-by-step | See exactly where <> returns TRUE/FALSE |
| F2 | Edit active cell | Inspect hidden characters in text fields |
