How To SheetsHow To Sheets
Formulas and Functions

Not Equal Google Sheets: The Complete Guide to the <> Operator

Learn how to use the not equal operator in Google Sheets with practical formulas, conditional formatting, and array patterns. Master <> to reliably compare data, detect mismatches, and build dynamic dashboards.

How To Sheets
How To Sheets Team
·5 min read
What-Is-Google-SheetsFormula-Errors-Google-SheetsConditional-Formatting-Google-Sheets
Not Equal in Sheets - How To Sheets
Photo by Kampus Productionvia Pexels
Quick AnswerDefinition

The not equal operator in Google Sheets is the symbol <>. It returns TRUE when two values differ and FALSE when they are the same. Use it in IF, FILTER, and conditional formatting to drive logic, validation, or visual cues. Example: =A1<>B1 evaluates to TRUE if A1 and B1 are not equal, enabling dynamic checks across your data.

Not Equal Operator: Core syntax and semantics

The not equal operator in Google Sheets is the symbol <>. It returns TRUE when the compared values differ and FALSE when they are the same. This basic operator underpins many conditional formulas and data validation tasks. According to How To Sheets, mastering this operator is foundational for building reliable spreadsheets.

Excel Formula
=A1<>B1

Explanation: This formula compares A1 and B1. If their values differ, the result is TRUE; otherwise FALSE.

Excel Formula
=IF(A1<>B1, "Different", "Same")

Explanation: Integrates the not-equal check into a decision, enabling dynamic messaging or branching.

Excel Formula
=ARRAYFORMULA(A2:A<>B2:B)

Explanation: Applies the comparison across entire columns without copying the formula down manually.

Common variations:

  • Compare against blanks: A2<>"" to detect non-empty cells
  • Case-insensitive text: you may wrap with LOWER or UPPER, e.g., LOWER(A2)<>LOWER(B2)

Tip: Use not equal within FILTER, SUMIF, or conditional formatting to build robust, data-driven sheets.

Practical examples in common formulas

Not equal logic is central to many everyday tasks in Google Sheets. Here are representative patterns that you can adapt to your data workflows.

Excel Formula
=IF(A2<>B2, "Mismatch", "Match")

Explanation: Branches output depending on whether A2 and B2 differ.

Excel Formula
=FILTER(A2:B, A2:A<>B2:B)

Explanation: Returns rows where the values in column A differ from the corresponding values in column B.

Excel Formula
=SUMIF(A:A, "<>"&"", B:B)

Explanation: Sums B over rows where A is not blank, illustrating not-equal logic with aggregation.

Notes: When using not equal with text, consider case-insensitive comparisons: =IF(LOWER(A2)<>LOWER(B2), "Diff", "Same").

Conditional formatting with not equal checks

Conditional formatting can highlight differences without altering data. Use a custom formula rule to paint cells when values differ between corresponding columns.

Excel Formula
=A1<>B1

Explanation: Applies the rule to each row; if A1 differs from B1, the formatting triggers for that row. You can adjust references to fit your range.

Another common pattern:

Excel Formula
=C1<>D1

Explanation: Compares adjacent columns so you can visually audit multiple pairs side-by-side.

Practical tip: Use absolute and relative references carefully when applying across a whole range to avoid misalignment in your formatting results.

Not equal vs. not empty checks

Often you only want to react when a cell is populated or not. The not-equal operator helps here too, but it’s important to distinguish not equal from not empty.

Excel Formula
=A2<>""

Explanation: Returns TRUE when A2 contains any content (non-blank). In dashboards, you might color-code populated rows.

Excel Formula
=IF(A2<>"", "Has value", "Empty")

Explanation: Simple branch showing a human-friendly label based on non-blank content. This avoids misinterpreting blank cells as zero or false.

Common pitfall: A blank cell may still produce unexpected results in numeric comparisons, so prefer explicit checks like A2<>"" for not-empty logic.

Common pitfalls and tips

Not equal is powerful, but use it with awareness of data types. Text comparisons may require normalization, and numbers stored as text can yield confusing results.

Excel Formula
=LOWER(A1)<>LOWER(B1)

Explanation: Normalizes text to a common case before comparison, reducing false mismatches.

Excel Formula
=IF(A1<>5, "Not 5", "Is 5")

Explanation: Demonstrates numeric comparison; ensure cells aren’t stored as text.

When extending to many rows, prefer ARRAYFORMULA to avoid manual copying:

Excel Formula
=ARRAYFORMULA(A2:A<>B2:B)

Explanation: Efficiently compares two entire columns without duplicating formulas.

Advanced patterns: combining not equal with other functions

Complex sheets often need not-equal logic woven with error handling and aggregation.

Excel Formula
=IFERROR(IF(A1<>B1, "Mismatch", "Match"), "Error")

Explanation: Handles errors gracefully when data is missing or malformed.

Excel Formula
=SUM(ARRAYFORMULA(--(A2:A<>B2:B)))

Explanation: Converts the boolean array of not-equal results into 1/0 values, allowing a sum of all differences. This is useful for quick mismatch tallies across large datasets.

Other variation: use NOT with ISBLANK for explicit emptiness checks, e.g., =NOT(ISBLANK(A1)) combined with a not-equal condition for richer logic.

Steps

Estimated time: 15-25 minutes

  1. 1

    Open your sheet and locate columns to compare

    Identify the two columns you want to compare side-by-side. Ensure you understand the data types (text vs numbers) so you pick appropriate comparison logic.

    Tip: Double-check data types; a number-formatted text value will not match a numeric value without normalization.
  2. 2

    Write the not-equal formula for a single row

    Enter a simple not-equal formula in a helper column to test a single row. This validates your syntax before dragging or applying to a range.

    Tip: Start with a small sample (rows 1-5) to confirm expected TRUE/FALSE results.
  3. 3

    Extend the formula across rows

    Use ARRAYFORMULA or fill down to apply the not-equal check across the entire data range.

    Tip: If you use ARRAYFORMULA, avoid mixing with other array-backed functions in the same column.
  4. 4

    Incorporate into conditional formatting

    Create a conditional format rule using a not-equal formula to visually flag mismatches.

    Tip: Reference should be relative to the first row of your range to ensure proper highlighting.
  5. 5

    Test with edge cases

    Include blanks, numeric vs text comparisons, and mixed-case text to verify robustness.

    Tip: Remember to handle empty cells explicitly (e.g., A2<>"").
  6. 6

    Document and review

    Add notes about the logic and any special cases so teammates understand the approach.

    Tip: Keep a short README in the sheet with the not-equal usage notes.
Pro Tip: Use ARRAYFORMULA for scalable comparisons across large datasets without dragging formulas.
Warning: Be mindful of numbers stored as text; normalize with VALUE or TEXT-to-number steps when necessary.
Note: For collaborative sheets, document assumptions about data types and expected values to reduce confusion.

Prerequisites

Required

Optional

  • Familiarity with conditional formatting
    Optional
  • Optional: Sheets performance awareness for large ranges
    Optional

Keyboard Shortcuts

ActionShortcut
Copy formula downFill the selected cell(s) with the formula from above when extending down a columnCtrl+D
CopyCopy a formula or value to another locationCtrl+C
PastePaste the copied formula/valueCtrl+V
Enter formula across multiple cellsApply a formula to a selection of cells simultaneouslyCtrl+↵

FAQ

What is the not equal operator in Google Sheets?

The not equal operator is <>. It returns TRUE when two values are different and FALSE when they are the same. It appears in formulas like =A1<>B1 and is essential for conditional logic and data validation.

The not equal operator is <>. It lets you check if two values differ and is great for conditional formatting and data checks.

How do I use not equal in an IF statement?

Place the not-equal condition inside the IF statement, e.g., =IF(A1<>B1, "Mismatch", "Match"). It evaluates the condition and returns different results based on whether A1 and B1 differ.

Use IF with <> to branch results based on whether two cells differ.

Can I perform not-equal checks across entire columns?

Yes. Use ARRAYFORMULA to apply not-equal across columns, for example =ARRAYFORMULA(A2:A<>B2:B). This returns a column of TRUE/FALSE values for each row.

Yes, with ARRAYFORMULA you can compare entire columns at once.

How can I highlight differences with conditional formatting?

Create a custom formula rule like =A1<>B1 and apply it to the relevant range. This will highlight rows where A and B differ.

Use a simple not-equal rule to color rows with differences.

Is the not equal operator case-sensitive for text?

Not typically; to enforce case-insensitive comparison, wrap text in LOWER or UPPER, e.g., =LOWER(A1)<>LOWER(B1).

Case-insensitive comparisons can be achieved with LOWER/UPPER wrappers.

What about not equal to blank in calculations?

Use A1<>"" to test non-emptiness, which is common in data validation and filtering.

Test for non-empty cells with <>"" in formulas or filters.

The Essentials

  • Master <> for not-equal checks in Sheets
  • Embed not-equal logic in IF/FILTER for dynamic results
  • Use LOWER/UPPER for case-insensitive comparisons
  • Apply not-equal in conditional formatting for quick visual audits
  • Leverage ARRAYFORMULA for bulk comparisons

Related Articles

← More in Formulas and Functions