Compare Two Excel Sheets for Matches and Differences

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365, you can compare two sheets by checking whether each key value appears on the other sheet, then use a lookup to compare related details. For a straightforward check, put this formula beside the first list and fill it down: =IF(COUNTIF('April List'!$A$2:$A$100,A2)>0,"Match","No match"). It marks each value in the current sheet as present or absent in the sheet named April List.

What Counts as a Match?

Before comparing, decide what “match” means for your data. It might mean that a customer ID appears in both sheets, that two rows are identical, or that a particular value—such as a price or status—is unchanged. These are different questions, and the right method depends on which one you need to answer.

A common task is comparing two lists that are not in the same order. For example, a January sheet might contain customer IDs 1042, 1048, and 1061, while a February sheet contains 1061, 1042, and 1070. Comparing row 2 with row 2 would give a misleading result because the IDs have moved. Instead, use a unique key, such as the customer ID, to find corresponding records wherever they appear.

Other common goals include:

  • Finding items that appear in one list but not the other.
  • Checking whether a key appears more than once.
  • Finding records that exist in both sheets but have a changed value.
  • Checking whether two sheets have the same data in the same row and column positions.

Choose a Reliable Key

A key is the column, or combination of columns, that identifies a record. An invoice number, product code, or employee ID is usually more reliable than a person’s name, because names can be duplicated, misspelled, or changed. If no single column identifies a row uniquely, you may need to compare a combination, such as first name plus last name and date of birth, or product code plus location.

Check that the key is consistent on both sheets before comparing. A numeric ID stored as a number on one sheet and as text on another may behave unexpectedly. Leading zeros matter too: product code 00417 may not be equivalent to the number 417 if the zeros are meaningful. Keep key columns in a consistent format and avoid changing identifiers just to make them look alike.

Also ask whether duplicates are allowed. If each key should occur only once, duplicates can signal a data problem. If duplicates are legitimate, a simple presence check can tell you that a key exists but cannot tell you which repeated record corresponds to which other record. In that case, add another identifying field or compare the records as groups.

Find Items Missing from the Other Sheet

For a basic presence check, assume the current sheet has IDs in A2:A100 and the sheet named April List has IDs in A2:A100. Enter this formula in B2 on the current sheet, then fill it down:

=IF(COUNTIF('April List'!$A$2:$A$100,A2)>0,"Match","No match")

If A2 contains 1042 and that ID appears anywhere in April List A2:A100, the result is Match; otherwise, it is No match. The dollar signs keep the lookup range fixed as you fill the formula down, while A2 changes to A3, A4, and so on. Replace the sheet name and range with your own. If a worksheet name contains spaces, keep the single quotes around it.

To find items that are in April List but not in the current sheet, run the same check in the other direction: place a formula beside the IDs on April List and point COUNTIF at the current sheet’s ID range. Comparing in both directions matters. A one-way check tells you which entries from one list are absent from the other, not whether the second list also has extra entries.

COUNTIF is not case-sensitive. For instance, it treats the text keys AB12 and ab12 as a match. It also recognizes wildcard characters in criteria, so IDs that literally contain an asterisk or question mark need extra care. If capitalization or unusual characters are meaningful, choose a stricter comparison method and verify a few results manually.

Compare Details After Matching the Key

Finding a matching ID is only the first step if you also need to check a related field. For example, assume the current sheet has IDs in A2:A100 and department names in B2:B100. April List has IDs in A2:A100 and corresponding department names in B2:B100. Put this formula in C2 on the current sheet and fill it down:

=IF(COUNTIF('April List'!$A$2:$A$100,A2)=0,"Missing",IF(B2=XLOOKUP(A2,'April List'!$A$2:$A$100,'April List'!$B$2:$B$100),"Same","Changed"))

For an ID that is absent from April List, the result is Missing. If the ID is present and its department is the same, the result is Same; if the department differs, the result is Changed. XLOOKUP retrieves the department associated with the first matching ID. This formula assumes the key is unique; if the same ID occurs multiple times, XLOOKUP returns the first matching record, which may not be the intended comparison. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.

For a quick check of a single value, an equality formula can be enough. Assume the two sheets have already been sorted into the same order, and the values to compare are in B2 on the current sheet and B2 on April List. In C2, enter:

=IF(B2='April List'!B2,"Same","Different")

This returns Same when the two cells have equal values and Different otherwise. It is not a safe way to compare independently sorted lists: it compares positions, not identities. Use it only when the rows are known to correspond.

When Row-by-Row Comparison Is Appropriate

Comparing cells in the same positions is useful when the sheets are two versions of the same report and their rows have been kept in the same order. It can quickly reveal edits in a fixed template, such as whether a value in a particular monthly forecast cell changed. For tables that may have been sorted, filtered, or had records inserted, match by a key first.

A row can also contain several fields that need checking. You can compare each field in a separate helper column, which makes it clear whether the name, address, or status changed. For a broad “does anything in this row differ?” check, first confirm that the columns line up and that blank cells should be treated consistently. A difference in one cell may be meaningful, or it may simply reflect a harmless formatting or data-entry variation that needs review.

Excel’s ordinary equality formulas compare cell values, not their visual formatting. Two cells can look different because of number formats, font colors, or conditional formatting while holding the same underlying value. Conversely, dates can display identically but be stored as different values. If formatting is part of the comparison, inspect formatting separately rather than relying only on formulas.

Handle Duplicates, Blanks, and Messy Text

A presence formula does not count matched records one-to-one. If the first sheet has one occurrence of ID 1042 and the second sheet has three, COUNTIF still reports that the ID is present. To investigate duplicates, select the key column and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values in the selected range, making it easier to check whether repeats are expected.

Blank keys deserve special treatment. A blank in one list should not usually be considered a valid match for another blank, since that can make incomplete records appear accounted for. Check for empty key cells before interpreting results, and decide whether a blank means “missing information” or is a legitimate value. A helper status that distinguishes blank keys from matched and unmatched keys can make a review safer.

Extra spaces are another frequent cause of apparent mismatches. “North” and “North ” can look identical but contain different text. You can clean a copied value with TRIM, but be aware that TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words to one; it does not remove every possible nonprinting character. Nonbreaking spaces copied from websites may need separate cleanup. Keep the original data intact until you have confirmed that cleaning does not remove meaningful differences.

Text matching with COUNTIF is case-insensitive, and number-versus-text inconsistencies can cause confusing results. Before concluding that a formula is wrong, inspect the underlying values, formats, and spaces in a few examples. A small set of manually checked records is a useful quality check, especially when the comparison will inform a business decision.

Use Power Query for Larger or Repeat Comparisons

When you regularly compare large tables, Power Query can provide a repeatable workflow instead of relying on copied formulas. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Workbook to import each workbook or table. In the Power Query Editor, choose Home > Merge Queries, select the matching key column in each table, and choose the join type that fits the question.

A Left Anti join returns rows from the first table that have no matching key in the second. A Right Anti join does the reverse. A Full Outer join keeps rows from both tables, including unmatched ones, and is useful for reviewing what is unique to either side. An Inner join keeps only rows with matching keys. After merging, expand the relevant columns and review the result, then use Close & Load to return it to Excel.

Power Query is especially useful when the source files are refreshed repeatedly, but it still depends on sound keys and consistent data. Duplicate keys can produce multiple joined rows, and inconsistent types or spaces can prevent records from matching. The available Power Query features differ across Excel platforms; these menu directions are for Windows desktop Excel. If the required tools are unavailable in your version, helper formulas are a practical alternative.

Review and Present the Results

Keep the source sheets unchanged while you compare them. Add a helper column for statuses such as Match, Missing, Same, or Changed, then filter that column to focus on exceptions. To highlight the text statuses, select the helper-column cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Choose a color that makes exceptions visible without obscuring the underlying values.

Before sharing a result, confirm that both ranges include all populated rows and that the formula references point to the intended sheets. Check several known matches and known nonmatches by hand. If a key should be unique, test for duplicates; if the comparison is row-based, confirm that the rows are aligned. These checks help catch errors caused by a truncated range, an incorrect sheet name, or a formula filled down only partway.

Frequently Asked Questions

Can Excel compare two sheets automatically?
Yes. Formulas such as COUNTIF and XLOOKUP can flag missing keys and changed details. Power Query can also merge tables for repeat comparisons. Excel needs a clear rule for what counts as a match; it cannot infer whether a name, ID, or full row is the right basis.

Can I compare two Excel sheets that are in different workbooks?
Yes. Formulas can refer to ranges in another open workbook, although external references can be harder to maintain if files are moved or renamed. For a recurring comparison, importing both workbooks into Power Query can make the steps easier to refresh.

How do I find rows that are in one sheet but not the other?
Use a presence check against the other sheet’s key column and filter for No match. Repeat the check in reverse to find extra rows in the second sheet. A Full Outer merge in Power Query is another way to review unmatched records from both sides.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive, so values such as AB12 and ab12 are treated as matching text. If capitalization matters, use a case-sensitive approach and verify the results against representative records.

Why does Excel say two values do not match when they look the same?
Common causes include leading or trailing spaces, nonprinting characters, a number stored as text, different underlying date values, or comparing the wrong rows. Inspect the raw values and data types, clean only what is safe to normalize, and check a few records manually.

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.