Two Cells Contain Same Value? Compare in Excel
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This calculator checks whether two cells contain the same value and returns TRUE or FALSE, with a case-sensitive option. You enter the two values or cell references and the comparison type, and it applies the standard Excel logic for you. The result is a clear TRUE or FALSE that matches what Excel’s comparison operators and EXACT function would produce.
Formula
The calculator uses two formulas depending on the mode you choose:
- Exact (case-sensitive):
a = b— returns TRUE only if the two values are identical, including letter case. - Ignore case:
LOWER(a) = LOWER(b)— converts both values to lowercase before comparing, so “Apple” and “apple” are considered the same.
In the calculator, a is the first value and b is the second value. The result is a boolean: TRUE if the values are the same under the chosen rule, FALSE otherwise.
Worked Example
Using the default values: a = "apple", b = "Apple", mode = Exact.
- Since mode is Exact, the formula is
a = b. - Compare
"apple"with"Apple"— the first character differs in case. - Therefore,
"apple" = "Apple"is FALSE.
The calculator returns FALSE, which is exactly what Excel would show with =A1=B1 if A1 contains “apple” and B1 contains “Apple”.
How Excel Compares Values
In Excel, the equal sign (=) between two cells performs a case-insensitive comparison by default. That means =A1=B1 returns TRUE if the values are the same ignoring letter case. For example, “Excel” and “excel” are considered equal.
If you need a case-sensitive comparison, Excel provides the EXACT function: =EXACT(A1,B1). This returns TRUE only when the values match exactly, including uppercase and lowercase letters, and also considers leading/trailing spaces as part of the value.
Numbers are compared by their numeric value, so 1 and 1.0 are equal. Text representations of numbers (like “1” vs 1) are not equal in Excel unless you convert one to the other.
When to Use Which Comparison
Choose the comparison mode based on what you are checking:
- Ignore case is best for names, product categories, or any data where capitalization is not meaningful. For instance, checking if a list of email addresses has duplicates usually ignores case because emails are case-insensitive.
- Exact (case-sensitive) is needed for passwords, codes, or IDs where case matters. A product code like “ABC123” is not the same as “abc123” in many systems.
Also consider whether you want to treat blank cells as equal to zero or as empty text. In Excel, an empty cell compared to 0 returns TRUE because empty is treated as 0 in numeric comparisons. This calculator follows that same behavior: if you enter 0 for one value and leave the other blank, it will return TRUE in ignore-case mode.
Common Mistakes and Misconceptions
One frequent mistake is assuming that the equal sign in Excel is case-sensitive. It is not — only EXACT is. Another is forgetting that Excel trims nothing: if a cell contains “apple ” with a trailing space, it will not equal “apple” without the space. The EXACT function also treats spaces as part of the value.
When comparing numbers stored as text versus numbers, Excel may return TRUE because it converts text to numbers in comparisons. For example, ="1"=1 returns TRUE. This calculator does the same: it treats the string “1” and the number 1 as equal in both modes, because the comparison is done after converting to a common type.
Another misconception is that IF(A1=B1,"yes","no") is case-sensitive. It is not, so if you need case-sensitive, you must use IF(EXACT(A1,B1),"yes","no").
Comparing More Than Two Cells
If you need to check whether three or more cells all contain the same value, you can extend the logic. For three cells, use AND(A1=B1, B1=C1). This returns TRUE only if all three are equal. For a range, you can use COUNTIF(range, A1)=COUNTA(range) to see if every cell equals the first cell.
This calculator handles only two values, but the same principle applies: compare each pair sequentially.
Limitations of the Comparison
The comparison is exact for the values you enter — it does not account for formatting, fonts, or cell colors. Two cells that look identical may have different underlying values (e.g., one is text “1” and the other is number 1), but as noted, Excel often treats them as equal in comparisons.
Also, the calculator does not handle arrays or ranges. For checking duplicates in a large list, you would typically use conditional formatting or a COUNTIF formula, not a pairwise comparison.
Frequently Asked Questions
Does Excel’s = operator ignore case?
Yes, by default Excel’s equal sign is case-insensitive. =A1=B1 will return TRUE for “Apple” and “apple”. To make it case-sensitive, use the EXACT function.
How do I compare two cells and return a custom message?
Use the IF function: =IF(A1=B1,"Match","No Match"). This returns “Match” when the values are equal (ignoring case) and “No Match” otherwise.
What if the cells contain formulas?
The comparison uses the calculated result of the formula, not the formula text. So if A1 has =1+1 and B1 has =2, they are equal because both evaluate to 2.
Can I compare two cells ignoring spaces?
Excel does not automatically ignore spaces. To ignore spaces, use =TRIM(A1)=TRIM(B1) or =EXACT(TRIM(A1),TRIM(B1)) for a case-sensitive comparison that also ignores leading/trailing spaces.
Why does my comparison return TRUE for blank and 0?
In Excel, an empty cell is treated as 0 in numeric comparisons. So =A1=B1 returns TRUE if A1 is blank and B1 contains 0. If you want to treat blanks as different from 0, use =AND(A1=B1, A1<>"").
How do I compare two cells in Google Sheets?
Google Sheets behaves similarly: =A1=B1 is case-insensitive, and =EXACT(A1,B1) is case-sensitive. The same TRIM trick works for ignoring spaces.
Related
Formula used
in a | First value | - | apple | 0 in b | Second value | - | Apple | 0 sel mode | Comparison mode | Exact (case-sensitive)=1, Ignore case=0 out result | Same value? | - | if(mode=1, a=b, lower(a)=lower(b)) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.