contact@consultdmw.com

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:

Excel greater-than formula

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.

Excel less-than formula

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:

Excel equal-to 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 greater-than or equal-to formula

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 less-than formula or equal-to

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.

Excel not-equal-to formula

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) —

“For the lesser evil is reckoned a good in comparison with the greater evil, since the lesser evil is rather to be chosen than the greater.”

Aristotle, Nicomachean Ethics