To compare corresponding cells on two Excel sheets, enter =IF(Sheet1!A1=Sheet2!A1,"Same","Different") in a comparison cell, then fill the formula across and down. For lists whose rows may be in a different order, compare records by a unique ID instead of comparing cells in the same positions.
Choose a comparison method
The right method depends on what “compare” means for your workbook. If both sheets have the same layout and records in the same order, a cell-by-cell formula is simple and transparent. If rows may have moved, match records using a key such as an employee number, product code, or invoice ID. If you need to inspect formatting or compare entire workbooks, a visual or dedicated comparison tool may be more appropriate.
Before comparing, decide what counts as a difference. You might care about a changed number, a missing record, a formula that differs even when its displayed result is the same, or a formatting change. A single method does not necessarily detect all of these. For instance, a formula that compares cell values will not tell you whether a cell’s fill color or number format changed.
- Same positions and structure: compare matching cells with formulas.
- Same records, different order: look up each record by a unique key.
- Need a quick visual review: show sheets or workbooks side by side.
- Need formatting or structural differences: consider Excel’s Inquire tools if your edition includes them.
Compare cells in the same positions
For a direct comparison, make a third sheet called Compare, or use an unused area of one of the existing sheets. The formula below assumes the sheets are named Sheet1 and Sheet2 and that the first cells to compare are A1 on both sheets. Put it in the corresponding cell on the comparison sheet, then fill it over the full area you want to check.
=IF(Sheet1!A1=Sheet2!A1,"Same","Different")
If A1 on Sheet1 contains 125 and A1 on Sheet2 contains 130, the result is Different. If both contain 125, the result is Same. When copying the formula to the next cell, Excel adjusts the references to compare the corresponding cells, such as B1 with B1. To compare a range that starts at another location, start the formula with those corresponding cell addresses instead.
Sheet names containing spaces or punctuation need single quotation marks around the name. For example, a reference to cell A1 on a sheet named January Data is written as 'January Data'!A1. A comparison using that sheet and a sheet named February would be =IF('January Data'!A1=February!A1,"Same","Different"). You can also type an equals sign in a cell and click the referenced sheet and cell while building a formula; Excel inserts the reference for you.
For a large range, fill the formula by dragging the fill handle, or copy the first formula, select the destination range, and paste. Check the top, bottom, and edges of the result to confirm the compared ranges line up. A formula filled one column or row too far can create misleading differences or compare cells that are not part of the intended data.
Highlight differences with conditional formatting
A comparison formula is useful for finding changes, but a sheet full of “Same” and “Different” labels can be hard to scan. A practical approach is to put the comparison results in a helper area and apply conditional formatting to highlight the differences. This also avoids a common limitation: Excel’s conditional-formatting formula rules do not accept an ordinary direct reference to another worksheet in the same way a cell formula does.
Suppose the cell-by-cell results are in C1:C100, with each result corresponding to the same row in the data. Select C1:C100, then choose Home > Conditional Formatting > Highlight Cells Rules to apply a rule to text containing “Different,” or use Home > Conditional Formatting > New Rule to create a formula-based rule. For a rule that colors the original data area, assume the status for row 1 is in C1 and select the cells to format, such as A1:B100. Use this formula:
=$C1="Different"
The dollar sign fixes the status column while allowing the row number to adjust. Choose a format, such as a fill color, and confirm the rule. Now a row’s original cells can be highlighted when its comparison status is Different. Keep the helper results visible or add a clear label so another person can understand why a cell is colored.
Compare records when row order changes
Cell-by-cell comparison only works when corresponding records occupy the same positions. If a record appears on row 12 in one sheet and row 15 in another, a position-based formula compares it with the wrong record. Instead, match a unique identifier first, then compare the associated value.
For example, assume Sheet1 has IDs in A2:A100 and sales amounts in B2:B100. Sheet2 has the same kind of IDs in A2:A100 and amounts in B2:B100, but its rows may be ordered differently. Enter this formula in C2 on Sheet1, then fill it down:
=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing",IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Same","Different"))
The formula reports Missing if the ID from A2 does not occur in Sheet2’s ID range. Otherwise, it finds that ID on Sheet2 and compares the matched sales amount with B2. If ID 104 has an amount of 125 on Sheet1 and 130 on Sheet2, the result is Different; if both amounts are 125, it is Same. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; in older Excel versions, a lookup can be built with INDEX and MATCH instead.
This method assumes the ID is unique. If an ID appears more than once, XLOOKUP returns the first matching record, so the result may not represent all duplicates. Clean or combine duplicate records, or use a key made from multiple fields when one column alone does not uniquely identify a row. Also check that IDs use consistent data types: a number stored as text may not behave like the same digits stored as a number.
Use Excel tools for visual or workbook comparisons
For a quick visual check, open both sheets or workbooks and use View > View Side by Side in Excel for Windows. When the two windows are available, Excel can scroll them together with Synchronous Scrolling, making it easier to inspect corresponding areas. This is a visual aid, not an automated report: it will not reliably find every difference in a long worksheet, and it does not match records that have moved to different rows.
Some Windows editions of Excel include the Inquire add-in, which provides a Compare Files feature for reviewing differences between workbooks. Availability depends on the Office edition and installation. To check, choose File > Options > Add-ins, select COM Add-ins in the Manage box, and choose Go. If Inquire is available, enable it and look for the Inquire tab and its Compare Files command. The comparison is intended for workbooks; it can report changes such as cell values, formulas, and formatting, depending on the report and options. Inquire is not generally available in Excel for the web or Excel for Mac, so use formulas or a visual review on those platforms.
Before using any tool that compares whole workbooks, make sure you have the correct versions open and understand whether the output is a report or a modified file. Comparison reports can contain sensitive workbook contents, so save and share them using the same care as the original data.
Understand what a formula comparison sees
The basic equality test compares the values Excel evaluates in the referenced cells. Consequently, two cells containing different formulas can still report Same if those formulas currently return the same result. For example, one cell might calculate a total with SUM and another might contain the resulting number. A value comparison is appropriate when the question is whether the displayed data agrees, but it is not a formula-audit check.
Similarly, Excel’s ordinary equals comparison is not case-sensitive for text: “North” and “north” are treated as equal. If capitalization matters, use the case-sensitive EXACT function. Assuming the cells to compare are A1 on Sheet1 and A1 on Sheet2, use =IF(EXACT(Sheet1!A1,Sheet2!A1),"Same","Different"). This is useful for codes or identifiers where case is meaningful, but it is not a substitute for checking data types or whitespace.
Blank cells deserve attention. Two genuinely blank cells compare as equal, and a formula that returns an empty string may appear blank while still containing a formula. If one sheet uses formulas that return empty strings and the other has truly empty cells, decide whether that distinction matters to your task. Also be aware that errors such as #N/A can make a simple equality comparison return an error rather than Same or Different. Resolve or explicitly account for errors before treating a comparison report as complete.
Other common causes of unexpected differences include leading or trailing spaces, numbers stored as text, dates with different underlying values, and formulas that depend on changing data. Do not round or clean values automatically unless the comparison rules permit it: small decimal differences may be meaningful in one dataset and irrelevant in another. When a difference is surprising, inspect the source cells and their formula bars rather than relying only on their appearance.
A careful workflow for reliable results
Good comparisons begin with consistent data. Keep an untouched copy of each source sheet, confirm that headers and columns mean the same thing, and decide whether you are comparing displayed values, formulas, formatting, or complete records. A worksheet comparison can only answer the question its formulas and rules encode.
- Confirm the two sheets cover the same period, categories, and intended records.
- Choose position matching for identical layouts or a unique key for reordered lists.
- Compare the necessary fields, not just one convenient column.
- Review Missing results separately from Different results; they indicate different problems.
- Investigate a sample of matches and differences against the source data.
- Label the comparison sheet with the source names and date so the results remain interpretable.
For repeat work, keep the comparison formulas and a short note explaining the key fields in a reusable template. If the data grows, update the referenced ranges or convert the source ranges into Excel tables so the comparison process is easier to maintain. Avoid overwriting source data with comparison results; separating inputs from outputs makes mistakes easier to spot and preserves a clear audit trail.
Frequently Asked Questions
Can Excel automatically tell me which cells are different?
Yes. A cell-by-cell IF formula can label corresponding values Same or Different, and conditional formatting can highlight those results. For workbooks with different row orders, match records by a unique ID first.
How do I compare two sheets with different row orders?
Use a shared unique identifier, such as an account or product ID, to find each record on the other sheet. Then compare the fields you care about. A straight cell-by-cell comparison is not reliable when rows have moved.
Can I compare two Excel sheets without formulas?
Yes. View the sheets side by side for a manual inspection, or use the Inquire Compare Files feature if it is included in your Windows Excel edition. These options have limits: side-by-side review is manual, and Inquire is not available in every edition or on every platform.
Does Excel treat uppercase and lowercase text as different?
No. The equals operator used in a basic comparison is not case-sensitive. Use EXACT when capitalization itself must match, and separately check for spaces or inconsistent data types if those matter.
Can a comparison formula find formatting changes?
No. The formulas described here compare cell contents or results, not font, fill, borders, or number formats. Use a suitable workbook comparison tool where available, or inspect formatting separately.
Related
- Excel Remove Duplicates: Is Matching Case-Sensitive?
- Excel Remove Duplicates From Multiple Columns: Steps
- Excel Remove Duplicates Based on One Column, Keep Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Freeze a Number in an Excel Formula with $ ReferencesTo freeze a number’s cell reference when you copy an Excel formula, add dollar signs to the reference—for…
- How to Add a New Line in Excel CONCATENATETo add a new line between text values joined with Excel’s CONCATENATE function, insert CHAR(10) between the cell…
- Excel INDEX Function Examples for Everyday LookupsExcel’s INDEX function returns the value at a specified row and column within a range. For example, with…
- INDEX MATCH With Two MATCH Functions: Practical ExamplesYes: the standard two-way lookup uses INDEX with one MATCH to find a row and a second MATCH…
- Excel Protect Sheet: Allow Users to Filter and SortYes. In Excel for Microsoft 365 on Windows, turn on the filter before protecting the worksheet, then choose…
- How to Group Names in Excel: Sort, Count, or CollapseTo group names in Excel, sort the list by the name or category column; to make rows collapsible,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.