How To Use Comparison Operators In Excel

Six Comparison Operators

There are six comparison operators available for you to use in Excel.

These operators are used to test for conditions such as:

Equal: Two values or strings are the same (apple = apple)Greater Than: One value is larger than the other (10 > 8)Less Than: One value is smaller than the other (8 < 10)Greater Than or Equal To: One value is larger or the same as another (10 >= 10)Less Than or Equal To: One value is smaller than or the same as another (5 <= 5)Not Equal To: Two values are not the same (dog <> cat)

All comparison operators work with values, while some (such as <> and =) also work with strings (text) and dates.

Comparison Operators in the IF Function

There are two areas in Excel where you can use comparison operators. The most common usage is inside of the IF function.

Inside any cell of a spreadsheet, invoke the IF function by typing:

You’ll see pop-up help text that reads:

This is the format for using the IF function properly.

The first value is the conditional test that contains the comparison operator. The second value is the number or string you want displayed if the comparison is true. The third value is the number or string you want displayed if the comparison is false.

All three values inside the IF function should be separated with commas.

The logical test can reference either values or cells in the Excel spreadsheet that contain values. You can also nest formulas inside the comparison itself.

For example, to compare the data in cell A1 to the data in cell B4, type:

To check if the value in cell A1 is under 50, type:

To check whether the value in cell A1 is less than half the value in cell B4, type:

In the examples above, Excel returns either TRUE or FALSE in the cell where you’ve typed the IF statement, depending on the result of the comparison.

You can replace TRUE or FALSE with any value or string if you’d like the IF formula to return something else in that cell. For example:

This will return “Bob” in the cell if the condition is true, or “Sally” if the condition is false.

Comparison Operators in Excel VBA or Macros

You can use the same comparison operators inside of the Excel VBA editor.

Excel VBA is used to create macros for automating actions inside of a spreadsheet.

To open the Excel VBA editor:

Select File > Options > Customize Ribbon. Enable the Developer check box under Main Tabs and select OK. In Excel, select Developer > View Code. Double-click ThisWorkbook under Microsoft Excel Objects in the left pane. At the top of the code window, set the left drop-down to Workbook and the right one to Open.

You are now editing code that will run every time the Excel file is opened. In this window, you could compare cell A1 to A2, and automatically fill in A3 with a value or text depending on the comparison operator results.

Here is an example of what that code would look like:

The formatting is slightly different in VBA, but the comparison symbols (operators) used to compare two values or strings is exactly the same.