Compare Two Excel Sheets and Find Duplicate Rows Easily

To compare two Excel sheets and find duplicates, put the records in the same workbook and use a helper formula such as =IF(COUNTIF(Sheet2!$A:$A,A2)>0,"Match","Not found") when column A contains a unique ID. Fill it down beside the first sheet: “Match” means that ID appears on the second sheet. If a duplicate must match across several columns, use COUNTIFS with each field as a condition instead.

Decide What Counts as a Duplicate

Before comparing data, define what “duplicate” means for your task. It might mean that the same customer ID appears on both sheets, that an entire row is identical, or that two rows share a combination such as email address and date. Excel cannot infer which fields identify the same real-world record; that is a data decision.

Same key: A matching account number or product code is enough, even if other fields differ.
Same combination: Two or more fields, such as first name and last name, must all match.
Same complete row: Every selected field must match. Differences in one field make the records different under this definition.

It is also important to distinguish a record found on both sheets from a repeated record within one sheet. A cross-sheet check answers whether a value exists in the other list. A within-sheet duplicate check asks whether that value occurs more than once in the same list. A value can satisfy either condition, both, or neither.

Prepare the Sheets Before Comparing

For the clearest results, place both datasets in one workbook, use one header row per sheet, and keep the same kind of information in the same columns. For example, put customer IDs in column A on both sheets. Convert the data to consistent types where possible: an ID stored as text may not behave like an ID stored as a number, and a date stored as text may not compare like a true Excel date.

Check for blank rows, extra spaces, inconsistent punctuation, and different spellings. The entries “Northwind” and “Northwind ” look alike on screen, but the second has a trailing space. A formula may treat them as different. Conversely, COUNTIF comparisons are not case-sensitive, so “abc” and “ABC” are generally treated as a match. If capitalization is meaningful, a basic COUNTIF check is not sufficient.

Keep a backup or work on a copy before deleting or changing records. A comparison formula is non-destructive, but sorting, removing duplicates, or editing source data can make it harder to reconstruct the original order.

Find Matching IDs with COUNTIF

Suppose Sheet1 has customer IDs in A2:A100 and Sheet2 has IDs in A2:A100. In Sheet1, enter a helper heading such as “Status” in B1. With the ID to check in Sheet1!A2 and the comparison list in Sheet2 column A, enter this in Sheet1!B2:

=IF(COUNTIF(Sheet2!$A:$A,A2)>0,"Match","Not found")

For example, if A2 contains 1042 and 1042 occurs anywhere in Sheet2 column A, COUNTIF returns a positive count and the formula displays “Match.” If it does not occur, the result is “Not found.” The dollar signs keep the comparison range fixed when you copy the formula down. The reference A2 changes to A3, A4, and so on.

Copy the formula down through the rows you need, using the fill handle or copying and pasting. Then filter the Status column to show only “Match” or only “Not found.” A match tells you that at least one record with that ID exists in the other sheet; it does not tell you how many times it appears there.

To find records in the second sheet that are missing from the first, repeat the check in Sheet2, but point the formula at Sheet1. Checking in both directions matters: a one-way comparison can find records in the first list that are absent from the second, but it cannot identify additions found only in the second list.

Match Records Across Multiple Columns

If an ID is not available or is not unique, compare a combination of fields. For example, assume columns A, B, and C contain ID, surname, and date of birth on both sheets. With the row to test in Sheet1!A2:C2 and the corresponding fields in Sheet2 columns A:C, enter this in Sheet1!D2:

=IF(COUNTIFS(Sheet2!$A:$A,A2,Sheet2!$B:$B,B2,Sheet2!$C:$C,C2)>0,"Duplicate","Unique")

COUNTIFS counts rows where all three conditions are true on the same row. If Sheet1 row 2 contains 1042, Chen, and a date that also appears together in Sheet2, the result is “Duplicate.” If the ID appears there but the surname or date differs, the result is “Unique.” Make sure each range covers the corresponding field and has the same dimensions; mismatched range sizes can cause errors.

Choose the columns carefully. Comparing only a person’s name can create false matches when two people share a name. Adding a date of birth, email, or another reliable field can reduce those collisions. On the other hand, including a field that changes frequently, such as a mailing address, can hide records that are the same customer but have updated details.

Find Repeats Within a Sheet

To check whether an ID repeats inside one list, rather than whether it appears on another sheet, count occurrences in the current sheet. With IDs in A2:A100, enter this in B2:

=IF(COUNTIF($A$2:$A$100,A2)>1,"Repeated here","First occurrence")

If 1042 appears three times in the specified range, each of those rows will be labeled “Repeated here.” The formula flags every occurrence, including the first. If you want to label only later occurrences, use this formula in B2 with the same layout:

=IF(COUNTIF($A$2:A2,A2)>1,"Repeat","First occurrence")

As you copy it down, the expanding range checks from the first data row through the current row. The first appearance is labeled “First occurrence”; later appearances are labeled “Repeat.” Be cautious with empty cells: a blank can be counted as a value, so filter or exclude blank IDs if they should not be treated as duplicates.

Highlight Matches Without Deleting Data

A helper column is usually the most transparent way to compare separate sheets. After the formula labels the rows, you can apply a filter or sort by the result. To highlight the results, select the helper cells and use Home > Conditional Formatting > Highlight Cells Rules, then choose a suitable rule such as Text that Contains and enter “Match.” This colors the status cells while leaving the original records intact.

For duplicates within a single column, select the data cells and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. That built-in rule is useful for a quick visual check on one range. It is not a substitute for a multi-column comparison, and it does not by itself establish that a row on one worksheet matches a row on another. For cross-sheet matching, use the helper formula or another comparison method.

Conditional formatting highlights values rather than resolving ambiguous records. Review the underlying rows before removing anything: two matching IDs may represent a genuine repeat, or they may indicate different events linked to the same account.

Compare Larger Files with Power Query

For large lists or a comparison that must be repeated, Power Query can merge two tables and return matching or unmatched records. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Excel Workbook to import each workbook or table. In the Power Query Editor, choose Home > Merge Queries, select the two tables, and click the corresponding key column in each preview. Select more than one key column when the match depends on a combination; select them in the same order in both tables.

Choose the join kind based on the question. An inner join returns rows with matching keys in both tables. A left anti join returns rows from the first table that have no match in the second. To find records missing in the opposite direction, switch which table is first and use a left anti join again. After reviewing the result, choose Home > Close & Load to return it to Excel.

Power Query is especially useful when the data is too large for comfortable manual review, when source files are refreshed regularly, or when the comparison requires repeatable cleanup steps. It does not make the definition of a match for you: select the appropriate key columns, confirm that the data types align, and review the result before acting on it. The exact available commands can vary by Excel edition and platform; the steps above describe Microsoft 365 for Windows.

Common Mismatches and Troubleshooting

Spaces and hidden characters: Extra spaces or imported control characters can prevent a match. A helper column using TRIM and CLEAN can remove many ordinary excess spaces and nonprinting characters, but unusual nonbreaking spaces may need separate cleanup.
Text versus numbers: The text value “00123” and the number 123 may not compare as intended. Decide whether leading zeros are meaningful, then standardize both lists consistently.
Wrong match key: A non-unique name or category can return matches that are technically correct for that field but misleading for the record. Use a unique identifier or a deliberate combination of columns.
Different date values: A displayed date can hide a time component. Two cells may show the same day while containing different times. Compare dates at the required precision or standardize the source values first.
Formula copied incorrectly: Confirm that the lookup range stays fixed and the row being tested changes as you fill down. Also check that sheet names and column references point to the intended data.

When a formula reports a match that you cannot see, inspect the underlying cell values and their number formats rather than relying only on how they look. When it reports no match, test a few known records and check spaces, data types, dates, and the selected columns. A small manual check is a useful safeguard before using the result for a large update.

Choose the Right Comparison Method

Use COUNTIF when one column contains the key and you want a simple yes-or-no answer. Use COUNTIFS when the match depends on several fields. Use the built-in Duplicate Values rule for a quick visual scan of duplicates within one selected range. Choose Power Query when you need a refreshable merge, an anti-join, or a repeatable process for substantial datasets.

Formulas are easy to audit and update when a workbook is modest in size. Whole-column references, such as A:A, are convenient, but in a very large or formula-heavy workbook they can involve more cells than necessary; bounded ranges or Excel tables can make the intended dataset clearer. Power Query keeps the source data and transformation steps separate, but requires setup and a refresh when inputs change. Neither approach guarantees that a match is a true real-world duplicate: that depends on the quality of the data and the chosen key.

Frequently Asked Questions

Can I compare two Excel files instead of two sheets?
Yes. You can copy the data into two sheets in one workbook, or import both files with Power Query and merge them. Keeping the data in one workbook makes a basic COUNTIF formula straightforward; a Power Query merge is often easier to refresh across files.

How do I find rows in one sheet that are missing from the other?
Use a helper column with COUNTIF or COUNTIFS and label a row “Not found” when the count is zero. Run the check in both directions if you need to identify missing records from each list.

Does COUNTIF find duplicates across an entire row?
No. COUNTIF checks one criterion range for one value. To compare several fields on the same row, use COUNTIFS with a criterion for each relevant column, or use Power Query with those columns selected as the merge keys.

Will Excel treat uppercase and lowercase text as different?
COUNTIF is not case-sensitive, so capitalization alone generally does not distinguish values. If case must matter, use a case-sensitive comparison approach rather than relying on COUNTIF.

Can I remove the duplicates after I find them?
You can, but first confirm which rows should be retained and save a copy. Excel’s Remove Duplicates command removes records based on selected columns; it is not the same as merely highlighting matches, and deletion can discard meaningful differences in other fields.

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.