To compare two Excel sheets with the same layout, put this formula in a blank column beside the first sheet’s data, then fill it down: =IF(A2=Sheet2!A2,"Match","Different"). It compares the cells in row 2 and reports whether they agree; change the references to match your sheets. For sheets with rows in a different order, compare records using a shared key, such as an ID, rather than comparing cell positions.
Choose the right comparison method
The best method depends on what you mean by “compare.” You might need to find changed values in two copies of a report, identify records added or removed, or check whether formulas and formatting differ. A cell-by-cell formula is quick when both sheets use the same structure. A key-based lookup is better when rows may have moved. For a one-time visual check, displaying two sheets side by side can be enough.
Before comparing, decide what counts as a difference. For example, should 12 and 12.00 count as equal? Should a blank cell match a formula that displays an empty string? Do capitalization or spaces matter? Excel’s ordinary equality tests treat text comparisons as case-insensitive, and values that look alike may differ internally. Defining the rules first helps prevent a comparison from flagging harmless differences or overlooking important ones.
- Same rows and columns: compare matching cell addresses with a helper formula.
- Rows may be reordered: use a unique identifier to find the corresponding record.
- Need to inspect visually: open both sheets or workbooks and arrange them side by side.
- Need to compare formulas or formatting: use a method that checks those properties, not just displayed values.
Compare matching cells with a formula
For a straightforward comparison, the two sheets should have the same columns in the same order, and each row should represent the same record. In Microsoft 365 for Windows, insert a new column next to the data on the first sheet. Enter a formula on row 2, then use the fill handle or copy and paste it down the rows you want to check. The sheet name in a formula must be written exactly as it appears on its tab. If a sheet name contains spaces, enclose it in single quotation marks, as in 'April Data'!A2.
Assuming the first sheet is named Sheet1 and the second Sheet2, and the values to compare are in column A on both sheets, enter this in Sheet1!B2:
With corresponding values in Sheet1!A2:A100 and Sheet2!A2:A100, enter in Sheet1!B2: =IF(A2=Sheet2!A2,"Match","Different"). If A2 contains North in both sheets, the result is Match; if Sheet2!A2 contains South, the result is Different.
To compare several columns, either add a result column for each field or combine the cell tests. Separate result columns make it easier to see which field changed. If you need one overall result for columns A through C, a formula can test all three corresponding cells. With the records in A2:C100 on both sheets, enter this in Sheet1!D2: =IF(AND(A2=Sheet2!A2,B2=Sheet2!B2,C2=Sheet2!C2),"Match","Different"). It returns Match only when all three pairs agree. Fill the formula down to assess the other rows.
These comparisons check cell values, not whether the original formulas, number formats, colors, comments, or data validation rules are the same. A displayed number can also hide differences in underlying precision: two cells formatted to show two decimal places may display 4.50 even if their stored values are slightly different. If that distinction matters, inspect the underlying values and decide what tolerance is acceptable for your work.
Find differences when rows are out of order
A row-by-row comparison can report many false differences if one sheet has been sorted differently or has inserted rows. Instead, identify a column that uniquely identifies each record, such as an invoice number or employee ID, and look up that key in the other sheet. The key needs to be consistent and unique; a person’s name alone may not be reliable if names repeat or change.
For Microsoft 365 or Excel 2021, XLOOKUP can retrieve the matching value from the second sheet. Suppose the first sheet has IDs in A2:A100 and amounts in B2:B100, while Sheet2 has IDs in A2:A100 and amounts in B2:B100. In Sheet1!C2, use:
With IDs in Sheet1!A2:A100, amounts in Sheet1!B2:B100, and corresponding IDs and amounts in Sheet2 columns A and B, enter in Sheet1!C2: =XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"Not found",0)=B2. If ID 104 has an amount of 250 on both sheets, the formula returns TRUE; if the amount differs, it returns FALSE. If the ID is absent from Sheet2, it returns FALSE because the lookup returns Not found.
Fill that formula down to check each record. You can add a separate lookup to identify missing IDs, rather than treating every FALSE result as a changed amount. With the same ID ranges and the ID to test in Sheet1!A2, enter in Sheet1!D2: =COUNTIF(Sheet2!$A$2:$A$100,A2)=0. If ID 104 occurs in Sheet2, the result is FALSE; if it does not occur, the result is TRUE. This checks whether the key is missing, not whether the rest of the record matches.
XLOOKUP returns the first match it finds. If a key occurs more than once, a lookup may not compare the intended records, so check for duplicates or build a genuinely unique key from multiple fields. XLOOKUP is available in Microsoft 365 and Excel 2021 and later, but not in some older Excel releases. In an older version, INDEX and MATCH can perform a similar lookup, though the formula is longer.
Highlight differences visually
For a visual review, you can place the two sets of values next to each other in a comparison sheet, then use conditional formatting to highlight mismatches. Copying values into a separate comparison area avoids changing the source sheets. Select the range to format, and choose Home > Conditional Formatting > Highlight Cells Rules for built-in rules, or Home > Conditional Formatting > New Rule to create a formula-based rule.
Conditional formatting formulas generally need to refer to cells on the sheet being formatted; a direct reference to another worksheet is not accepted in many such rules. One dependable approach is to put the two sets of values in adjacent columns on the same comparison sheet, select the first set of cells, and create a formula rule that compares each selected cell with its neighbor. For example, if the copied values are in A2:A100 and B2:B100, select A2:A100 and use this rule formula: =A2<>B2. The rule highlights a cell in column A when it differs from the corresponding cell in column B. You can apply a similar rule to the second column if you want both sides highlighted.
Another simple visual aid is to filter a helper column containing Match or Different, or TRUE and FALSE, so only discrepancies remain visible. This is often easier to audit than relying on color alone, particularly in a long list or when a workbook will be printed. Keep the original data unchanged until you have reviewed the flagged rows.
View two sheets or workbooks side by side
When the data is small or the differences need human judgment, viewing both versions at once can be useful. If both sheets are in one workbook, open the View tab and choose New Window to create another window for that workbook. Then use View > View Side by Side when the two windows are open, and select the sheet you want to inspect in each window. If comparing separate workbooks, open both files first and use the same side-by-side command. Synchronous Scrolling, when available in the side-by-side view, keeps the windows moving together as you scroll.
Visual inspection works well for a few rows, notes, or layout changes, but it is easy to miss a difference in a large range. It also does not automatically tell you which records are missing or changed. Use formulas or a dedicated comparison tool when completeness matters. On Excel for Mac, window-arrangement options differ by version and operating system; you can use the View tab if the side-by-side controls are available, or arrange the workbook windows using macOS window controls. Excel for the web has fewer desktop window and comparison controls, so opening separate browser windows may be more practical.
What a comparison does—and does not—check
A comparison formula usually answers a narrow question: are these two cell values equal under Excel’s comparison rules? It does not automatically establish that two sheets are identical in every respect. Differences can exist in formulas, formatting, hidden rows, column widths, named ranges, charts, or workbook settings even when visible values match. Conversely, two different formulas can produce the same result today.
Consider a sales sheet where one version shows a total of 500. One sheet may contain a typed value of 500 while the other uses a SUM formula that currently returns 500. A value comparison says they match, but the formulas differ and may behave differently when the source data changes. If formula consistency is important, inspect formulas separately by showing formulas on the sheets or comparing the formula text in a suitable audit process. Do not assume a cell-value result proves that workbook logic is identical.
Also account for expected changes. A timestamp, refresh time, or automatically updated report field can differ every time a file is generated. Document those fields and exclude them intentionally, rather than dismissing all mismatches. For numerical measurements, decide whether exact equality is necessary or whether a defined tolerance is appropriate. The right rule depends on the data and its purpose.
Common problems and ways to avoid them
- Wrong sheet reference: Check the tab name and cell address. A misspelled sheet name or shifted column can make every result misleading.
- Different row order: Do not compare row 25 to row 25 unless both rows are known to represent the same record. Match on a stable identifier instead.
- Duplicate IDs: A lookup may return the first matching record only. Verify uniqueness before treating the result as conclusive.
- Extra spaces or inconsistent text: “North” and “North ” may look alike but are not the same text. Clean data deliberately before comparing; do not remove spaces if they are meaningful.
- Numbers stored as text: A value that looks numeric may be stored as text in one sheet and as a number in the other. Check cell formats and data types if results seem surprising.
- Blanks and formulas returning empty text: A truly empty cell and a formula displaying an empty string are not always equivalent for every test. Decide how these should be treated.
- Editing the source by accident: Put helper formulas in a new column or a copy of the workbook. Save a separate comparison copy if the files are important.
For large comparisons, keep the ranges limited to the actual data rather than using entire columns unnecessarily, especially when formulas are copied across many sheets. After checking the results, filter for differences and verify a sample against the source cells. If the result will support a consequential decision, use a documented process and have another person review the comparison criteria.
Frequently Asked Questions
Can Excel compare two sheets automatically?
Excel formulas can compare values automatically, and conditional formatting can highlight differences. Excel does not make every aspect of two sheets identical or different through one universal comparison command; choose a method based on whether you need to compare values, formulas, formatting, or records.
How do I compare two Excel sheets and highlight differences?
Put corresponding values beside each other on a comparison sheet, select the range to format, and create a conditional formatting rule such as a comparison between the two adjacent cells. For a dependable audit trail, also use a helper column that labels each pair as a match or difference.
Can I compare sheets when the rows are in a different order?
Yes. Match each record by a unique identifier and use XLOOKUP in Microsoft 365 or Excel 2021 and later to retrieve the corresponding value. A direct row-by-row comparison is not reliable when records have been sorted differently.
Does the comparison formula check formulas and formatting?
No. A formula such as an equality test compares cell values according to Excel’s comparison rules. It does not establish that the underlying formulas, formatting, comments, or other worksheet features are identical.
How can I compare sheets in different workbooks?
Open both workbooks and use sheet references that include the other workbook, or copy the relevant data into a comparison workbook. For a quick visual check, arrange both workbook windows side by side. Keep both files accessible when formulas refer to an external workbook.
Related
- Flash Fill in Excel: How to Fill Data by Pattern Correctly
- Flash Fill in Excel: Fill to the Last Row
- Flash Fill in Excel: How It Works and When to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Merge Content of Two Cells in Excel: Keep Both ValuesTo combine the contents of two cells in Excel without discarding either value, enter a formula in a…
- How to Divide in Excel Without Getting a Date ResultTo divide in Excel without seeing a date, enter a formula such as =A2/B2, then change the result…
- How to Set Up Automatic Email Forwarding in OutlookTo automatically forward every new message in Outlook, open Settings, go to Mail > Forwarding, turn on forwarding,…
- Excel Consolidate Worksheets Into One: Methods & TipsYes. To combine rows from several worksheets into one list, use Power Query’s Append feature, or use the…
- Google Sheets Date Math: Formulas, Examples, and TipsGoogle Sheets date math works by treating dates as serial numbers, so you can subtract one date from…
- Excel: Find Where a Cell Is Used Across SheetsTo see which formulas use a cell in Excel for Microsoft 365 on Windows, select it and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.