How to Use Not Equal To in Excel: Operator, Formulas and Examples

shape
shape
shape
shape
shape
shape
shape
shape
How to Use Not Equal To in Excel: Operator, Formulas and Examples - featured image

The not equal to operator in Excel is written as a less-than sign followed by a greater-than sign, like this: <>. It returns TRUE when two values are different and FALSE when they are the same. You will use it inside IF formulas, in COUNTIF and SUMIF criteria, in FILTER, in conditional formatting and in data validation. This guide explains how the operator works, gives worked examples with a small sales table that you can check by hand, shows how to test for blank and non-blank cells, how to combine several not-equal conditions with AND and OR, how to exclude values from totals, and the mistakes that cause wrong counts, such as quotation marks around numbers and hidden spaces.

Updated November 2026

In this guide

  1. The sample data
  2. The basic not equal formula
  3. Not equal inside an IF formula
  4. Not equal in COUNTIF
  5. Not equal in SUMIF and SUMIFS
  6. Blank and non-blank cells
  7. Multiple not-equal conditions with AND and OR
  8. Not equal in FILTER
  9. Conditional formatting with not equal
  10. Compare two columns for differences
  11. Data validation with not equal
  12. Worked example: flag orders that need review
  13. Not equal across sheets and files
  14. Quick recap
  15. Practice problems
  16. Common mistakes

The sample data

The examples use this table in A1:C7.

RegionRepAmount
EastAli1200
WestSara800
EastOmar1500
NorthHina600
WestBilal950
EastZain700

The basic not equal formula

Type =A2<>"East" in a cell. If A2 holds West, the result is TRUE, because West is not East. If A2 holds East, the result is FALSE. You can compare numbers too: =C2<>1200 returns FALSE for Ali, since the amount is 1200, and TRUE for everyone else. You can also compare two cells, such as =A2<>B2. Text comparisons are not case sensitive, so East and EAST are treated as equal. If you need a case-sensitive test, use =NOT(EXACT(A2,"East")).

Not equal inside an IF formula

The operator is most often used in IF. =IF(A2<>"East","Other region","East") returns East for the East rows and Other region for the rest. Another example is a status check: =IF(C2<>0,"Has sales","No sales"). You can often write the same logic with equal to and swap the results, so =IF(A2="East","East","Other region") gives the same answer. Choose the version that is easier to read. See our IF statements guide for more examples, including nested IFs.

Not equal in COUNTIF

In COUNTIF, the operator goes inside quotation marks with the value: =COUNTIF(A2:A7,"<>East"). For the sample data this returns 3, the number of rows that are not East: West, North and West. To use a cell reference, join the operator and the cell with an ampersand: =COUNTIF(A2:A7,"<>"&E1), where E1 holds the region name. For numbers, =COUNTIF(C2:C7,"<>800") returns 5, since only Sara has 800. Remember that the criteria are always text in COUNTIF, and the operator is part of that text.

Not equal in SUMIF and SUMIFS

To total the amounts for everything except East, use =SUMIF(A2:A7,"<>East",C2:C7). The result is 2,350, the sum of 800, 600 and 950. Check: the total of all amounts is 5,750, and East is 1,200 plus 1,500 plus 700, which is 3,400, so 5,750 minus 3,400 is 2,350. For several conditions, use SUMIFS: =SUMIFS(C2:C7,A2:A7,"<>East",C2:C7,">700") adds amounts above 700 that are not in East, which gives 800 plus 950, or 1,750. See our SUMIFS guide and COUNTIFS guide for the full syntax.

Blank and non-blank cells

To test for a cell that is not empty, compare it with an empty text: =A2<>"" returns TRUE when the cell has any content. In COUNTIF, =COUNTIF(A2:A100,"<>") counts non-blank cells, and =COUNTIF(A2:A100,"") counts blank cells. Be careful with cells that contain a formula returning empty text, since they look blank but are not empty. A cell with only a space also counts as not blank. Clean those first, as shown in our guide to removing spaces in Excel. A common use is a formula that stays quiet until data is entered: =IF(B2<>"",B2*C2,"").

Multiple not-equal conditions with AND and OR

To exclude two values, use AND: =IF(AND(A2<>"East",A2<>"West"),"North only",""). A row is shown as North only when it is neither East nor West. Using OR here would be a mistake, since a value that is not East is still TRUE for the second test, and OR would return TRUE for almost every row. The rule is that to exclude several values you need AND, and to include several values you need OR. For the sample data, only the Hina row returns North only. For a long list of excluded values, use =ISNA(MATCH(A2,{"East","West"},0)), which returns TRUE for values that are not in the list.

Not equal in FILTER

In Microsoft 365, use the operator to return the rows that do not match: =FILTER(A2:C7,A2:A7<>"East") returns Sara, Hina and Bilal. You can combine it with another test by multiplying: =FILTER(A2:C7,(A2:A7<>"East")*(C2:C7>700)) returns Sara 800 and Bilal 950. See our FILTER function guide for more. The result updates automatically when the data changes.

Conditional formatting with not equal

To highlight the rows that are not East, select A2:C7, go to Home, Conditional Formatting, New Rule, and choose Use a formula to determine which cells to format. Enter =$A2<>"East", choose a fill color and click OK. The dollar sign before A locks the column, so the whole row is shaded when column A is not East. To highlight cells where two columns differ, use =$B2<>$C2. This is a quick way to compare two lists, such as an old price column and a new price column, and spot every change at a glance.

Compare two columns for differences

Put the old prices in column B and the new prices in column C. In D2 use =IF(B2<>C2,"Changed","Same") and fill down. Filter column D for Changed to see all the differences. For text lists, use =IF(COUNTIF($B$2:$B$100,C2)=0,"Missing in old list","Found") to flag items that do not appear in the other list. To count the number of differences, use SUMPRODUCT: =SUMPRODUCT(--(B2:B100<>C2:C100)). The double minus signs turn TRUE and FALSE into 1 and 0, so the formula returns the count of rows that differ.

Data validation with not equal

You can block a value with data validation. Select the cells, go to Data, Data Validation, choose Custom, and enter =A2<>"N/A". Now the user cannot type N/A in those cells. To stop duplicates in a column, use =COUNTIF($A$2:$A$100,A2)=1 instead. Add an error alert message that explains the rule, so users understand why the entry is rejected. Validation stops typing, but it does not stop pasting, so check the data after a large paste.

Worked example: flag orders that need review

A client wants to review every order that is not from the home region and that has an amount different from the standard value of 1,200. Add a column D with the formula =IF(AND(A2<>"East",C2<>1200),"Review","") and fill it down. For the sample data, the rows for Sara 800, Hina 600 and Bilal 950 are flagged, while the three East rows are not, because East is the home region. Then count the flags with =COUNTIF(D2:D7,"Review"), which returns 3. Add conditional formatting to shade the flagged rows, and a drop-down to let the reviewer mark each row as Done, as shown in our drop-down list guide. Finally, filter column D to show only the Review rows. This small workflow uses the not equal operator three times, in IF, AND and COUNTIF, and it shows how the operator turns a plain list into a working checklist for a team.

Not equal across sheets and files

You can compare a cell on one sheet with a cell on another, such as =Sheet1!A2<>Sheet2!A2. This is helpful for checking a new version of a report against the old one. If the sheets are large, use SUMPRODUCT to count all differences in one formula, and then use conditional formatting to find them. Make sure that both ranges have the same size and the same sort order, since a different order makes every row look different. When the comparison is between two files, copy one sheet into the other workbook first, so the formulas do not depend on an external link that can break.

Quick recap

Use the not equal operator to test that two values differ. Put it in quotation marks inside COUNTIF and SUMIF, use AND to exclude several values, compare with empty text to test for non-blank cells, and use it in FILTER and conditional formatting to show what does not match. Keep numbers out of quotation marks in plain comparisons, and clean hidden spaces first.

Practice problems

Try these on the sample data. Problem 1: count the rows where Region is not West. The answer is 4. Problem 2: total the amounts where Region is not North. The answer is 5,750 minus 600, which is 5,150. Problem 3: use IF to label rows as Big when the amount is not below 1,000. That is, Ali and Omar. Problem 4: use FILTER to list reps who are not Ali. The answer is five rows. Problem 5: use AND to find rows that are neither East nor North. The answer is the two West rows. Check each by scanning the table.

Common mistakes

The most common mistake is to put the numbers in quotation marks inside a normal comparison, such as =C2<>"1200", which compares a number with text and is always TRUE. Another is to use OR when AND is needed to exclude several values. A third is to forget the quotation marks around the operator in COUNTIF and SUMIF. A fourth is to ignore hidden spaces, so that East with a trailing space is not equal to East. A fifth is to test for blanks with =A2=0, which is also TRUE for real zeros, so it cannot tell an empty cell from a zero. Use TRIM, and compare with an empty string.

Frequently asked questions

What is the not equal operator in Excel?

It is a less-than sign followed by a greater-than sign, which returns TRUE when two values are different.

How do I use not equal in COUNTIF?

Put the operator in quotation marks with the value, such as the criteria for not East.

How do I check that a cell is not blank?

Compare the cell with an empty text in quotation marks using the not equal operator, or use COUNTIF with the not equal operator and no value.

How do I exclude several values?

Use AND with several not-equal tests.

Is the not equal test case sensitive?

No. Use NOT with EXACT for a case-sensitive test.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *