Multiple Cells Are Equal? How to Check in Excel

This guide explains how to check whether multiple cells in Excel are equal to each other, or equal to a specific value. You will learn the core formulas (AND, COUNTIF, EXACT) and how to apply them to real data, so you can quickly spot mismatches in lists, IDs, or entries.

Formula

The most direct way to test if three cells (A1, B1, C1) are all equal is:

=IF(AND(A1=B1, B1=C1), "Yes", "No")

This checks two conditions: A1 equals B1, and B1 equals C1. If both are true, the AND function returns TRUE and the IF returns “Yes”. If any comparison fails, it returns “No”. For more cells, extend the AND arguments: =AND(A1=B1, B1=C1, C1=D1, ...).

For text, use =EXACT(A1, B1) to make the comparison case-sensitive. For example, “apple” and “Apple” are equal with = but not with EXACT.

Worked Example

Using the defaults from the calculator (5, 5, 5):

1. Compare first and second: 5 = 5 → TRUE
2. Compare second and third: 5 = 5 → TRUE
3. Both TRUE → AND returns TRUE → result “Yes”

If you change the third value to 6, the second comparison fails, and the result becomes “No”.

When to Use Which Formula

• All cells equal to each other → =AND(A1=B1, B1=C1, ...) or =COUNTIF(range, first_cell)=COUNTA(range)
• All cells equal to a specific value (e.g., all are “Pass”) → =COUNTIF(range, "Pass")=COUNTA(range)
• Case-sensitive equality → =AND(EXACT(A1,B1), EXACT(B1,C1))
• Check if any two cells are equal → =OR(A1=B1, A1=C1, B1=C1)
• Count how many cells match a given value → =COUNTIF(range, value)

Common Mistakes and How to Avoid Them

• Forgetting that = is case-insensitive – “TEXT” and “text” are equal to Excel. Use EXACT if case matters.
• Comparing numbers stored as text – A cell containing “5” (text) is not equal to 5 (number). Use the VALUE function to convert, or check for green triangles.
• Trailing spaces – “apple ” and “apple” look the same but are not equal. Use TRIM to clean data first.
• Using AND with a range – =AND(A1:A3) does not compare cells; it checks if every cell in the range is TRUE. Instead, compare pairs.
• Rounding errors – For floating-point numbers, 0.1 + 0.2 may not equal 0.3 exactly. Round both sides before comparing.

How to Handle Large Ranges

When you have dozens or hundreds of cells, typing individual comparisons is tedious. Use these approaches:

• COUNTIF method – To check if all cells in A1:A100 equal the first cell: =COUNTIF(A1:A100, A1)=COUNTA(A1:A100). This works for both numbers and text, but ignores blanks (COUNTA counts non-empty cells).
• Conditional Formatting – Highlight cells that are not equal to the first cell: select the range, go to Home → Conditional Formatting → New Rule → Use a formula. Enter =A1<>$A$1 (adjust for your range) and set a format. This visually flags mismatches.
• Array formula (Excel 365) – Use =AND(A1:A100=A1) as a dynamic array formula to get TRUE/FALSE without Ctrl+Shift+Enter.

Comparing Two Lists for Matches

A related task is checking whether two columns contain the same values, row by row. Use =A1=B1 in a helper column to get TRUE/FALSE. For whole-list comparison (order doesn’t matter), use =SUMPRODUCT(--(COUNTIF(A1:A10, B1:B10)>0)) to count how many items in B appear in A. For duplicates across lists, use conditional formatting with =COUNTIF($A$1:$A$10, B1)>0.

Frequently Asked Questions

How do I check if multiple cells are equal in Excel?

Use =AND(A1=B1, B1=C1) for three cells, or =COUNTIF(A1:C1, A1)=3 for a range. The COUNTIF version counts how many cells match the first cell; if that count equals the total number of cells, they are all equal.

Why does my formula return FALSE even though the cells look the same?

Common reasons: text vs. number format, trailing spaces, or non-printing characters. Use =TRIM(A1) to remove spaces and =VALUE(A1) to convert text to numbers. Also check for line breaks with =CLEAN(A1).

Can I compare cells case-sensitively?

Yes, use the EXACT function. For example, =EXACT(A1, B1) returns TRUE only if the text is identical, including case. To compare multiple cells, combine with AND: =AND(EXACT(A1,B1), EXACT(B1,C1)).

How do I highlight cells that are not equal to the first cell?

Select the range (e.g., A1:A10), go to Home → Conditional Formatting → New Rule → Use a formula. Enter =A1<>$A$1 and set a fill color. This highlights any cell that differs from the first cell.

What’s the difference between = and EXACT?

The = operator ignores case, so “ABC” = “abc” is TRUE. EXACT is case-sensitive, so EXACT(“ABC”,”abc”) is FALSE. EXACT also treats different Unicode characters as different, while = may treat some as equivalent.

How do I check if all cells in a column are equal to a specific value like “Pass”?

Use =COUNTIF(A1:A100, "Pass")=COUNTA(A1:A100). This counts how many cells contain “Pass” and compares that to the number of non-empty cells. If they match, all non-empty cells are “Pass”.

Related

• GED Score Calculator: How to Estimate Your Score
• Duke Treadmill Test Score Calculator
• Dropping Lowest Test Score Calculator

in a | First value | - | 5 | 0
in b | Second value | - | 5 | 0
in c | Third value | - | 5 | 0
out all_equal | All equal? | - | IF(AND(a=b, b=c), "Yes", "No") | 0