Does Not Equal in Excel: How to Use the <> Operator

How the Does Not Equal Operator Works in Excel

The does not equal operator in Excel is written as two angle brackets stuck together: <>. Yes, that little symbol is the entire trick. You drop it between two values, and Excel returns TRUE when those values are different — FALSE when they match. Simple in theory. Sometimes annoying in practice, especially when blank cells, hidden spaces, or stray apostrophes throw the result off.

Most people meet <> first inside an IF statement. Something like =IF(A2<>"Approved","Check",""). But the operator does much more. You can pair it with COUNTIF, SUMIF, SUMIFS, AVERAGEIFS, and even array formulas. It even shows up inside data validation rules and conditional formatting logic — anywhere Excel needs a yes/no comparison.

And here is where the confusion starts. <> behaves slightly differently with numbers, text, errors, and empty cells. A number compared with text? Different. A blank cell compared with an empty string ""? Excel often says they are equal — sometimes not. That is why this guide walks you through every realistic scenario so you stop guessing.

Basic Syntax and Where to Place the Operator

The cleanest way to use the does not equal sign in Excel is as a standalone comparison: =A1<>B1. Type that in any empty cell. If A1 and B1 hold different values, the cell returns TRUE. If they match, FALSE. That is it. No quotes, no equal sign before the angle brackets, no spaces between them.

Things get more interesting when you wrap the operator inside other functions. The pattern stays the same — left value, <>, right value — but you also need quotes around literal text. Numbers go bare. Cell references stay bare. Text strings need quotes. That single rule trips up beginners more than anything else.

Here are the four most common contexts where the operator appears, and they cover roughly 95% of real-world spreadsheets you will ever build or audit.

Using <> Inside an IF Function

This is the workhorse pattern. You want Excel to do one thing when two values differ, and something else when they match. Maybe flag rows where the budget does not equal the actual spend. Or highlight invoices where the billing address is different from the shipping address. The IF formula handles it.

Type this in any cell: =IF(B2<>C2,"Mismatch","OK"). When B2 and C2 are different, Excel writes "Mismatch". When they are identical, "OK". You can extend this to nested IFs, IFS, or SWITCH — but the comparison logic stays the same.

One thing worth flagging early: Excel treats text comparisons as case-insensitive by default. So ="Apple"<>"apple" returns FALSE — Excel says they are equal. If you need case-sensitive logic, swap in the EXACT function and wrap it: =IF(NOT(EXACT(A2,B2)),"Different","Same"). That is a small detail that catches even experienced users off guard.

Counting Cells That Do Not Equal a Value

COUNTIF with <> is one of the most useful combinations in everyday Excel work. Say you have a column of order statuses — Approved, Pending, Cancelled, Refunded — and you want to count everything that is not Cancelled. The formula is straightforward: =COUNTIF(A2:A100,"<>Cancelled").

Notice the structure. The criteria is a single string. The operator and the value both go inside the same set of double quotes. No spaces. No concatenation needed if the value is a literal. Excel parses it correctly because COUNTIF expects exactly this format for criteria arguments.

But what if you want to count cells that do not equal a value stored in another cell? You need to concatenate. The pattern becomes =COUNTIF(A2:A100,"<>"&D1). The ampersand glues the operator to the value pulled from D1. Forget the ampersand and Excel either errors out or returns a wrong count, because it thinks you are looking for literal text "<>D1".

The same logic applies to COUNTIFS for multi-criteria counts. Each criteria pair handles its own operator. So you might write =COUNTIFS(A:A,"<>Cancelled",B:B,">100") to count rows where status is not Cancelled and amount exceeds 100. Powerful, readable, and reliable once you get the quoting right.

Summing Cells That Do Not Equal a Value

SUMIF is COUNTIF's cousin, and it follows nearly the same rules. The big difference is you need three arguments: the range to check, the criteria, and the range to add up. Want to total all sales that are not from the "Returns" category? Try =SUMIF(A2:A1000,"<>Returns",B2:B1000).

SUMIFS handles multiple conditions. The argument order flips — sum range first, then pairs of criteria range and criteria. So =SUMIFS(C:C,A:A,"<>Returns",B:B,">=2024-01-01") sums column C where column A is not Returns and column B is on or after January 1, 2024. Order matters here. Mix it up and Excel either errors or returns nonsense.

People often ask why SUMIF gives zero when they think it should not. Nine times out of ten, the criteria range and the sum range are different sizes, or the data has trailing spaces. Excel sees "Returns " (with a space) as different from "Returns" — which is technically correct behavior, but it produces unexpected results. TRIM the data first, or use wildcards if you need fuzzy matching.

The Operator Inside Array Formulas and Boolean Math

You can multiply TRUE/FALSE results by 1 to get binary counts, and that opens up array tricks that COUNTIF cannot handle. The formula =SUMPRODUCT((A2:A100<>"")*(B2:B100<>"Cancelled")) counts rows where column A is not empty and column B is not "Cancelled". This is faster than nested IF arrays in older Excel versions, and it works in every version since Excel 2007.

Modern Excel — anything from Microsoft 365 or Excel 2021 — also supports dynamic arrays. So you can write =ROWS(FILTER(A2:A100,B2:B100<>"Cancelled")) to count rows where B is not Cancelled, with cleaner syntax. The FILTER function pulls back every row meeting the condition, and ROWS counts what came back.

For boolean-driven calculations, remember that <> returns TRUE or FALSE, and Excel treats TRUE as 1 and FALSE as 0 in math operations. So =SUM((A2:A100<>100)*1) entered as an array formula (Ctrl+Shift+Enter in older versions) counts how many cells do not equal 100. Useful when you want to embed the logic inside a larger formula.

Conditional Formatting with the <> Operator

One of the cleanest uses for the does not equal operator is highlighting differences between two columns. Suppose you have an expected column in A and an actual column in B. Select A2:B100, open Conditional Formatting, choose "New Rule", pick "Use a formula", and enter =$A2<>$B2. Apply a fill color, click OK. Now every row where A and B differ jumps off the page.

The dollar signs lock the columns but let the row numbers shift, so the rule walks down the range checking each row independently. If you forgot the dollar signs, the rule would only check row 2 across the entire range — clearly not what you want.

You can also highlight cells that do not equal a specific value. Select the range, create a formula rule, and use =A2<>"Approved". Every cell where the value is not "Approved" gets the format. This is faster than filtering manually, and the formatting updates automatically when the data changes.

Comparing Dates, Numbers, and Errors with <>

Date comparisons work the same way as numbers because Excel stores dates as serial numbers under the hood. So =A2<>DATE(2025,1,1) returns TRUE if A2 is any date other than January 1, 2025. The date function builds a proper serial number, which Excel compares cleanly. Avoid typing dates as text strings inside the formula — that introduces locale issues and false negatives.

Numbers stored as text are a common gotcha. If column A has 100 as a number and column B has "100" as text (because of a CSV import or a leading apostrophe), =A2<>B2 returns TRUE — Excel says they are different. Fix this by running VALUE() on the text column, or use Text to Columns to convert in bulk.

For error values like #N/A or #DIV/0!, the operator propagates the error. So =A2<>B2 returns #N/A if either cell holds an error. Wrap with IFERROR if you need to handle this gracefully: =IFERROR(A2<>B2,"Check inputs"). That way the formula returns your fallback message instead of an error code.

Real-World Examples That Use the Does Not Equal Operator

Let me walk you through a few realistic scenarios where you would actually reach for <> in everyday work. These are the kinds of things that pop up in financial reports, HR spreadsheets, inventory tracking, and customer data audits. Once you see the pattern, you will start spotting opportunities all over your own workbooks.

Scenario One: Counting Active Customers

Imagine you have a customer table with a Status column. Statuses include Active, Inactive, Pending, and Churned. You want a quick count of everyone who is not Churned. The formula is =COUNTIF(D2:D5000,"<>Churned"). That single cell now updates automatically every time the Status column changes. Drop it into a dashboard, and you have a live count of every customer who has not officially left.

If you also want to exclude Inactive customers from the same count, switch to COUNTIFS or wrap a SUMPRODUCT around two conditions. The COUNTIFS version reads =COUNTIFS(D2:D5000,"<>Churned",D2:D5000,"<>Inactive"). Both conditions must be TRUE for the row to count. That gives you the population of currently engaged customers — Active plus Pending — without the noise.

Scenario Two: Summing Sales Excluding Refunds

Sales reports almost always have a Returns or Refund category that you want to subtract out — or simply exclude — to see clean revenue. Suppose column A has the transaction type and column B has the dollar amount. The formula =SUMIF(A2:A2000,"<>Refund",B2:B2000) adds up every row where the type is not Refund. Quick. Clean. No helper column needed.

If you want to layer in a date filter — say, only transactions from the current quarter — SUMIFS takes over. The pattern becomes =SUMIFS(B:B,A:A,"<>Refund",C:C,">="&DATE(2025,1,1),C:C,"<"&DATE(2025,4,1)). Notice how each criteria pair handles its own operator and its own column. SUMIFS is verbose but reliable.

Scenario Three: Flagging Mismatched Records

You imported a CSV and now you want to compare it against your master list. Both files share a key column — say, customer email — and a verification column. If the verification status differs between sources, you want a flag. Put the master in columns A and B, the import in columns C and D. In column E, drop =IF(B2<>D2,"Mismatch","OK") and copy down. Now every row that does not agree gets flagged.

You can extend this with conditional formatting to color the mismatched rows. Select A2:E5000, create a formula rule using =$B2<>$D2, and assign a red fill. The rows light up automatically wherever the two sources disagree. This is the bread and butter of data audits.

Performance Notes for Big Spreadsheets

The <> operator itself is fast. Excel evaluates it in nanoseconds. The slowdown, when it happens, comes from the function wrapping the operator — usually COUNTIF or SUMIF running across hundreds of thousands of rows. If your workbook starts feeling sluggish, the fix is rarely to swap out <>. Instead, look at how you are referencing the range.

Using full-column references like A:A forces Excel to scan all 1,048,576 rows whether you have data there or not. That is a lot of empty cells. Switching to explicit ranges like A2:A50000 can cut calculation time dramatically, especially on older machines. Excel's modern smart recalculation helps, but explicit ranges still win for predictable performance.

Another option for massive datasets is to convert your data to a proper Excel Table — Insert tab, Table button, or Ctrl+T. Table references like Customers[Status] automatically size to the data, so you get the performance of an explicit range with the convenience of a full-column reference. Plus formulas auto-fill when you add new rows, which is worth the conversion all on its own.

For workbooks that crunch millions of rows, consider Power Query or Power Pivot rather than fighting with COUNTIF and SUMIF. Both tools handle <>-style filters far more efficiently and let you build proper data models. But for everything up to a few hundred thousand rows, the classic operator approach is still the right call.

Putting the <> Operator to Work

The does not equal operator is one of those tiny pieces of Excel that punches far above its weight. Two angle brackets. That is the whole symbol. Yet you can use it to build mismatch audits, exclusion totals, conditional formatting that catches bad data, and data validation rules that block forbidden entries. Once you internalize the quoting rules and the blank-versus-empty-string gotcha, the operator becomes second nature.

If you are still getting unexpected results, the fix is almost always one of three things. First, check your quotes — text needs them, numbers do not. Second, check for trailing spaces or numbers stored as text by running TRIM() and VALUE(). Third, look for blanks pretending to be empty strings — ISBLANK() and LEN() will tell you which is which. Walk through those three, and 95% of your <> problems vanish.

Beyond the basics, mixing the operator with SUMPRODUCT, FILTER, and dynamic array functions opens up patterns that simply were not possible in older Excel versions. So if you have access to Microsoft 365 or Excel 2021, lean into the array-friendly functions. They make criteria like "not equal to any of these values" much easier to express than legacy COUNTIFS chains.