Compare Data in Two Excel Sheets: Methods and Checks

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, you can compare two sheets by checking matching cells with a formula, looking up records by a shared ID, highlighting differences with conditional formatting, or merging the data in Power Query. The best method depends on whether the rows line up and whether you need a quick check or a repeatable comparison.

What comparing two sheets can reveal

A comparison can answer several different questions: Are corresponding cells identical? Did a value change for a particular customer or product? Are any records missing from one list? Do the sheets have the same structure? These are related tasks, but they are not interchangeable. A cell-by-cell check can be fast when the rows match exactly, yet misleading if one sheet has been sorted or has extra records.

Before comparing, decide what counts as a match. It might mean identical text and numbers, the same customer ID regardless of row order, or agreement on selected fields such as name and balance. Also decide whether differences in capitalization, spaces, date display, or rounding matter. Making these choices first prevents a result that looks precise but answers the wrong question.

Choose a method based on the data

Use the structure of the sheets to select an approach:

  • Same records in the same row order: compare corresponding cells or rows with formulas.
  • Same records, different order: match records using a unique identifier, then compare the fields that matter.
  • Need a visual scan: use conditional formatting to highlight changed cells, provided the rows are aligned.
  • Large or recurring comparisons: use Power Query to merge tables and identify unmatched records.
  • Need to inspect workbook structure or formulas: a workbook comparison tool may be more appropriate than a data-only check.

For a one-time, small comparison, a formula is often easiest to understand and audit. For repeated monthly files, a saved Power Query workflow can reduce repetitive manual work.

Compare cells when rows are aligned

If both sheets contain the same records in the same order, place a comparison formula in a third sheet or an unused column. For example, assume Old and New are the sheet names, the values to compare are in A2 on each sheet, and the result goes in C2 of a results sheet. Enter =IF(Old!A2=New!A2,"Match","Different"). If both cells contain 125, the result is Match; if Old!A2 contains 125 and New!A2 contains 130, it is Different. Copy the formula down to check more rows.

To compare an entire row, with the relevant fields in columns A and B on both sheets and row 2 being the first data row, enter this in the results sheet’s C2: =IF(AND(Old!A2=New!A2,Old!B2=New!B2),"Match","Review"). If both pairs agree, the result is Match; if either field differs, it is Review. Add more comparisons inside AND when additional columns must agree. This approach assumes the rows correspond; it does not find the same record elsewhere in the other sheet.

After entering a formula, use the fill handle to copy it down. Check the first and last results and confirm the formula references moved as intended. If you use tables, structured references can make formulas easier to read, but the basic principle is the same: compare like fields from records that have already been aligned.

Match records by a shared identifier

When row order differs, compare by a stable key such as an order number, employee ID, or product code. The key should identify one record uniquely. Names are often poor keys because two people can share a name, and a person’s name may be entered differently in the two sheets.

For example, assume Old has IDs in column A and amounts in column B, while New has the same fields in columns A and B. In Old’s row 2, enter =IFERROR(IF(B2=XLOOKUP(A2,New!A:A,New!B:B),"Match","Different"),"Missing ID"). If Old row 2 has ID 104 and amount 250, and the matching row in New has ID 104 and amount 275, the result is Different. If ID 104 is absent from New, the result is Missing ID. Copy the formula down to assess the other Old records.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. This example assumes each ID appears only once in New; duplicates can make the lookup ambiguous because XLOOKUP returns a matching result rather than reconciling duplicate records. To find IDs in Old that are absent from New, with IDs in column A on both sheets and the formula entered for Old!A2, use =IF(COUNTIF(New!$A:$A,A2)=0,"Missing from New","Present"). For an audit, run the corresponding check in the other direction too: a record can be missing from Old even when every Old ID exists in New.

Highlight differences with conditional formatting

Conditional formatting makes changes easier to scan, but it does not establish which rows represent the same record. It is most useful when the two ranges are aligned. Select the cells to format, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to flag repeated values within a selected range. For a direct comparison between two sheets, a formula-based rule can be set up through Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Select the target range first, and base the rule on the corresponding cell on the other sheet; Excel may require a helper formula on the same sheet if a direct cross-sheet reference is rejected by the rule dialog.

Choose a clear fill color and test a known matching cell and a known changed cell. Color is a visual cue, not a record of what changed or why. For a long list, a separate result column with labels such as Match and Different is generally easier to filter, count, and explain than color alone.

Use Power Query for repeatable comparisons

Power Query is useful when the lists are large, arrive regularly, or need a clear check for records found on only one side. In Excel for Microsoft 365 on Windows, convert each dataset to a table with Insert > Table, give the tables clear names, and load them to Power Query using Data > From Table/Range. In the Power Query Editor, use Home > Merge Queries, select the shared key in each table, and choose a join type that fits the question.

A left anti join returns rows from the first table that have no matching key in the second; reversing the table order finds records present only in the second. An inner join brings together records whose keys match, after which you can compare selected fields. Expand the columns you need, inspect the results, and choose Home > Close & Load to return the output to Excel. When the source tables are updated, use Data > Refresh All to refresh the query.

Power Query does not automatically decide what a meaningful difference is. You still need to choose the key, account for duplicate keys, and select which columns to compare. It is also a transformation workflow, so keep the original source data intact and review the query output before relying on it.

Common problems that cause false differences

  • Rows are not aligned: a direct cell comparison reports differences after a sort even if the records are all present. Match by key instead.
  • Duplicate or missing keys: repeated IDs can produce ambiguous matches; check key uniqueness and compare in both directions for missing records.
  • Spaces and inconsistent text: leading or trailing spaces, different punctuation, or inconsistent capitalization can affect matching. Decide whether to clean the data before comparing.
  • 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 rather than trusting how the cells look.
  • Dates and displayed rounding: dates are stored as serial values and may display differently because of formatting. Two amounts may look the same after rounding while their underlying values differ.
  • Blanks, zeros, and errors: decide whether an empty cell is different from zero, and inspect error values separately. A comparison result should not be treated as proof that every underlying value is clean.

If capitalization must count as a difference, ordinary equality comparisons may not be sufficient; Excel’s EXACT function performs a case-sensitive text comparison. If capitalization should not matter, normalize the text consistently before matching. Either way, document the rule so someone reviewing the workbook understands what Match means.

Verify and share the results

Before acting on a comparison, check a few known examples: one match, one changed value, one key missing from each direction, and any duplicate key. Filter the result column to Different or Missing, then compare those records with the source sheets. Confirm that the formulas cover all data rows and that new rows are included; a fixed range can silently omit records added later.

Keep the source sheets unchanged, label the comparison date and method, and save a separate results copy if the findings matter to a business process. If the workbook contains sensitive information, follow the organization’s rules for access and sharing. A comparison identifies differences; it does not explain their cause or determine which version is correct.

Other comparison options

For a workbook-level review, Microsoft’s Spreadsheet Compare tool can compare workbooks and report differences in cells and formulas, but its availability depends on the Office edition and installation. On Windows, the Inquire add-in may provide workbook comparison features in supported editions; it is not a universal button in every Excel installation. Check the installed Excel features before planning around it. On Mac and Excel for the web, menus and feature availability can differ, so formulas or Power Query may be more practical when the same comparison needs to work across platforms.

For a small task, you can also place the sheets side by side using View > New Window and View > View Side by Side, then inspect corresponding areas manually. This helps with visual review but is slow and easy to misread in large datasets. It is best used as a secondary check, not as a substitute for a systematic comparison.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Excel does not apply one universal comparison rule automatically. You can create formulas, use conditional formatting, or build a Power Query merge, but you must specify which cells or records to compare and what counts as a match.

How do I compare two sheets when the rows are in a different order?
Use a shared, preferably unique identifier to find the corresponding record, then compare the fields you care about. A direct row-by-row formula is only reliable when the rows are already aligned.

Can I find rows that are missing from one sheet?
Yes. Check each sheet’s key column against the other, using COUNTIF, a lookup formula, or a Power Query anti join. Check both directions to find records missing from either list.

Why does Excel show a difference when the cells look the same?
The cells may contain different underlying values, extra spaces, text instead of numbers, different date values, or more decimal precision than the display shows. Inspect the formula bar, data types, and formatting.

Can I compare sheets in Excel for Mac or Excel for the web?
Many worksheet formulas work across Excel platforms, but ribbon locations and feature availability can differ. Power Query and workbook-comparison tools are not identical across Windows, Mac, and the web; check the options in your installed version.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.