Excel Test If Two Cells Are Equal: Formulas and Fixes

Formulas & functions · Updated November 18, 2026

To test whether two cells are equal in Excel, enter =A1=B1 in another cell, replacing A1 and B1 with the cells you want to compare. Excel returns TRUE when their values compare as equal and FALSE when they do not; the comparison is not case-sensitive.

The basic equality formula

The equals sign is an Excel comparison operator. In a formula, put the first cell reference on one side and the second reference on the other. For example, with values in A2 and B2, enter =A2=B2 in C2. If both cells contain the number 42, C2 displays TRUE. If one contains 42 and the other contains 43, it displays FALSE.

The result is a logical value, not the text “TRUE” or “FALSE.” That matters when you use the result in another formula: Excel can use it directly in an IF function, a filter, or another logical test. To show a friendlier message, with the values still in A2 and B2, enter =IF(A2=B2,"Match","Different") in C2. It displays Match if the cells compare equal and Different otherwise.

For a quick one-time check, you can also type the comparison directly into the formula bar or an empty cell. If you are comparing other cells, edit both references. Excel normally adjusts relative references when you copy a formula down a column, so a formula in C2 copied to C3 becomes =A3=B3.

What Excel means by “equal”

A standard comparison checks cell contents, not whether the cells look the same on the worksheet. Number formats, font color, borders, and other visual formatting do not determine equality. A cell displaying 10.0 and another displaying 10 may compare equal if both contain the numeric value 10, even though their displayed formats differ.

Likewise, two values that look alike may not compare equal if one is stored differently. A number entered as text is not necessarily equal to the same value stored as a number. For example, a cell containing the number 25 and a cell containing the text string “25” are different data types for a direct comparison. This can happen after importing data, pasting from another application, or entering a leading apostrophe.

To check what is stored, select a cell and inspect the formula bar, or use Excel’s number-format and data-cleaning tools. Functions such as ISTEXT and ISNUMBER can help diagnose a type mismatch. With the values in A2 and B2, =AND(ISNUMBER(A2),ISNUMBER(B2),A2=B2) returns TRUE only when both cells contain numbers and those numbers compare equal. This is useful when text that resembles a number should not count as a match.

Case-sensitive text comparisons

The equals operator treats text comparisons as case-insensitive. Thus, “North” and “NORTH” compare equal with =A2=B2. That is usually convenient for names, labels, and codes when capitalization is inconsistent, but it may not fit a password-like value or a code where letter case matters.

For a case-sensitive comparison, use the EXACT function. With text in A2 and B2, enter =EXACT(A2,B2). It returns TRUE only when the text matches character for character, including capitalization. For example, “North” and “NORTH” return FALSE with EXACT even though the ordinary equality test returns TRUE.

EXACT does not ignore spaces or punctuation. A trailing space in one cell can make two otherwise similar strings fail the test. If your goal is to compare text while ignoring accidental spaces at the beginning and end, first consider cleaning the source data with TRIM. Be aware that TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces; it does not remove every possible invisible character, such as some nonbreaking spaces copied from web pages.

Blanks, formulas, and zero

Two genuinely empty cells compare as equal with the basic test. However, “looks blank” can mean several different things in Excel. A cell may be truly empty, contain a formula that returns an empty string, contain a space, or contain the number zero with a format that hides it. Those cases can behave differently in comparisons and calculations.

To test specifically whether both cells are truly empty, use ISBLANK. With the cells to check in A2 and B2, enter =AND(ISBLANK(A2),ISBLANK(B2)). This returns TRUE only if neither cell contains a value or formula. In particular, a formula that displays an empty string is still a formula, so ISBLANK returns FALSE for that cell.

If you need a three-way result that distinguishes two empty cells from equal nonblank values, with A2 and B2 as the comparison cells, use =IF(AND(ISBLANK(A2),ISBLANK(B2)),"Both blank",IF(A2=B2,"Equal","Different")). This helps avoid reporting two missing entries as a meaningful match. Decide how your worksheet should treat blanks before applying a comparison across a large table; a blank-versus-zero case may deserve its own rule rather than being grouped with ordinary matches.

Numbers, dates, and decimal precision

For ordinary numeric values, direct equality is appropriate when the stored numbers should be exactly the same. Dates in Excel are stored as serial numbers, so two cells containing the same date value compare equal even if one is formatted as a long date and the other as a short date. A date with a time component, however, is a different stored value from the same date at midnight. If dates appear identical but return FALSE, check whether one contains a hidden time.

Calculated decimal values can also differ by a very small amount even when their displayed numbers look identical. Excel displays a rounded representation based on the cell’s number format, so two cells may both show 1.23 while storing slightly different values. If the comparison is about a measurement or calculation where a small difference is acceptable, compare the difference with a chosen tolerance instead of requiring exact equality.

For example, with numeric results in A2 and B2, this test returns TRUE when their absolute difference is less than 0.000001: =ABS(A2-B2)<0.000001. The threshold is a decision for your task, not a universal Excel setting; choose units and a tolerance that make sense for the data. The formula assumes both cells contain numbers. If a mismatch might be meaningful, show the difference with =A2-B2 in C2 rather than hiding it behind a tolerance.

Rounding is another option when the comparison should be made at a stated number of decimal places. With numeric values in A2 and B2, =ROUND(A2,2)=ROUND(B2,2) compares the values after rounding each to two decimal places. This changes the comparison rule: values that differ beyond the second decimal place may still count as equal after rounding. Use it only when that rounding is appropriate for the data.

Compare many rows or return useful labels

To compare corresponding pairs down a list, put the first comparison in the results column and copy it down. For instance, with paired values in A2:A100 and B2:B100, enter =A2=B2 in C2, then fill the formula through C100. Each row compares its own A and B values. You can copy by dragging the fill handle at the lower-right corner of the selected cell, or by copying and pasting into the destination range.

If a report should say what happened rather than display TRUE or FALSE, wrap the comparison in IF. With paired values in A2:A100 and B2:B100, enter =IF(A2=B2,"Same","Check") in C2 and copy down. The labels make a review easier, but retain the underlying comparison logic: “Same” still means the values compare equal under Excel’s standard, case-insensitive equality rule.

For a total of matching rows, count the TRUE results in C2:C100 with =COUNTIF(C2:C100,TRUE). This assumes the range contains the logical results from the equality formulas, rather than text labels. Counting matches is useful for a quick reconciliation, but it does not identify which rows differ; keep the row-level results available for investigation.

Highlight matching cells with conditional formatting

You can color matching pairs without adding a visible results column. Select the range to format, such as A2:B100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With paired values in columns A and B, starting on row 2, enter =$A2=$B2 as the rule formula, choose a format, and confirm the rule.

The dollar signs lock the column references while leaving the row number relative. Excel can therefore evaluate A2 against B2 for the first selected row, A3 against B3 for the next, and so on. If you select only A2:A100, the same rule can highlight the A cell for each matching row. Change the references if your pairs are in different columns or the selected range starts on a different row.

Conditional formatting changes appearance; it does not change values or create a separate TRUE/FALSE result. If you need to sort, count, export, or inspect matches later, a helper column with the basic formula is often clearer. The conditional-formatting menu path is for Excel for Microsoft 365 on Windows; Excel for Mac has the Conditional Formatting controls on its Home tab as well, though the dialog layout can differ.

Common reasons a comparison returns an unexpected result

  • Different data types: one value may be a number and the other text that resembles a number. Check the source or test with ISNUMBER and ISTEXT.
  • Extra spaces or hidden characters: imported text can contain leading spaces, trailing spaces, or nonprinting characters. Inspect the text and clean it before comparing.
  • Different stored precision: displayed rounding can conceal a small numeric difference. Compare the difference or apply an intentional tolerance.
  • Case sensitivity: the equals operator ignores letter case. Use EXACT when capitalization must match.
  • A hidden time in a date: two date cells can display the same day while one contains a time value. Check the formula bar or display more time precision.
  • An error value: if either referenced cell contains an error such as #N/A or #VALUE!, the comparison can return an error instead of TRUE or FALSE.

Do not automatically turn every error into FALSE without considering what the error means. With A2 and B2 as the cells being compared, =IFERROR(A2=B2,FALSE) returns FALSE when the comparison produces an error. That can be appropriate for a report that must show a yes-or-no result, but it can also conceal a broken lookup or invalid source value. For troubleshooting, it is often better to leave the error visible or handle specific errors separately.

Choosing the right test

Use the simplest comparison that matches the question you are asking. The standard equals operator is right for most checks of values; EXACT is the text option when capitalization matters. Use ISBLANK when the distinction between an actually empty cell and other blank-looking contents matters, and use a tolerance or deliberate rounding rule for calculated measurements.

Before applying a formula to thousands of rows, test it on representative cases: a clear match, a clear mismatch, a blank, a number stored as text, and any edge case important to the worksheet. That small check helps confirm that the formula reflects your definition of “equal,” rather than merely producing a convenient-looking result.

Frequently Asked Questions

What formula checks whether two cells are equal in Excel?
Use =A1=B1, replacing A1 and B1 with the cell references you want to compare. The result is TRUE or FALSE.

How do I compare two cells and display Yes or No?
With the values in A1 and B1, use =IF(A1=B1,"Yes","No"). Excel displays Yes for a match and No otherwise.

How do I make Excel compare text with case sensitivity?
Use the EXACT function. With text in A1 and B1, =EXACT(A1,B1) returns TRUE only when the characters, including capitalization, match.

Why does Excel say two cells are different when they look the same?
They may contain different data types, extra spaces, hidden characters, different decimal values, or a hidden time in a date. The displayed format can conceal those differences.

Can Excel highlight two cells when they match?
Yes. Select the cells or rows to format, create a conditional-formatting rule using a formula such as =$A2=$B2, and choose a highlight format. Adjust the references to fit your columns and starting row.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.