Google Sheets Not Equal: Master the <> Operator in Formulas
Master the not-equal operator in Google Sheets with practical IF, FILTER, and data-validation examples. Learn why google sheets does not equal and avoid errors.
If you're wondering how to express google sheets does not equal, the standard operator is <>. Use it in logical tests like IF, FILTER, and COUNTIF to check that two values are not the same. For example, =A2<>B2 returns TRUE when A2 and B2 differ. This phrase appears frequently in tutorials and workflows.
Understanding the not-equal operator in Google Sheets
The not-equal operator in Google Sheets is <>. It returns TRUE when the left and right operands are different. This is essential for building logical tests in IF, FILTER, and other functions. When you see the phrase google sheets does not equal, remember the operator is <> and not a string comparison. Use it to drive branching, filtering, and validation in your workflows.
=IF(A2<>B2, "Different", "Same")=IF(NOT(A2=B2), "Different", "Same")=SUMPRODUCT(--(A1:A10<>B1:B10))- In real data, you’ll often test inequality across rows, columns, or arrays. The first formula compares single cells, the second shows an alternative approach using NOT, and the third counts mismatches across ranges for quick sanity checks.
Practical examples: not-equal in different contexts
The not-equal operator is not limited to single-cell tests. You can apply it in arrays, aggregate conditions, and text/date comparisons. For instance, using <> inside FILTER or QUERY helps you exclude unwanted rows or values. Combining not-equal with other functions builds robust data-cleaning pipelines.
=FILTER(A2:C, A2:A<><>"" )=QUERY(A1:C, "select A, B where C <> 'N/A'", 1)These patterns are common in dashboards and data-cleaning sheets, and they demonstrate how google sheets does not equal translates into practical filters and selections.
Not-equal in mixed data types and edge cases
Not-equal tests can be brittle if data types differ. Coerce values when needed and validate edge cases such as numbers stored as text, dates as strings, or mixed case text. These techniques reduce false mismatches and improve reliability across sheets.
Not-equal across data types and edge cases
=IF(VALUE(A2)<>VALUE(B2), "Mismatch", "Match")=IF(EXACT(A2,B2), "Exact", "Not Exact")=IF(DATEVALUE(A2)<>DATEVALUE(B2), "Date mismatch", "Date same")- VALUE converts numeric-like text to numbers for reliable comparisons. EXACT provides case-sensitive string comparison, which is useful when case matters for your data. DATEVALUE handles textual dates to ensure accurate date inequalities.
Steps
Estimated time: 45-60 minutes
- 1
Identify not-equal use cases
List where you need to test inequality: data validation, filtering, conditional logic, and alerting. Decide whether you compare numbers, text, or dates. This step sets the scope for robust not-equal formulas.
Tip: Map each use case to a concrete cell or range to reduce ambiguity. - 2
Choose the correct syntax
For simple checks use A1<>B1. For more advanced data, combine with IF, FILTER, or QUERY. Consider the data type and potential coercion needs (text vs number).
Tip: Prefer explicit coercion (VALUE, DATEVALUE) when data types are uncertain. - 3
Implement a basic test
Create a baseline test like =IF(A2<>B2, "Mismatch", "Match"). Extend to ranges using ARRAYFORMULA or SUMPRODUCT as needed.
Tip: Test with edge cases: text numbers, empty cells, and dates. - 4
Validate with filters and queries
Use not-equal in FILTER and QUERY to exclude certain values, e.g., <> 'N/A' or <> "" for non-empty rows.
Tip: Always verify your result set against a manual expectation before deploying in dashboards. - 5
Handle edge cases and errors
Wrap not-equal tests in IFERROR to catch invalid coercions. Document any assumptions about data types to avoid surprises.
Tip: Use NOT(A=B) as an alternative when readability matters. - 6
Document and snapshot
Comment formulas or maintain a short guide within the sheet so teammates understand the not-equal checks.
Tip: Keep a changelog when adjusting comparison logic in shared sheets.
Prerequisites
Required
- Required
- Basic knowledge of formulas (IF, AND, OR, not equal operator)Required
- A test spreadsheet to practice not-equal logicRequired
Optional
- Optional: familiarity with FILTER, QUERY, and VALUEOptional
- Optional: Apps Script basics for automationOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells or formula text | Ctrl+C |
| PastePaste content into a cell or formula bar | Ctrl+V |
| Fill downFill formulas down a column | Ctrl+D |
| UndoRevert the last action | Ctrl+Z |
| Open find and replaceSearch or replace in the sheet | Ctrl+H |
FAQ
What does the not-equal operator do in Google Sheets?
The not-equal operator <> returns TRUE when the two operands differ. It’s essential for IF, FILTER, QUERY, and data-cleaning workflows in Google Sheets.
Not equal checks return true when two values are different, which is useful for filtering and conditional logic.
Can I use not-equal in conditional formatting?
Yes. You can use a custom formula like =A1<>B1 as the conditional format rule to highlight cells where values differ.
Yes, you can apply not-equal in conditional formatting to flag mismatches.
Is there a difference between <> and =<> in Google Sheets?
In Google Sheets, <> is the standard not-equal operator. =<> is not required and should be avoided for clarity.
Use <> as the not-equal operator; =<> isn’t needed.
How do I test not equal across arrays?
You can use ARRAYFORMULA with <> to compare two columns element-wise, or use SUMPRODUCT to count mismatches across ranges.
Array formulas with <> let you check many pairs at once.
Why might my not-equal test be TRUE for numbers stored as text?
Text representation can prevent numeric comparisons. Coerce values with VALUE or NUMBERVALUE before testing inequality.
Convert text numbers to real numbers before comparing to avoid false positives.
The Essentials
- Master <> for basic and advanced tests
- Coerce data types when mixing numbers, text, and dates
- Use FILTER/QUERY to build robust not-equal data slices
- Guard tests with IFERROR to handle unexpected data