When you need to compare values in a spreadsheet, Google Sheets gives you the standard operators to do so. You can use functions if you prefer, but if the regular symbols are for you, here's how to use them.
You might want to see if a value is greater than, less than, or equal to another. This is especially helpful if you change your data often and need to keep track of these types of comparisons. Let's look at how to use comparison operators in Google Sheets for constants, cell references, and in mixed formulas.
Comparison Operators in Google Sheets
Think back to math class in elementary or middle school and those comparison symbols should pop into your mind. These same symbols are available for comparing values in Google Sheets.
- Equal To: =, equivalent to the EQ function
- Not Equal To: <>, equivalent to the NE function
- Greater Than: >, equivalent to the GT function
- Greater Than or Equal To: >=, equivalent to the GTE function
- Less Than: <, equivalent to the LT function
- Less Than or Equal To: <=, equivalent to the GTE function
When you compare values using these operators, the result is either True or False. And like any other formula in Google Sheets, you'll start with an equal sign.
Here are several examples.
To see if the value in cell A1 is equal to the value in cell B1, you can use this formula:
=A1=B1
To see if those same values are not equal to each other, you'd use this formula:
=A1<>B1
To see if the value in cell A1 is greater than 150, you can use this formula:
=A1>150
For one final example, to see if 200 is less than or equal to that in cell B1, use this formula:
=200<=B1
As you can see, the formulas are basic and easy to assemble. The structure is equal sign, value one, comparison operator, value two (without the commas).
Comparison Operators With Functions
You can use comparison operators in Google Sheets for more than comparing two values. You'll find that they come in handy when using functions and other types of formulas as well.
For instance, you might use COUNTIF in Google Sheets to count the number of cells that meet criteria. This is a perfect example of including a comparison operator.
With this formula, we can count the number of cells containing values greater than 10 in the cell range A1 through A5:
=COUNTIF(A1:A5,">10")
Using another criteria-based function, SUMIF, we can add the values in that same cell range if they are less than 10 with this formula:
=SUMIF(A1:A5,"<10")
Another function where you'll see comparison operators used is with the logical IF function. With this formula, we'll display the number 1 if the value in cell A1 is equal to No and the number 2 if it's not.
=IF(A1="No","1","2")
Comparison operators in Google Sheets give you a quick and easy way to compare values. If you prefer to use the comparison functions instead, check out our how-to for those.
And for working with other symbols in Google Sheets, look at how to use the ampersand (&) for adding text or for concatenating data.