To compare values in two Excel sheets, enter a formula beside the first list and check whether each value appears in the other sheet. With values in column A on both sheets, put this in Sheet1!B2 and fill it down: =IF(COUNTIF(Sheet2!$A$2:$A$1000,A2)>0,"Duplicate","Not found"). It marks each Sheet1 value that also appears in Sheet2; you can reverse the sheet references to check the other direction.
This approach works in Microsoft Excel for Microsoft 365 on Windows and in current desktop Excel versions that support COUNTIF. It compares values, not entire worksheets cell by cell. The right method depends on whether you want to flag shared names, find missing records, compare complete rows, or identify repeats within either sheet.
What “duplicates between two sheets” means
People use “duplicates” to describe two related but different checks. The first is an overlap check: does a value on one sheet also occur on the other? The formula above answers that question. The second is a within-list duplicate check: does a value occur more than once on the same sheet? A match between sheets does not, by itself, mean a value is repeated within either list.
For example, imagine Sheet1 contains customer IDs 104, 105, and 106, while Sheet2 contains 105 and 107. The overlap is 105. The formula marks 105 as a duplicate and the other Sheet1 IDs as not found. It does not say whether 105 appears twice on Sheet1 or Sheet2; that requires a separate count within each list.
Decide what counts as a duplicate before comparing. A customer ID may be enough, while a product record may need both a product code and a size. Names alone can also be ambiguous: two different people may share a name, and the same person’s name may be entered with different punctuation.
Use COUNTIF to flag matches
For a straightforward comparison, COUNTIF counts how many times the value from the current row appears in the comparison range. IF converts that count into a readable label. In this example, row 1 contains headers, the lookup values are in Sheet1!A2:A1000, and the comparison list is in Sheet2!A2:A1000.
With values in Sheet1!A2:A1000 and Sheet2!A2:A1000, enter this in Sheet1!B2, then fill down through B1000:
=IF(COUNTIF(Sheet2!$A$2:$A$1000,A2)>0,"Duplicate","Not found")
The dollar signs keep the comparison range fixed as you copy the formula down; A2 changes to A3, A4, and so on. If a value appears several times on Sheet2, the label still says “Duplicate” because the formula only checks whether the count is greater than zero. To return the actual number of matches instead, use this formula in Sheet1!B2 with the same two lists:
=COUNTIF(Sheet2!$A$2:$A$1000,A2)
A result of 0 means no match, 1 means one occurrence on Sheet2, and 2 or more means the value is repeated there. This count still does not measure how often the value appears on Sheet1.
If your sheet name includes a space, enclose it in single quotation marks in a formula. For example, a reference to a sheet named “June List” looks like 'June List'!$A$2:$A$1000. Replace the sheet names and ranges with those in your workbook, and make the ranges long enough to include all current records.
Check the other direction, too
A check from Sheet1 to Sheet2 identifies Sheet1 values missing from, or shared with, Sheet2. It does not automatically list items that exist only on Sheet2. To find those, run the same check in reverse. This matters when you are reconciling two lists: checking just one direction can leave unmatched records unnoticed.
With values in Sheet2!A2:A1000 and the comparison list in Sheet1!A2:A1000, enter this in Sheet2!B2 and fill down:
=IF(COUNTIF(Sheet1!$A$2:$A$1000,A2)>0,"Duplicate","Not found")
Now filter the helper column for “Not found” on each sheet. The filtered rows show items exclusive to that sheet. If you only need a quick review, the formula results can also be sorted or filtered using Excel’s regular filter controls. Keep the original data intact while reviewing: a match label is an analysis result, not a reason to delete a row automatically.
Compare records using more than one column
When one field is not a unique identifier, compare multiple fields together. For example, a product code might appear in several sizes, so the product code and size must both match. COUNTIFS checks multiple conditions on the same row of the comparison range.
Assume Sheet1 has product codes in column A and sizes in column B, and Sheet2 stores the same fields in columns A and B. Put this in Sheet1!C2 and fill down:
=IF(COUNTIFS(Sheet2!$A$2:$A$1000,A2,Sheet2!$B$2:$B$1000,B2)>0,"Match","No match")
This returns “Match” only if a row on Sheet2 has both the same product code and the same size. The criteria ranges must cover the same number of rows. If the record is defined by three fields, add another range-and-criteria pair to COUNTIFS. Choose fields that together identify the record meaningfully; adding a field that changes frequently can cause valid records to appear unmatched.
Highlight matches and missing values
A helper column is often the most dependable way to apply color. Excel’s conditional-formatting formula rules generally cannot use a direct reference to a different worksheet. First create the “Duplicate” or “Not found” helper formula described above on Sheet1. Then select the cells to color—for example, A2:A1000—and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
With the helper labels in Sheet1!B2:B1000 and A2:A1000 selected, use this conditional-formatting formula to highlight matches:
=$B2="Duplicate"
Choose a format, such as a fill color, and confirm the rule. The dollar sign fixes the helper column while the row number adjusts for each selected cell. To highlight missing items instead, use =$B2="Not found" with the same selection and helper-column layout. Repeat the process on Sheet2 after creating the reverse-direction helper formula. On Mac, Excel has the same basic conditional-formatting feature; ribbon layout and wording can vary slightly by version.
Common causes of incorrect-looking results
A formula can be syntactically correct yet produce results that seem surprising. Check the data and the comparison rules before assuming Excel has missed a record:
- Extra spaces: “North” and “North ” may not compare as you expect. Clean inconsistent text, or use a separate helper column with TRIM before matching. TRIM removes ordinary leading and trailing spaces, though it does not remove every kind of unusual whitespace.
- Different data types: An ID stored as text on one sheet and as a number on another can create mismatches or inconsistent results. Check the cells’ values and formats, and standardize the underlying data type rather than relying only on how the cells look.
- COUNTIF wildcards: In a criteria string, an asterisk (*) can stand for any number of characters and a question mark (?) can stand for one character. If your data contains those characters literally, COUNTIF may interpret them as wildcards. For an exact match that is also case-sensitive, use the alternative below.
- Uppercase and lowercase: COUNTIF is not case-sensitive, so “ab12” and “AB12” count as a match. That is usually suitable for names and codes, but not for identifiers where letter case matters.
- Blanks: Empty cells can appear to match empty cells. If blanks should not be labeled as duplicates, add a check that ignores a blank lookup cell.
- Range limits: A formula using rows 2 through 1000 will not include data below row 1000. Extend the range when needed or use Excel Tables so formulas and references can adjust as the data grows.
For case-sensitive comparisons, assume the value to check is in Sheet1!A2 and the comparison range is Sheet2!A2:A1000. Put this formula in Sheet1!B2 and fill down:
=IF(SUMPRODUCT(--EXACT(A2,Sheet2!$A$2:$A$1000))>0,"Duplicate","Not found")
EXACT distinguishes uppercase from lowercase, while SUMPRODUCT tests whether any comparison returns TRUE. This is a more specialized check; use it only when case is part of what makes an identifier unique. If you need to ignore blank lookup cells, use this formula with the same layout:
=IF(A2="","",IF(COUNTIF(Sheet2!$A$2:$A$1000,A2)>0,"Duplicate","Not found"))
Choose a method for the size of the task
For two modest lists with one key column, COUNTIF is usually the easiest method to set up, audit, and explain to another workbook user. Use COUNTIFS when a record depends on several fields. Use conditional formatting when visual scanning matters, and retain a helper column when you want to filter or count the results.
For recurring comparisons or large datasets, Power Query can make the process easier to repeat. In Excel for Microsoft 365 on Windows, load each source as a query using the Data tab’s Get & Transform Data options, then use Home > Merge Queries in the Power Query Editor to join them on the relevant key fields. Choose the join type based on whether you want only matching rows or also unmatched records. Power Query’s controls and availability can differ by Excel version and platform; formula-based comparison is often more straightforward for a one-time check.
Before deleting anything, verify that a match really means the records are duplicates. Two orders can have the same customer ID but represent separate purchases, for instance. Consider keeping a copy of the source data, checking both directions, and reviewing the fields that define a unique record. A comparison formula finds matching values; it cannot determine the business meaning of those matches for you.
Frequently Asked Questions
How do I compare two Excel sheets and show only duplicates?
Use a COUNTIF formula beside one list to label values that also appear in the other sheet, then filter the helper column for “Duplicate.” Check in both directions if you need every shared or unmatched item accounted for.
How do I find values in one sheet that are missing from another?
Use the same comparison formula and filter for “Not found.” To find items missing from the first sheet, put the reverse-direction check beside the second list.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF treats letter case as equivalent. Use a formula based on EXACT and SUMPRODUCT when capitalization is part of the identifier.
Can I compare two columns without adding a helper column?
Yes. You can create a conditional-formatting rule, but a direct cross-sheet reference may not be accepted in the rule. A helper formula is a practical workaround; another option is to define a named range for the comparison list and use that name in the rule.
Can I compare complete rows instead of one column?
Yes. Identify the fields that define the record, then use COUNTIFS to require each field to match on the same row of the other sheet. If the records are complex or the task repeats regularly, consider merging the data with Power Query.
Related
- Excel Conditional Formatting for Values Less Than a Cell
- Conditional Formatting in Excel for the Web: A Guide
- Conditional Formatting in Excel: Rules and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Bing Chat Excel Formula Not Calculating? Causes & FixesIf a formula from Bing Chat is not calculating, first paste it into an Excel cell and confirm…
- Excel Wildcard SUMIF Formula Examples That Match TextTo total values for text that contains a word, use SUMIF with asterisks around the word: with product…
- Excel A to Z Formula: How to Sort DataWith the values to sort in A2:A20 and an empty output area beginning at C2, enter =SORT(A2:A20) in…
- Excel Formula to Highlight a Row If a Cell ContainsWith the search term in E2, the value to check in column A, and rows 2–100 to format…
- Salesforce Row-Level Formula Examples for ReportsSalesforce row-level formulas calculate a value separately for each record in a report; useful examples include discount percentage,…
- Extract Middle Text in Excel: Formulas and ExamplesTo extract text from the middle of a cell in Excel, use MID: with the source text in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.