To compare two cells in Excel, enter =A2=B2 in another cell, assuming the values you want to compare are in A2 and B2. Excel returns TRUE if they match and FALSE if they do not; wrap the comparison in IF when you want words or another result instead.
How the basic comparison formula works
The equal sign in a formula starts the calculation, and the second equals sign is Excel’s comparison operator. With values in A2 and B2, =A2=B2 tests whether those two cell values are equal. If both cells contain the text North, the result is TRUE. If A2 contains North and B2 contains South, the result is FALSE.
The result is a logical value, not the text “TRUE” or “FALSE.” Other formulas can use it directly, and you can filter or count results. For example, the comparison can be part of a larger formula that makes a decision. The same pattern works with other comparison operators:
=A2<>B2checks whether the cells are different.=A2>B2checks whether A2 is greater than B2.=A2<B2checks whether A2 is less than B2.=A2>=B2and=A2<=B2include equality in the comparison.
These comparisons are useful for numbers, dates, and text, although the meaning of “greater” or “less” depends on the type and order of the values. For text, Excel compares entries according to its text-comparison rules; greater-than is usually less intuitive than equality.
Return a message instead of TRUE or FALSE
If a worksheet is intended for people to read, a label may be clearer than a logical result. With the two values in A2 and B2, enter =IF(A2=B2,"Match","Different"). It displays Match when the cells are equal and Different otherwise. Change the quoted labels to suit your report; the comparison remains the same.
IF can also return a value from a cell or perform a calculation. With the comparison values in A2 and B2 and the desired message in C2, =IF(A2=B2,C2,"Review") returns C2 when the compared values match, or Review when they do not. The values returned by the two branches can be text, numbers, or cell references, provided the formula is valid for the intended use.
For a simple equality check, the basic formula is usually easier to inspect. Use IF when the result needs to drive a label, a follow-up calculation, or a workflow. Avoid writing a formula that repeats the same comparison unnecessarily: a direct TRUE/FALSE result is already suitable for many later calculations.
Make a case-sensitive text comparison
The equals comparison does not distinguish between uppercase and lowercase letters in text. With the text values in A2 and B2, =A2=B2 returns TRUE for Apple in A2 and apple in B2. This is useful for ordinary labels, where capitalization is often just a style choice, but it is not appropriate when letter case itself matters.
Use the EXACT function for a case-sensitive test. With the text entries in A2 and B2, =EXACT(A2,B2) returns TRUE only when the text matches character for character, including capitalization. For example, Apple and apple return FALSE. EXACT is for text comparison; it does not make a number and a text string into the same type.
EXACT also makes spaces significant. If one entry has an extra space at the end, it can report a difference even when the cells look nearly identical on screen. That behavior can be helpful for checking codes or identifiers, but it can also reveal accidental whitespace that needs cleaning.
Account for blanks, numbers, and stored text
Cell contents that look similar are not always stored the same way. A number such as 25 and the text string “25” may display alike, but they are different data types and should not be assumed to compare as equal. When comparing imported data, check whether values are genuine numbers or numbers stored as text. The green error indicator or a change in alignment can provide clues, though neither is a complete test.
If A2 contains a numeric string that can be converted to a number and B2 contains a number, use =VALUE(A2)=B2 to compare after conversion. For example, if A2 contains text 25 and B2 contains the numeric value 25, the formula returns TRUE. VALUE cannot make arbitrary text meaningful as a number; invalid text can produce an error, so inspect the source data before relying on conversion.
Blank cells also deserve care. A direct comparison can treat a referenced empty cell similarly to zero in some numeric comparisons, so a blank and a zero may not be distinguished the way a reader expects. To test specifically whether both cells are empty, with the cells in A2 and B2, use =AND(ISBLANK(A2),ISBLANK(B2)). It returns TRUE only when both cells are genuinely empty; a cell containing a formula that displays an empty string is not genuinely blank for ISBLANK.
If a formula returns an empty string, that is different from leaving the cell empty. To test whether both cells display empty text, with the results in A2 and B2, use =AND(A2="",B2=""). This test also treats a truly blank cell as equal to an empty string in this context. Choose the test based on whether your rule concerns genuinely empty cells or values that appear empty.
Compare rows or check whether values differ
For a row-by-row comparison, place the formula beside the first pair and fill it down. With corresponding values in A2 and B2, enter =A2=B2 in C2, then copy or fill C2 down the rows. Excel adjusts the references to A3 and B3, then A4 and B4, and so on. The resulting column gives a TRUE/FALSE check for each pair.
To make a column of readable status labels instead, with the paired entries in A2 and B2, put =IF(A2=B2,"Same","Check") in C2 and fill down. Same means the row’s values match under Excel’s ordinary comparison rules; Check flags a mismatch for review. Give the result column a heading so that someone scanning the workbook knows what the labels mean.
For a quick count of mismatches, assuming comparison results are in C2:C100, use =COUNTIF(C2:C100,FALSE). It counts the FALSE results in that range. If the comparison formulas return words rather than logical values, count the appropriate text label instead; the criterion must match what the formulas actually produce.
Comparing two complete columns is not the same as checking whether two lists contain the same items. The row formula checks A2 against B2, A3 against B3, and so on. If the same values appear in different orders, it may flag differences even though both lists contain the same set. For an order-independent comparison, first decide whether duplicate entries matter and whether each value must appear the same number of times; that is a separate list-matching problem.
Highlight differences with Conditional Formatting
A formula can identify a mismatch, while Conditional Formatting makes it visible in the original cells. To highlight pairs across A2:B100, select that range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the first row of the selected range being row 2, enter =$A2<>$B2 as the rule formula, choose a format, and confirm the rule.
The dollar signs lock the A and B columns while leaving the row number relative. That lets the same rule test A2 against B2, then A3 against B3, throughout the selected range. Because the formula uses not equal to, the format is applied to both cells in a row when their values differ. If the selection starts on a different row, change the row number in the formula to match its first row.
To highlight only one side of each pair, select just A2:A100 and use a rule that compares each A cell with the corresponding B cell. With that selection beginning in row 2, enter =$A2<>$B2. The same formula works because the range determines which cells receive formatting; the formula determines whether each row meets the condition. Conditional Formatting changes appearance, not the underlying values or the result of a comparison formula.
Common comparison problems and fixes
- Unexpected FALSE results: Check for leading or trailing spaces, different data types, or a formula in one cell that returns a different value than its display suggests. Widen the column or inspect the formula bar to see what is actually stored.
- Unexpected TRUE results: Remember that ordinary text equality is not case-sensitive. Use EXACT when capitalization must match, and use ISBLANK when the rule needs to distinguish an empty cell from zero or an empty-string formula.
- Numbers differ only in displayed decimals: A cell’s number format can hide underlying precision. Formatting a number to two decimal places does not round its stored value. If the business rule is to compare values rounded to two decimal places, with numbers in A2 and B2, use
=ROUND(A2,2)=ROUND(B2,2). For example, 4.126 and 4.124 both round to 4.13 and 4.12 respectively, so the formula returns FALSE. Decide the appropriate precision before rounding. - Errors appear in the result: If either referenced cell contains an error such as #N/A, a direct comparison may also return an error rather than TRUE or FALSE. Find and resolve the source error, or deliberately build an error-handling rule; do not treat an error as an ordinary mismatch without deciding what it means.
- The copied formula checks the wrong cells: Relative references move when filled. Use dollar signs to lock a row or column only when the comparison design requires it. For ordinary row-by-row checks, leaving both references relative is correct.
Choose the right comparison method
For a straightforward test of two values, use =A2=B2. For a readable label, wrap the test in IF. For text where capitalization matters, use EXACT. To distinguish truly blank cells from values that merely look empty, use ISBLANK or an explicit empty-string test depending on the requirement. To scan many pairs visually, use Conditional Formatting; to analyze them, create a helper column with a comparison formula.
Before choosing, clarify what “match” means in the worksheet. Does capitalization count? Are spaces meaningful? Should 25 and text “25” count as equal? Is a displayed difference caused only by formatting or hidden decimal places? Stating these rules first prevents a formula from returning a technically correct answer to the wrong question.
Frequently Asked Questions
What is the simplest Excel formula to compare two cells?
With the values in A2 and B2, use =A2=B2. Excel returns TRUE for equal values and FALSE otherwise.
How do I compare two cells and show “Yes” or “No”?
With the values in A2 and B2, use =IF(A2=B2,"Yes","No"). It returns Yes for a match and No for a difference under the usual comparison rules.
How can I compare text in Excel and make uppercase matter?
With the text in A2 and B2, use =EXACT(A2,B2). Unlike the equals operator, EXACT is case-sensitive.
How do I highlight cells when two columns differ?
Select the comparison range, then create a formula-based rule from Home > Conditional Formatting > New Rule. For pairs in A2 and B2 downward, use =$A2<>$B2 when the selected range starts in row 2.
Why does Excel say two numbers are different when they look the same?
They may have different underlying precision, one may be stored as text, or one may contain an unshown space or other character. Check the stored values and types, and only round or convert them if that matches the comparison rule you need.
Related
- How to Insert a Checkbox in Excel: Steps and Tips
- Change Text to Numbers in Excel: Easy, Reliable Methods
- Center Across Selection in Excel: Add It to Quick Access
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- INDIRECT in Excel: Create Dynamic Cell ReferencesIn Excel, INDIRECT turns text that looks like a cell or range address into a usable reference. For…
- Inventory Spreadsheet Software: How to Choose the Right ToolInventory spreadsheet software is a spreadsheet app—such as Microsoft Excel or Google Sheets—used to record stock, movements, locations,…
- Excel Formula Bot Free? Free Plans, Limits, and Safe UseYes—many Excel formula bots offer free access, but it is usually limited by usage, features, or account requirements.…
- Sort Dates in Excel in Ascending Order the Right WayTo sort dates in Excel from oldest to newest, select a date cell, then choose Data > Sort…
- SQL Server Split Function: STRING_SPLIT ExplainedSQL Server’s built-in split function is STRING_SPLIT, which turns a delimited string into a table of values. Use…
- Excel DATEDIF Age Formula: Calculate Years, Months, DaysTo calculate someone’s age in completed years in Excel, use =DATEDIF(A2,TODAY(),"Y"), with the date of birth in A2.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.