“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.
Table of Contents
How To Use “Does Not Equal” in Excel
- In the cell you want to return the results, type the equals sign (=)
- Select the cell you want to compare.
- Type the “does not equal” sign (<>).
- In quotation marks, write the text or value you want to compare to. You can also use a cell reference instead.
- Click “Enter.”
- 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:
In our example worksheet, we can use “does not equal to” find workers in the Manufacturing department. Here’s how:
- In the cell you want to return the results, type the equals (=) sign.
- Select the cell you want to compare.
- Type the “Does not equal” (<>) sign.
- 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”
- Click “Enter.”
- 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"
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:
- In the cell type, type the equals (=) sign and the OR function.
- Select the cell for the first condition.
- Add the “does not equal” (<>) sign.
- Write the comparison value in quotes, or you can use a cell reference.
- Add a comma, then select the cell for the second condition.
- Add the “does not equal” (<>) sign.
- Write the comparison value in quotes, or you can use a cell reference.
- Close the bracket and click “Enter.”
- Copy the formula to the rest of the columns.
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.
- In the cell, type the equals (=) sign and the AND function.
- Select the cell for the first condition.
- Add the “does not equal” (<>) sign.
- Write the comparison value in quotes, or you can use a cell reference.
- Add a comma, then select the cell for the second condition.
- Add the “does not equal” (<>) sign.
- Write the comparison value in quotes, or you can use a cell reference.
- Hit “Enter.”
- Copy the formula to the rest of the columns.
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:
- In the cell, type the equals (=) sign and the IF function.
- Select the cell you want to compare.
- Add the “does not equal” (<>) sign.
- Write the comparison value in quotes, or you can use a cell reference.
- Add a comma, then write the value to return if TRUE. In our example, we’ll use YES.
- Add a comma, then write the value to return if FALSE. In our example, we’ll use NO.
- Close the brackets and click “Enter.”
- Copy the formula to the rest of the columns.
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:
- Select the range of cells you want to apply the formatting to.
- Go to the “Home” tab on the Excel ribbon.
- Click on “Conditional Formatting” in the “Styles” group.
- Choose “Highlight Cells Rules.”
- Choose “More options.”
- In the new window, go to “Edit rule,” and in the second drop-down, choose “not equal to.”
- Add the comparison value that you want to compare against in the last text box. In our example, the word is “New York.”
- Select “Format.”
- Click the “Fill” tab.
- Choose the color you want to use to highlight.
- Click “OK.”
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.
Related:
