How to Use Logical Comparison Operators in Excel Formulas
Using the logical comparison operators represented by the individual characters ˃, ˂ and = and in the combinations ˂˃, ˃= and ˂= in Microsoft® Excel formulas.
Last updated on 2024-05-15.
The Logical Comparison Operators
If you need formulas to compare values in different cells in an Excel spreadsheet, then you can create them using logical comparison operators:
| Comparison | Operator | Action |
|---|---|---|
| Greater than | ˃ | If comparison formula is A˃B and value A is greater than value B, then formula returns TRUE. Otherwise, it returns FALSE. |
| Less than | ˂ | If comparison formula is A˂B and value A is less than value B, then formula returns TRUE. Otherwise, it returns FALSE. |
| Equal to | = | If comparison formula is A=B and value A is the same as value B, then formula returns TRUE. Otherwise, it returns FALSE. |
For additional functionality these operators can be paired :
| Comparison | Operator | Action |
|---|---|---|
| Greater than or equal to | ˃= | If comparison formula is A˃=B and value A is greater than value B or value A is equal to value B, then formula returns TRUE. Otherwise, it returns FALSE. |
| Less than or equal to | ˂= | If comparison formula is A˂=B and value A is less value B or value A is equal to value B, then formula returns TRUE. Otherwise, it returns FALSE. |
| Not equal to | ˂˃ | If comparison formula is A˂˃B and value A is not the same as value B, then formula returns TRUE. Otherwise, it returns FALSE. |
Example Excel Comparison Formulas
If your formula is to compare values of different types, always test it against a set of data fully representative of what your spreadsheet will meet in real life.
The examples shown here provide a smidgen of the application of comparison operators. Comparisons between numeric values shouldn't provide surprises.
However, you need to be cautious when making comparisons between text values and between dates.
Greater–Than Formula
In these examples, the formula compares two variables, Value A and Value B:
As a variation on the formula illustrated above, =A2˃4 is one in which the variable in cell A2 is compared with the fixed constant value 4.
To make a comparison with a fixed date, be wary of how you express that date. Excel stores a date as a number. So =A2˃2020-02-14 will not work as required; far from it. Best to use Excel's DATE function, I reckon: =A2˃DATE(2020,02,14).
A superior way to make comparisons with a constant value is to store that value in a cell — Cell Z2 say — on the spreadsheet. In which case =A2˃$Z$2 will do the job. Conveniently, there's no need to edit the formula if you need it to make comparisons with some other constant.
Less–Than Formula
In all the examples in this piece, I've tested the formulas applied to text values for the English alphabet only.
The Excel Not–Equal–To Formula section, below, has observations on formulas for comparing dates.
Equal–To Formula
On first reading, the profusion of equals signs in the formula might cause confusion. Regain your composure by dismissing the one on the left as simply Excel's own convention indicating that this cell's content is indeed a formula:
Greater–Than or Equal–To Formula
Excel recognises certain combinations of operators provided they are in a certain order. In this formula, Excel behaves; but give it A=˃B and … Excel will not let you off:
Excel Less–Than or Equal–To Formula
The topic How to Work With Dates in Excel Formulas is required reading if you're doing comparisons between data values.
Excel Not–Equal–To Formula
If your formulas are comparing text values, be aware that Excel's application of logical comparison operators does not distinguish between upper and lower case letters.
To test for case-sensitive comparisons, you dispense with comparison operators.
Using Excel's EXACT function will return TRUE when both text values it is fed with match in all respects, as in =EXACT(A2,B2) — a more exacting comparison than afforded by =A2=B2.
As the alternative to =A2<>B2, you need to flip the EXACT result by wrappiing it in a NOT function: =NOT(EXACT(A2,B2)).
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —