To compare cells in the same positions on two worksheets, enter =IF(January!A2=February!A2,"Match","Different") in a results cell, then fill the formula down. This compares January!A2 with February!A2 and labels the result; use the worksheet names from your workbook in place of January and February.
Choose the right kind of comparison
The first question is whether each cell on one sheet should be compared with the cell in the same position on the other sheet. If both lists are in the same order and each row represents the same record, a direct cell-to-cell formula is usually the simplest method. You can copy it across columns and down rows to compare a whole range.
If the sheets contain the same records but in different orders, comparing A2 with A2 may produce misleading differences. In that case, match rows using a shared identifier, such as an employee number or product code, and compare the values belonging to that identifier. Decide, too, whether capitalization, empty cells, and duplicates matter. Those choices affect which formula gives a useful answer.
- Same rows and columns: compare corresponding cells directly.
- Same records, different order: look up records by a key before comparing their values.
- Need a visual scan: create a helper column that labels results, then apply conditional formatting to it.
- Need to find missing records: compare the key lists as well as the data values.
Compare cells in the same positions
Suppose the worksheets are named January and February, and you want to compare their values in column A. With the result placed in C2 and the cells to compare in January!A2 and February!A2, use =IF(January!A2=February!A2,"Match","Different"). It returns Match when Excel considers the two values equal and Different otherwise. Drag the fill handle down to compare more rows. To compare another column, copy the formula sideways; Excel adjusts the cell references.
For a worksheet name containing spaces, surround the name with single quotation marks. With values in A2 on the tabs named January Data and February Data, use =IF('January Data'!A2='February Data'!A2,"Match","Different"). The quotation marks are part of Excel’s worksheet-reference syntax; they are required for names with spaces and can also be used for names without spaces.
For a numeric comparison, the formula can return the amount of the difference rather than a label. With numeric values in January!A2 and February!A2, enter =January!A2-February!A2. If the first cell contains 125 and the second contains 120, the result is 5; a negative result means the February value is larger. This is useful for amounts or counts, but it is not a general replacement for a match test because subtracting text values produces an error.
Flag differences and make them easy to scan
A helper column makes a long comparison easier to review. Put a heading such as Status above the results and use the comparison formula for each row. Then select the result cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Different and select a format. Excel highlights those result cells, while the formula still provides a readable status.
You can also show the compared values side by side and sort or filter the helper column to focus on differences. Keep the source sheets unchanged if they are official exports or reference data; placing formulas on a separate review sheet can make it clearer which cells are original and which are analysis. Conditional formatting changes how cells look, not the underlying values, so a color should not be treated as a substitute for checking the result.
Direct cross-worksheet references in conditional-formatting rules can be restricted. A helper column avoids that complication: calculate the comparison in an ordinary cell, then format the local result. If you want to color the original data cells rather than the status cells, you may need a named range or a formula-based rule that refers to an allowed reference; test the rule on a small range first.
Handle capitalization and blank cells
Excel’s ordinary equality comparison does not distinguish uppercase from lowercase letters. For example, January!A2 containing North and February!A2 containing north will ordinarily count as a match. If capitalization is meaningful, use the EXACT function instead. With the text values to compare in January!A2 and February!A2, enter =IF(EXACT(January!A2,February!A2),"Match","Different"). EXACT returns TRUE only when the text strings have the same characters and capitalization.
Blank cells need a little more care. A basic equality test can report two empty cells as a match, which may be correct—or may conceal a missing value that deserves attention. With the cells being compared in January!A2 and February!A2, use =IF(AND(January!A2="",February!A2=""),"Both blank",IF(January!A2="","Missing in January",IF(February!A2="","Missing in February",IF(January!A2=February!A2,"Match","Different")))). This distinguishes two blanks from a blank on only one sheet, then compares the remaining values.
That blank check also treats a formula whose result is an empty string as blank. If it matters whether a cell is genuinely empty versus containing a formula that displays nothing, use Excel’s ISBLANK function as a separate check. Also look for spaces: a cell containing a space is not the same as an empty cell. TRIM can remove ordinary extra spaces from text, but applying cleanup changes the comparison question, so keep the original data available.
Compare records when the rows are reordered
A direct formula compares positions, not identities. If the second sheet has been sorted differently, use a unique key to locate the corresponding row. For example, assume the first sheet has product codes in A2:A100 and prices in B2:B100; the second sheet, named Price List, has codes in D2:D100 and prices in E2:E100. In C2 on the first sheet, enter =IFERROR(IF(B2=XLOOKUP(A2,'Price List'!D:D,'Price List'!E:E),"Match","Different"),"Not found"), then fill it down.
XLOOKUP searches for the code from A2 in the second sheet’s D column and returns its corresponding price from E. IF compares that returned price with B2; IFERROR labels a code that is not found. For example, if code P-104 has a price of 19.99 on both sheets, the result is Match. If the code exists on the first sheet but not the second, the result is Not found. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may require a different lookup approach.
Choose a key that identifies one record unambiguously. If a code appears multiple times, a lookup can return one matching occurrence without proving that every duplicate row agrees. In that situation, compare the duplicate counts or make a composite key from multiple fields, and inspect the records before relying on a single lookup result.
Understand what Excel considers equal
Two cells can look alike on screen without storing the same kind of value. A number displayed with two decimal places may have additional precision behind the formatting. A date is stored as a number, and different number formats can make the same underlying value appear different. Conversely, a number stored as text may look identical to a numeric value but behave differently in calculations.
If results seem surprising, inspect the formula bar and the cells’ number formats. Check for leading or trailing spaces, different date values, and numbers imported as text. Decide whether you need to compare the stored values exactly or compare normalized values after cleaning them. Do not round or trim data automatically unless that matches the purpose of the comparison: rounding can hide a real difference, and removing spaces can change meaningful text.
Also confirm that the formula points to the intended worksheets and rows. A copied formula may shift references as it is filled. If a reference must stay fixed while the formula moves, use an absolute reference such as $A$2; if only the column or row should stay fixed, use $A2 or A$2. Check a few results manually against the source cells before treating a large comparison as complete.
Other ways to compare sheets
For a small table, side-by-side review may be enough: arrange the relevant columns next to each other and use a helper formula for ambiguous rows. For a repeatable check, formulas are convenient because they recalculate when the source data changes. Conditional formatting is helpful when the main goal is to spot differences visually, but the formulas should remain available for auditing and filtering.
For larger or recurring comparisons, Power Query can load tables, merge them using a key, and make it easier to review unmatched records. Its controls and workflow differ from cell formulas, and the query generally needs refreshing after source data changes. Excel’s Spreadsheet Compare add-in is another option in some Microsoft 365 or Office editions, but it is not available in every installation. Check your Excel edition and available add-ins before planning around it. For a one-off comparison, a simple formula is usually more transparent than setting up a specialized tool.
Frequently Asked Questions
Can Excel compare two entire sheets at once?
There is no single basic cell formula that checks every possible difference between two complete worksheets. You can fill a comparison formula across the used range, or use Power Query or an available comparison add-in when the sheets are large or the task recurs.
Why does my formula say Different when the cells look the same?
Check for extra spaces, a number stored as text, different underlying date or numeric values, or a reference to the wrong row. Display formatting can hide differences in stored values, so inspect the formula bar and test a few source cells directly.
Does Excel treat uppercase and lowercase text as different?
Not with the ordinary equals comparison used in a basic IF formula. Use EXACT when capitalization must match; it compares text with case sensitivity.
How do I compare sheets that are sorted differently?
Match rows by a shared, preferably unique identifier, then compare the associated values. A direct A2-to-A2 comparison checks row positions and cannot determine whether two differently positioned rows represent the same record.
Can I compare cells across different workbooks?
Yes. Excel formulas can refer to cells in another open or saved workbook, although external links can become harder to manage if files are moved or renamed. For a comparison you will reuse, consider keeping the source files in a stable location and verifying that links still point to the intended workbook.
Related
- Consolidate Option in Excel: How to Combine Data Ranges
- Consolidate Rows in Excel Based on Criteria
- Combine Two Cells Into One in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Create a Mailing List in Outlook: Steps & TipsTo create a mailing list in Outlook, make a contact list in the People section and add the…
- Excel Named Ranges: Can They Cover Multiple Cells?No. In Excel for Microsoft 365, a defined name can refer to one cell, a range of many…
- How to Schedule an Email in Outlook for iOS on iPhoneTo schedule an email in Outlook for iOS, compose your message, tap the three-dot menu, choose Schedule Send,…
- Excel Paste Values Shortcut: Keys, Options, and FixesIn Excel for Microsoft 365 on Windows, copy the cells, select the destination, then press Ctrl+Alt+V, press V,…
- Merge Cells but Keep All Text in Excel: Safe MethodsIn Excel, merging cells normally keeps only the value in the upper-left cell and removes the contents of…
- Excel Keyboard Shortcut to Resize a Column or AutoFitIn Excel for Microsoft 365 on Windows, select a cell in the column and press Alt, H, O,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.