‘Does Not Equal’ Operator in Excel (Examples)

The Does Not Equal (<>) operator is a comparison operator in Excel that returns TRUE if the first value is not identical to the second value; otherwise, it returns FALSE. 

The example dataset below illustrates how the Does Not Equal operator works.

In the example dataset, the Does Not Equal operator compares the values in column A to those in column B.

If the values are not identical or equal, the formula returns TRUE, and if the values are the same, the procedure returns FALSE.

The Not-equal-to operator (<>) is the opposite of the Equal-to operator (=), which is used to compare values and see if they are the same or not

The Does Not Equal operator can be combined with conditional functions in Excel to automate data computation.

We can also use the operator in conditional formatting procedures and Excel VBA code.

This tutorial shows seven examples of using the Does Not Equal operator to achieve specific results in Excel. 

Example #1: Use the Does Not Equal Operator With the IF Function

We can use the Does Not Equal operator to formulate a condition for the IF function.

Remember, the IF function lets us test whether a condition is met and returns TRUE if the condition is met and returns FALSE if the condition is not met.  

Let’s consider the following dataset showing students’ grades in a Math test. 

A student is considered to have failed the test if they attained an E grade. 

Therefore, we want to enter a formula in column C that will return “Failed” if the student achieved an E grade; otherwise, return “Passed.”

We use the below steps:

1. Select cell C2 and type in the formula below:

=IF(B2<>"E","Passed","Failed")

Comment: Notice that the Does Not Equal operator has been used to test that the value in cell B2 is not equal to “E.”

2. On the Formula bar, click the Enter button to enter the formula.

3. Drag down or double-click the fill handle to apply the formula to the other cells in the column.

Explanation of the formula

=IF(B2<>”E”,”Passed”,”Failed”)

• B2<>”E” – The value A in cell B2 is not equal to E; therefore, the Does Not Equal operator returns TRUE.
• The formula resolves to =IF(TRUE,”Passed”,”Failed”) and returns “Passed.”

Also read: Using Excel If Statement with Multiple Conditions Range

Example #2: Use the Does Not Equal Operator With the SUMIF Function

The SUMIF function adds the cells that meet a particular condition or criteria.

We can use the Does Not Equal operator to provide the criteria for the SUMIF function. 

Let’s look at the below dataset showing the sales of desktop computers of particular brands.

We want to add the sales numbers of all the brands except Apple and display the result in cell C9.

We use the following steps:

1. Select cell C9 and type in the below formula:

=SUMIF(B2:B6,"<>"&C8,C2:C6)

2. Press Enter.

The total sales figure for all the desktops, excluding the Apple brand, is displayed in cell C9.

Example #3: Use the Does Not Equal Operator With the COUNTIF Function

The COUNTIF function tallies the number of cells within a given cell range that meet a specific condition.

The Does Not Equal operator can be used to provide the condition needed by the COUNTIF function.

Let’s consider the following dataset showing popular tablet brands. 

We want to determine the number of popular tablet brands, excluding Amazon, and display the result in cell B4.

We proceed as follows:

1. Select cell B4 and type in the following formula:

=COUNTIF(A2:A11,"<>"&"Amazon")

2. Press Enter.

The number of all the brands except Amazon is displayed in cell B4.

Example #4: Use Does Not Equal Operator With AVERAGEIF Function

The AVERAGEIF function finds the mean of the cell range identified by a given condition or criteria.

The Does Not Equal operator can be used to determine the needed condition or criteria.

Presume we have the following dataset showing the salaries of particular employees in a company.

We want to find out the average salary of the employees who are not Branch managers.

We use the below steps:

1. Select cell E4 and type in the following formula:

=AVERAGEIF(B2:B9,"<>"&"Branch Manager",C2:C9)

2. Press Enter.

The average salary of all the employees who are not branch managers is displayed in cell E4.

Example #5: Use the Does Not Equal Operator With the COUNTIFS Function

The COUNTIFS function calculates the number of cells specified by a given set of criteria or conditions.

The Does Not Equal operator can supply the criteria or conditions. 

We have the following dataset showing popular tablet brands.

We want to calculate the number of popular tablet brands, excluding Amazon and HTC, and show the result in cell B4.

We use the following steps:

1. Select cell B4 and type in the below formula:

=COUNTIFS(A2:A11,"<>"&"Amazon",A2:A11,"<>"&"HTC")

2. Press Enter.

The number of popular tablet brands excluding Amazon and HTC, is displayed in cell B4.

Example #6: Use the Does Not Equal Operator With Conditional Formatting Feature

We may want to apply conditional formatting to a cell range to highlight an aspect of the data.

In this case, we can use the Does Not Equal operator in the formula to determine which cell to format. 

The following dataset shows employees’ names, designations, and salaries. 

We want to apply conditional formatting to highlight in yellow all the designations in column B that are not “Branch Manager.”

We use  the below steps:

1. Select cell range B2:B9.

2. On the Home tab, in the Styles group, open the Conditional Formatting drop-down and select the New Rule option.

3. Do the following on the New Formatting Rule dialog box that appears:

• On the Select a Rule Type box, select Use a Formula to determine which cells to format.
• In the Format values where this formula is true box, type the formula =B2<>”Branch Manager.” Notice the Does Not Equal operator in the formula.
• Click the Format button.

4. Open the Fill tab on the Format Cells dialog box that appears, choose a yellow background color, and click OK.

5. Click OK on the New Formatting Rule dialog box.

All the designations that are not “Branch Manager” are highlighted in yellow color:

Just like we have used the ‘does not equal to’ operator in Conditional Formatting, you can also use other operators such as Greater Than or Less Than

Also read: How to Remove Conditional Formatting in Excel?

Example #7: Use the Does Not Equal Operator in Excel VBA Procedures

The Does Not Equal operator can be used in If-Then constructs in Excel sub-procedures to evaluate conditions and determine what the code does.

For example, the following sub-procedure closes all inactive workbooks:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub CloseInactiveWbk()
    Dim Bk As Workbook
    For Each Bk In Workbooks
        If Bk.Name <> ActiveWorkbook.Name Then Bk.Close
    Next Bk
End Sub

Notice that the Does Not Equal operator is used in the code to compare each open workbook’s name with the active workbook’s name. 

If the name of the open workbook is not equal to the name of the active workbook, the inactive workbook is closed. 

In this tutorial, we looked at seven examples of using the Does Not Equal (<>) operator in Excel.

The Does Not Equal operator can be combined with conditional functions in Excel to automate calculations.

We can also apply the operator in conditional formatting and Excel VBA code.

Other Excel articles you may also like:

• How to Use Greater Than or Equal to Operator in Excel Formula?
• COUNTIF Greater Than Zero in Excel
• How to Count How Many Times a Word Appears in Excel (Easy Formulas)
• SUMPRODUCT vs SUMIFS Function in Excel
• How to Sum Positive Numbers in Excel
• How to Count Unique Values in Excel
• How to Find Duplicates in Excel (Conditional Formatting/ Count If/ Filter)