To compare two Excel sheets, use a cell-by-cell formula when the data is in the same order, or match records by a unique key when rows may be rearranged. For a quick check, enter =IF(Sheet1!A1=Sheet2!A1,"Match","Different") in a third sheet and fill it across and down; for larger or differently organized tables, XLOOKUP or Power Query is usually more useful.
Choose a comparison method
The right method depends on what “compare” means for your task. You might need to spot changed amounts in matching rows, find records that exist in only one sheet, check whether two whole tables are identical, or simply inspect two worksheets side by side. These are related tasks, but they do not all call for the same tool.
Start by asking whether corresponding records are in the same row on both sheets. If they are, direct cell comparisons are simple and transparent. If rows might be sorted differently, compare by a stable identifier such as an order number or employee ID instead. For repeated monthly files or large tables, Power Query can make the comparison repeatable rather than relying on manual checks.
- Same layout and row order: use a formula grid to test corresponding cells.
- Same records, different row order: look up records by a key, then compare selected fields.
- Missing records on either side: use a key-based check or a Power Query full outer merge.
- Visual review: display the sheets side by side, while remembering that visual inspection does not automatically identify every difference.
Prepare the sheets before comparing
Make the comparison fair by confirming that both sheets represent the same kind of data and that their columns have clear headings. Check that identifiers are stored consistently: an ID stored as text in one sheet and as a number in another may not match as expected. Also decide whether blank cells, spaces, capitalization, rounding, and formula results should count as differences.
Save a separate copy of the workbook before making extensive changes. If the data is important, keep the original sheets intact and put comparison results on a new worksheet. This makes it easier to review the outcome and trace a flagged value back to its source.
- Remove accidental leading or trailing spaces if they are not meaningful.
- Check for duplicate IDs before using a lookup; a key should identify one record if you expect one-to-one matches.
- Confirm that dates and numbers use compatible values and formats. Two dates can look different because of formatting even when their underlying values match.
- Make sure the compared ranges include all relevant rows and columns, not just the portion currently visible.
Compare cells when the layouts match
Suppose both worksheets have the same structure, with the first value in cell A1 on each sheet and corresponding data continuing in the same positions. On a third worksheet, enter this formula in A1: =IF(Sheet1!A1=Sheet2!A1,"Match","Different"). It returns “Match” when the referenced cells compare equal and “Different” otherwise. For example, if both source cells contain 42, the result is “Match”; if one contains 42 and the other 47, it is “Different.”
Copy the formula across and down to cover the area you want to check. Excel adjusts the relative cell references as the formula is filled, so the next result checks the next pair of cells. If your worksheet names contain spaces, enclose each name in single quotation marks, as in =IF('January Data'!A1='February Data'!A1,"Match","Different"). Use the actual sheet names in your workbook.
To count the differences in a completed comparison grid, use COUNTIF on the result range. With comparison results in A1:Z100 on the third sheet, enter =COUNTIF(A1:Z100,"Different"). If five cells differ, the formula returns 5. This counts differing cells, not necessarily differing records: one row with three changed fields contributes three to the count.
A cell-by-cell formula is not a good choice if one sheet has been sorted independently. It compares positions, not identities. If row 10 refers to a different customer on each sheet, a “Different” result may simply mean the records are in a different order, not that the customer’s data changed.
Match records by a key with XLOOKUP
When rows can move, use a field that identifies each record. For example, suppose both sheets have unique order IDs in column A and amounts in column B, with data in rows 2 through 100. On Sheet1, enter this formula in C2 and fill it down: =IFERROR(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Match","Value differs"),"ID missing"). If order 104 has an amount of 250 in both sheets, the result is “Match.” If order 105 has 180 on Sheet1 and 175 on Sheet2, the result is “Value differs.” If an ID from Sheet1 is absent from the lookup range, the result is “ID missing.”
XLOOKUP searches for the ID in Sheet2 and returns the corresponding amount. The dollar signs keep the lookup ranges fixed as you fill the formula down, while A2 and B2 change for each row. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It returns the first matching result, so duplicate IDs can conceal ambiguity; check key uniqueness or handle duplicates separately. This example checks only records from Sheet1 against Sheet2. To find IDs present only on Sheet2, perform the reverse check as well.
If the sheets contain several fields to verify, make a separate comparison for each field or build a clearly defined combined comparison. Separate checks are easier to interpret because a result can identify whether the amount, date, or status changed. Avoid combining values casually: different combinations of text and numbers can produce ambiguous strings unless separators and data types are handled deliberately.
Highlight differences for faster review
Once formulas identify differences, conditional formatting can make the results easier to scan. Select the comparison-result range, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter “Different” and choose a format, such as a light red fill. You can apply a second rule to the same range for “ID missing.” This highlights the comparison output without changing the source data.
For a same-layout check, the formula grid itself can be the review area: color “Different” results, then inspect the corresponding source cells. Keep labels beside the results or use a separate comparison sheet that records the key, the original values, and the status. A red highlight tells you that a rule was triggered; it does not explain why the values differ, so retain enough context to investigate.
In Microsoft 365 for Windows, conditional formatting is on the Home tab. Excel for Mac has conditional-formatting tools as well, though the ribbon layout can vary slightly by release. Excel for the web also supports common conditional-formatting rules, but some advanced workbook features and editing options can differ from desktop Excel.
Use Power Query for larger or repeatable comparisons
Power Query is useful when you regularly compare exported files or work with tables that are too large to inspect comfortably. In Excel for Microsoft 365 on Windows, load each source through Data > Get Data > From File > From Workbook, select the workbook and the relevant sheet or table, and load the data into Power Query. Repeat for the second source. The exact import choices depend on how the source workbook is organized.
In the Power Query Editor, choose Home > Merge Queries (or Merge Queries as New if you want a separate result), select the two tables, and select the matching key column in each. A Full Outer join keeps rows from both sources, including keys that have no match on the other side. Expand the merged column to bring in the fields you need to inspect. You can then add comparison logic to identify unequal values and load the result back into Excel.
Power Query is not a live cell-by-cell link to the source files. After the source data changes, refresh the query to update the comparison. Check that the selected key is reliable and that duplicate keys are understood: repeated keys can produce multiple joined rows and make the output larger than expected. Power Query is available in Excel for Microsoft 365 on Windows; Mac availability and capabilities depend on the Excel release, and the web version does not provide the same complete authoring experience as desktop Excel.
View two worksheets side by side
For a quick human review, open both workbooks and choose View > View Side by Side. Excel can arrange the open workbook windows together; View > Synchronous Scrolling can keep their vertical scrolling coordinated. This is helpful when you want to inspect layouts, notes, or small sections that are difficult to capture in a formula.
If the sheets are in the same workbook, use View > New Window to open another window for that workbook, then arrange the windows side by side. This displays the same workbook in two windows, allowing you to navigate to different sheets or areas. Side-by-side viewing is a visual aid, not an automated comparison: it may miss a changed value far down a long list, and it does not reliably identify records that have moved.
Common comparison problems
A result that looks wrong often comes from the data or the comparison rule rather than a broken formula. Excel compares stored values, while number formats control how many of those values look on screen. For instance, two cells may both store 1.234 but display as 1.23. Conversely, two values may display similarly while differing beyond the visible decimal places.
- Extra spaces: “West” and “West ” are different text values. A cleanup step may be appropriate if trailing spaces are accidental, but do not remove them if spaces carry meaning.
- Text versus numbers: an ID such as 0012 may be text in one sheet and the number 12 in another. Decide whether leading zeros are significant before normalizing the columns.
- Blank cells and formulas: a cell displaying nothing may contain a formula returning an empty string. Decide whether that should be treated like a truly empty cell.
- Duplicates: a lookup based on a repeated key may return only one matching row, whereas a merge can return multiple combinations.
- Row order: a direct position-by-position check can report many differences after a sort, even when the records themselves are unchanged.
Be explicit about whether you are comparing formulas or their displayed results. A direct reference comparison usually compares the calculated values of cells. If two formulas differ but currently return the same value, a value comparison may say they match; if formula text itself matters, inspect the formulas as text or use a purpose-built auditing approach.
How to choose and verify the result
For a one-time check of two similarly arranged ranges, a formula grid is quick and easy to audit. For tables with a unique identifier and shuffled rows, a lookup formula makes the matching logic clear. For repeat imports, multiple columns, or records missing from either side, Power Query provides a refreshable process. Side-by-side viewing is best for a small visual inspection, not as the only control for critical or extensive data.
Whatever method you choose, verify a sample of both flagged and unflagged records against the original sheets. Check a record that should match, one with a known changed value, and one that should be missing if missing records matter. A comparison is only as trustworthy as its key, range, and definition of “equal.” Keep a short note of those choices if someone else will rely on the results later.
Frequently Asked Questions
Can Excel compare two sheets automatically?
Yes. Formulas can mark matching or different cells, and Power Query can compare tables through a merge. Neither method knows what counts as a meaningful difference until you specify the comparison fields, matching key, and treatment of blanks or duplicates.
How do I compare two Excel sheets and find missing rows?
Match the sheets on a unique ID and check whether each ID appears in the other sheet. A lookup can flag missing IDs in one direction, but repeat the check in reverse to find records present only in the other sheet. A full outer merge in Power Query can show unmatched records from both sides.
Can I compare sheets when their rows are in a different order?
Yes. Do not compare matching cell positions. Use a stable key, such as an invoice number, to find the corresponding record and then compare its values. Confirm that the key is unique if each row is expected to have exactly one counterpart.
Does a cell-by-cell comparison detect different formulas?
Not necessarily. A standard formula comparing two referenced cells generally checks their calculated values, not whether the formulas that produced them are identical. If formula text matters, compare the formulas themselves using an approach designed for formula inspection.
Can I compare two Excel files rather than two sheets?
Yes. Open both files and use formulas that refer to the other workbook, or import both files into Power Query and merge their tables. For repeat comparisons, Power Query is often easier to refresh, but keep source file locations and column structures consistent.
Related
- Encrypt an Excel Spreadsheet: Steps and Security Tips
- Employee Time Tracking Template Excel: Setup and Tips
- X-Arcade Programming Software: What You Need to Know
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Conditional Formatting for Errors in Excel: How to Do ItTo highlight error cells in Excel, select the range, choose Home > Conditional Formatting > New Rule >…
- Excel Consolidate Duplicates and Sum ValuesYes. In Excel for Microsoft 365, use UNIQUE to list each label once and SUMIF to add the…
- Copy Excel Formulas to Google Sheets Without ErrorsTo copy a formula from Excel into Google Sheets, copy the formula cell in Excel, select the destination…
- Excel Can’t Delete a Named Range? Causes and FixesYes, you can delete a named range in Excel for Microsoft 365 on Windows: choose Formulas > Name…
- Double Space Shortcut in Word: Windows and MacIn Microsoft Word for Windows, press Ctrl+2 to double-space the selected text or the paragraph where the cursor…
- Excel Trendlines: Which Type Should You Use for Your Data?Use a linear trendline when your data changes at a roughly steady rate; choose another type only when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.