Search
Close this search box.

How To Use “Does Not Equal” in Excel [Easy 10 Min Guide]

“Does not equal” in Excel is usually expressed as an operator (<>). It is often used in combination with Excel functions and opens up a world of possibilities for data analysis. With this operator, you can easily identify and count cells that do not match a specific value or condition, allowing you to gain insights, make informed decisions, and troubleshoot data inconsistencies.

This comprehensive guide will walk you through the various ways to leverage the “does not equal” operator in Excel. Whether you are a beginner or an experienced user, this guide will equip you with the knowledge and techniques to effectively employ this operator in your data analysis tasks. Read on to learn how to use “does not equal” in Excel.

How To Use “Does Not Equal” in Excel

  1. In the cell you want to return the results, type the equals sign (=)
  2. Select the cell you want to compare.
  3. Type the “does not equal” sign (<>).
  4. In quotation marks, write the text or value you want to compare to. You can also use a cell reference instead.
  5. Click “Enter.”
  6. Copy the formula to the rest of the column by dragging the square at the bottom right corner of the cell.

Table of All Excel Logical Operators

Excel is one of the most powerful spreadsheet software suites in the market, and there are a number of different logical operators in Excel, including the following:

Operator Description
= Equal to
<> Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to

 

These operators allow you to build formulas, conditional statements, and logical expressions to make decisions, perform calculations, and manipulate data.

Common Uses of the Does Not Equal Logical Operator in Excel

The “does not equal to” logical operator (<>) in Excel is commonly used for multiple purposes, such as:

1. Conditional Formatting

The “does not equal to” (<>) operator is frequently used in conditional formatting to highlight cells or ranges that do not equal a specific value. For instance, you might want to apply formatting to cells that are not equal to zero or not equal to a particular text string.

2. Filtering and Data Analysis

When working with data in Excel, the “does not equal to” operator is useful for filtering and analyzing data. You can use it to filter out or exclude specific values from a dataset based on certain criteria. For example, you may want to filter out all rows that do not have a specific category or exclude values that are not within a certain range.

3. Logical Functions

The “does not equal to” operator is commonly used within logical functions like IF, COUNTIF, SUMIF, and others. It allows you to create conditions and perform calculations based on values that are not equal to a particular criterion.

For example, you can use the COUNTIF function with the (<>) operator to count the number of cells that do not equal a specific value.

4. Data Validation

In Excel, the “does not equal to” operator is also valuable in data validation. You can set up validation rules that enforce certain conditions using the (<>) operator. This ensures that users enter values that are not equal to a specific value or follow specific criteria.

How To Use the “Does Not Equal” Operator in Excel

The “does not equal” operator is represented by the sign (<>) in Excel. The formula usually returns TRUE or FALSE depending on the comparison results.

In this guide, we’ll be using the following worksheet for our examples:

Does not equal in Excel—Excel table with entered data

In our example worksheet, we can use “does not equal to” find workers in the Manufacturing department. Here’s how:

  1. In the cell you want to return the results, type the equals (=) sign.
  2. Select the cell you want to compare.
Excel cells to compare

 

  1. Type the “Does not equal” (<>) sign.
Does not equal sign in an Excel cell

 

  1. In quotation marks, write the text you want to compare to. You can also use a cell reference instead. In our example, the word is “Manufacturing
Comparing cell data in Excel

 

  1. Click “Enter.”
  2. Copy the formula to the rest of the column by dragging the square at the bottom right corner of the cell.

The whole formula we used for this example is:

=B2<>"Manufacturing"
Entered data in Excel after using "does not equal"

This formula returns a TRUE if the value in column B is not Manufacturing.

How To Use “Does Not Equal” with Other Functions

You can also get more functionality out of the “does not equal” (<>) operator by using it together with other functions like OR, AND, and IF.

Using “Does Not Equal” with OR

Using the “does not equal” (<>) operator with OR lets you use two conditions instead of one. The formula will then return TRUE if either one of the conditions is met.

In our example worksheet, we can compare columns B and C at the same time and return TRUE if one of the two conditions matches.

Here’s how:

  1. In the cell type, type the equals (=) sign and the OR function.
Equals sign with OR in Excel

 

  1. Select the cell for the first condition.
  2. Add the “does not equal” (<>) sign.
Add the "does not equal" sign to an Excel cell.

 

  1. Write the comparison value in quotes, or you can use a cell reference.
Comparison values in an Excel spreadsheet

 

  1. Add a comma, then select the cell for the second condition.
Selecting the cell for the second condition in Excel

 

  1. Add the “does not equal” (<>) sign.
Adding the "does not equal" sign to a cell

 

  1. Write the comparison value in quotes, or you can use a cell reference.
Writing the comparison cell value

 

  1. Close the bracket and click “Enter.”
Excel formula using "does not equal to" and OR function

 

  1. Copy the formula to the rest of the columns.
Copied Excel formula in multiple cells using OR function

 

The formula in this example is:

=OR(B2<>"Manufacturing",C2<>"California")

This formula returns TRUE if either the cell in column B has Manufacturing or the cell in column C has “California.” In this example, only the second last row returns FALSE since it has both Manufacturing and California.

Using “Does Not Equal” with AND

Using “does not equal” with the AND function also lets you use two conditions in your formula, like OR. The only difference is that with AND, the formula will only return TRUE if both conditions are met.

  1. In the cell, type the equals (=) sign and the AND function.
Adding the AND function in an Excel cell

 

  1. Select the cell for the first condition.
Selecting the first condition with AND in Excel

 

  1. Add the “does not equal” (<>) sign.
  2. Write the comparison value in quotes, or you can use a cell reference.
Adding the comparison value in quotes in an Excel cell

 

  1. Add a comma, then select the cell for the second condition.
Selecting the cell for the second condition using the AND function

 

  1. Add the “does not equal” (<>) sign.
  2. Write the comparison value in quotes, or you can use a cell reference.
Using the AND function with "does not equal to"

 

  1. Hit “Enter.”
"Does not equal" used with AND function in Excel

 

  1. Copy the formula to the rest of the columns.
"does not equal" with AND function formula used in multiple cells

The formula in this example is:

=AND(B2<>"Manufacturing",C2<>"California")

This formula returns TRUE only if both conditions are met. In this example, only two cells don’t have both the words Manufacturing and California. These two rows are the only ones to return TRUE.

How to Use “Does not Equal” with the IF Function

We can also have the “does not equal” (<>) operator returns other values other than TRUE and FALSE. To do this, we need to include the IF function.

The syntax for using “does not equal” with the IF function is as follows:

=IF(value1 <> value2, action_if_true, action_if_false)
  • value1 and value2 represent the values you want to compare. You can use the “does not equal” operator (<>) between these two values to determine if they are unequal.
  • action_if_true is the action or result you want to execute if the inequality comparison is true (i.e., value1 is not equal to value2).
  • action_if_false is the action or result you want to execute if the inequality comparison is false (i.e., value1 is equal to value2).

In our example, we can find workers who don’t work in California.

Here’s how:

  1. In the cell, type the equals (=) sign and the IF function.
Excel cell with equal signs and the IF function.

 

  1. Select the cell you want to compare.
Two cells selected for comparison

 

  1. Add the “does not equal” (<>) sign.
“does not equal” sign in an Excel cell

 

  1. Write the comparison value in quotes, or you can use a cell reference.
Comparison value in quotes using the IF function

 

  1. Add a comma, then write the value to return if TRUE. In our example, we’ll use YES.
A cell with the value of TRUE

 

  1. Add a comma, then write the value to return if FALSE. In our example, we’ll use NO.
An Excel cell with the value to return of FALSE

 

  1. Close the brackets and click “Enter.”
Excel brackets with the complete formula

 

  1. Copy the formula to the rest of the columns.
Copied formula using IF in multiple cells

The formula in this example is:

=IF(C2<>"California", "YES","NO")

The formula returns Yes if the worker is not in California and No if they are.

Using “Does Not Equal” in Conditional Formatting

You can go one step further and use “does not equal” conditional formatting in Excel. Using the “does not equal” operator (<>) with conditional formatting in Excel allows you to visually highlight cells or apply specific formatting based on inequality comparisons.

This feature is particularly useful when you want to draw attention to cells that do not match a particular value or condition.

To apply conditional formatting based on the “not equal to” condition, follow these steps:

  1. Select the range of cells you want to apply the formatting to.
Excel cells with already inputted data that's ready to be formatted

 

  1. Go to the “Home” tab on the Excel ribbon.
  2. Click on “Conditional Formatting” in the “Styles” group.
Conditional Formatting button in Excel

 

  1. Choose “Highlight Cells Rules.”
Highlight Rules button in Excel

 

  1. Choose “More options.”
More options tab in Excel

 

  1. In the new window, go to “Edit rule,” and in the second drop-down, choose “not equal to.”
Adding a new formatting rule in Excel

 

  1. Add the comparison value that you want to compare against in the last text box. In our example, the word is “New York.
Adding a new rule for "New York" in Excel

 

  1. Select “Format.”
  2. Click the “Fill” tab.
The "Fill" tab in Excel

 

  1. Choose the color you want to use to highlight.
  2. Click “OK.”
Highlighted cells in Excel

 

This will highlight the cells that are not equal to the specified value or cell reference. In our example, the cells in column C without the word New York have been highlighted in green.

Frequently Asked Questions

How Do You Write Not Equal in Excel?

In Excel, the “not equal to” operator is represented by the symbol (<>). This operator is used to compare two values and determine if they are not equal.

For example, suppose you want to compare the values in cells A1 and B1 and check if they are not equal. In another cell, you would enter the formula:

=A1<>B1

Can You Use “Does Not Equal” with Multiple Values in Excel?

In Excel, you can use the “does not equal to” operator (<>) to compare a single value with multiple values. However, you would need to use multiple comparison operations to achieve this.

One way to do this is by using the AND function or the OR function. You can then create two conditions for the same value.

For example, the formula below can be used to check if the cell has either of the two words and return TRUE if it does not.

=AND(B2<>"Manufacturing",B2<>"Sales")

Wrapping Up

The flexibility of the “does not equal” operator allows you to tackle a wide range of data analysis tasks. You can apply it to compare values, exclude certain conditions, or perform conditional calculations. You can even use it in conditional formatting to highlight cells based on a condition.

In this article, we’ve shown you how to use “does not equal” in Excel in different scenarios, including conditional formatting. You can learn more about Excel tools and functions by checking out the guide for the best Excel courses or any of our related guides below.

If you found our content useful and would like to support our site, check out our premium template library, where you can save 50% with the code SSP at checkout.

Get Premium Templates

Related:

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!