Comparison operators are very helpful in Excel to force a Boolean result from a formula. Once we test two values against each other, we can return a desired result based on whether the outcome is TRUE or FALSE.
Here is a list of the most common comparison operators:
- Equal Sign (=): Checks if two values are equal to each other. Returns TRUE if values are EQUAL
- Not Equal (<>): Checks if two values are not equal to each other. Returns TRUE if values are NOT equal.
- Less Than (<): Checks if first value is less than second value. Returns TRUE if first value is less than second value.
- Greater Than (>): Checks if first value is greater than second value. Returns TRUE if first value is greater than second value.
Example:
What’s the Point?
You may be wondering why it would ever be useful to compare numbers in this way. Even if it is useful, what value beyond TRUE/FALSE can you extract from this comparison?
Below we have two data sets. Sales by Month are broken out. The first data set was pulled internally by your team, but the second set of data was pulled by 3rd party marketing analysts who are helping your company improve its sales strategy. Are the two data sets equal? We’ll find out…
Hard to tell at first glance… Is the data in column A – C equal to E – G?
Next, we’ll add a column that compares the sales figures from the first pull to the second. The format is:
=CELL1=CELL2
After applying the formula across the entire data set, we can clearly see that there are two values that do NOT match the original pull of data that your team did.
Although this may not be exactly the type of data you’re working with, it should be obvious that comparison operators can be very helpful in comparing data sets, especially across dozen, hundreds, or thousands of rows.