To compare two cells in Excel, use an equality test: with the values to compare in A2 and B2, enter =A2=B2. Excel returns TRUE when the values are equal and FALSE when they are not; wrap the test in IF if you want words instead.
What a comparison formula does
An Excel comparison formula evaluates a relationship between values and returns the logical result TRUE or FALSE. You can use that result directly in a cell, combine it with other tests, or use it to decide what an IF formula should display. Comparisons are useful for checking whether two entries agree, identifying changes between periods, validating data, and testing whether a number falls within a range.
Excel uses six comparison operators:
=means equal to.<>means not equal to.>means greater than.<means less than.>=means greater than or equal to.<=means less than or equal to.
In a worksheet formula, start with one equals sign, then place the comparison between the values or cell references. Excel does not use the double-equals operator common in some programming languages. For example, a test for unequal values uses the not-equal operator, not an exclamation mark. Comparison results can also feed into functions such as AND, OR, and IF.
Compare two cells and display a useful result
A direct comparison is ideal when TRUE and FALSE are informative enough. For a more readable result, use IF to specify what Excel should show for each outcome. With the values to compare in A2 and B2, enter =IF(A2=B2,"Match","Different"). It displays Match when the cells are equal and Different otherwise.
The text in quotation marks is an output label, so you can replace it with wording that suits your sheet, such as Same and Changed or Pass and Review. The test itself still uses the equals operator. To show a result only when the values differ, reverse the test or use the not-equal operator. With A2 and B2 holding the compared values, enter =IF(A2<>B2,"Check","No change"). This displays Check for unequal values and No change when they match.
When comparing two columns row by row, place the comparison formula in a third column beside the first pair. For example, if column A contains old values and column B contains new values, the result in row 2 can be filled down to compare each corresponding pair. Excel adjusts relative references for each row. Check that the two columns represent the same records in the same order; a row-by-row comparison can report differences simply because one list was sorted differently.
Compare text, including capitalization and spaces
Ordinary equality comparisons in Excel are not case-sensitive: text such as North and NORTH is generally treated as equal by a formula using the equals operator. That is convenient for many lists, but it is not appropriate when capitalization itself matters, such as checking a case-sensitive code. With the text entries in A2 and B2, enter =EXACT(A2,B2). EXACT returns TRUE only when the text matches exactly, including letter case.
A result that looks unexpected may be caused by spaces rather than letters. A trailing space after a name is easy to miss, and an ordinary comparison can treat text differently when one entry contains extra characters. If leading and trailing spaces should be ignored, use TRIM. With text in A2 and B2, enter =TRIM(A2)=TRIM(B2). This compares the cleaned text and returns TRUE if the results match. TRIM also reduces repeated internal ordinary spaces to single spaces, but it does not remove every kind of invisible character; nonbreaking spaces copied from websites may need separate cleanup.
Choose the comparison deliberately: use the equals operator when capitalization is not important, EXACT when it is, and a cleanup step when inconsistent spaces are the issue. These checks compare cell contents, not necessarily how the cells look. Number formats, font color, and other visual formatting do not affect a normal equality test.
Compare numbers and allow for a tolerance
For whole numbers or values that should match exactly, the basic equality test is usually sufficient. Measurements and calculated decimals may differ by a small amount because of rounding or the way values are stored internally. In that situation, decide on an acceptable tolerance rather than expecting an exact match. With decimal measurements in A2 and B2 and a chosen tolerance of 0.01, enter =ABS(A2-B2)<=0.01. For example, if A2 is 12.345 and B2 is 12.35, the absolute difference is 0.005, so the formula returns TRUE.
The tolerance is a rule you choose for the task, not a universal Excel setting. A hundredth may be suitable for one measurement and far too wide or narrow for another. If the values should be compared after rounding to a fixed number of decimal places, you can round both values first. With values in A2 and B2 and two decimal places required, enter =ROUND(A2,2)=ROUND(B2,2). For 12.345 and 12.35, Excel rounds both to 12.35, so the comparison returns TRUE. Decide whether rounding or a tolerance better represents the real requirement, especially when a difference near the boundary matters.
To test whether a value lies within a range, combine two comparisons with AND. With the value in A2 and inclusive lower and upper limits of 10 and 20, enter =AND(A2>=10,A2<=20). It returns TRUE for values from 10 through 20, including both endpoints, and FALSE for values outside that interval.
Check whether a value appears in another list
Comparing lists is different from comparing two cells in the same row. If you need to know whether a particular entry appears anywhere in a reference list, COUNTIF can count matches. With the reference values in A2:A10 and the item to find in D2, enter =COUNTIF($A$2:$A$10,D2)>0. It returns TRUE if at least one matching entry is found and FALSE if none is found. The dollar signs keep the reference range fixed when you copy the formula down, while D2 changes to D3, D4, and so on.
COUNTIF is useful for checking membership, but it does not tell you which row contains a match or compare items in order. It is also not case-sensitive, and wildcard characters in criteria can affect text searches. If you need a case-sensitive list check, you need a different approach using EXACT across the range; for a simple one-cell comparison, EXACT is the straightforward choice. For large list-cleaning tasks, confirm that spaces and data types are consistent before interpreting a missing match as a genuine difference.
Highlight differences instead of showing a result
Conditional formatting can color mismatched cells without adding a visible TRUE/FALSE column. In Excel for Microsoft 365 on Windows, select the rows or cells you want to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With values in columns A and B and the first data row at row 2, use =$A2<>$B2 as the rule formula, choose a format, and confirm the rule.
The dollar signs lock the column references so the rule compares A with B across each selected row; the row number remains relative so it changes for each row. If you select only A2:B20, the rule can highlight both cells in a row when they differ. If you select only A2:A20, it highlights the cells in column A for rows where A and B do not match. Make sure the rule’s starting row corresponds to the first row in the selected range, or the comparison may be offset.
To highlight matching rows instead, change the not-equal operator to the equal operator. Conditional formatting changes appearance; it does not replace or edit the underlying values. On Excel for Mac, the same feature is available from the Home tab’s Conditional Formatting menu, although the dialog layout can differ. Excel for the web also offers conditional formatting, but available controls can vary; use the desktop app if a rule option is missing.
Dates, blanks, and common comparison problems
Excel stores valid dates as numbers and displays them using date formats. Two cells containing the same date can compare equal even if one displays as 3-Nov-2026 and the other as 11/3/2026. Conversely, dates that look alike may not be equal if one cell contains text rather than a real Excel date, or if one includes a time component that the cell format hides. Check the cell values and formats when a date comparison surprises you. If the requirement is to compare calendar days while ignoring times, extract or normalize the date portion rather than assuming the displayed text tells the whole story.
Blank cells deserve similar care. A formula that compares two empty-looking cells may treat a genuinely blank cell and a formula returning an empty string as equivalent in some comparisons, even though one cell contains a formula. If the distinction matters, check for actual blanks separately. With cells A2 and B2 to compare, enter =IF(AND(A2="",B2=""),"Both empty",IF(A2=B2,"Match","Different")). This labels two empty-looking entries as Both empty before testing the values; it does not establish whether those cells are truly empty or contain formulas that return an empty string.
Errors in either input can also cause a comparison formula to return an error instead of TRUE or FALSE. Use error handling only when you have a meaningful message for that case. With potentially error-containing values in A2 and B2, enter =IFERROR(IF(A2=B2,"Match","Different"),"Check source"). It returns Check source if evaluating the comparison produces an error. This can make a report easier to read, but it can also conceal an underlying data problem, so investigate the source rather than treating every error as a normal mismatch.
Choose the right comparison method
Before building a comparison, identify what “same” means for the task. Two entries may need to match exactly, match after rounding, match regardless of capitalization, or appear anywhere in a separate list. A formula can only apply the rule you define; it cannot infer whether a tiny numeric difference or a change in letter case is important.
- Use a direct equality or not-equal test for a simple pair of values.
- Use IF when a worksheet needs readable labels rather than TRUE and FALSE.
- Use EXACT when capitalization in text must match.
- Use a tolerance or ROUND for decimal values when exact equality is not the intended test.
- Use COUNTIF to check whether an item appears in a range.
- Use conditional formatting when visual identification is more useful than a separate result column.
When a result seems wrong, first inspect the underlying values, not just their display. Look for extra spaces, numbers stored as text, dates with hidden times, formulas returning empty strings, and errors. Also verify that the correct rows are aligned and that copied formulas refer to the intended cells. Testing a few known matches and mismatches makes it easier to confirm that the rule behaves as intended before applying it across a large dataset.
Frequently Asked Questions
How do I compare two columns in Excel and show Match or Different?
Put each pair of values on the same row, enter the IF comparison shown above in a result column, and fill it down. Confirm that records are aligned before relying on the results.
How do I compare two cells without a formula result column?
Use Conditional Formatting with a formula rule to highlight differences. The rule changes cell appearance but does not write a comparison label or alter the cell values.
Is Excel’s equal comparison case-sensitive?
No. An ordinary equality test is not case-sensitive for text. Use EXACT when uppercase and lowercase letters must be treated as different.
Why do two numbers that look the same compare as different?
They may contain different underlying decimal values, or one may be stored as text. Check the actual values and data types; use a deliberate tolerance or rounding rule if small decimal differences should count as equal.
Can I compare a value with a list on another worksheet?
Yes. COUNTIF can check a range on another sheet as long as the range reference points to that sheet. Use a fixed range when copying the check for multiple lookup values, and verify that text and number formats are consistent.
Related
- How to Share Excel Files for Real-Time Collaboration
- How to Share Excel Files in Google Sheets or Excel
- Link Excel to Excel: How to Connect Workbooks and Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IF vs. IFERROR in Excel: Key Differences and ExamplesIF tests a condition and returns one result when it is true and another when it is false;…
- How to Compare Hours in Excel With Formulas and FormattingTo compare two times in Excel, enter them as real time values and use a comparison formula such…
- Packing List Excel With Formula: Build a Smart ChecklistYes. In an Excel packing list, put each item in column A and its packing status in column…
- Filter Only Bold Text in Excel with a Helper ColumnTo filter for bold cells in Excel, add a helper column that labels each cell as Bold or…
- Excel Formula to Convert Minutes to HoursTo convert a number of minutes in cell A2 to decimal hours in Microsoft Excel, divide by 60:…
- VLOOKUP Excel Example: Look Up Data Across Two SheetsTo look up a value from one Excel sheet in another, use VLOOKUP with the source sheet in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.