Search Excel for Duplicate Entries: Methods That Work

How-to basics · Updated November 18, 2026

To search Excel for duplicate entries, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel will mark repeated values in the selected range. To identify duplicates with a formula or remove them from a list, use the methods below—and make a copy before deleting anything.

What Excel means by duplicate entries

A duplicate is a value or record that appears more than once, but the right way to search depends on what counts as a match. In a contact list, you may want to find repeated email addresses while retaining different people who share a name. In an order table, you may need to identify repeated order numbers, or rows that match across several columns. Decide which fields define a duplicate before selecting a method.

Excel’s built-in Duplicate Values rule compares cell values within the range you select. It does not decide whether two entire records are duplicates based on a set of fields. For example, two rows with the same customer name but different order dates contain a repeated name, not necessarily a duplicate order. Conversely, two records might be duplicates even if one column, such as a note, differs.

It also helps to distinguish finding duplicates from removing them. Highlighting and formulas let you inspect results before making changes. Remove Duplicates deletes records from the selected data, which is useful for cleanup but can discard information if the matching criteria are not chosen carefully.

Highlight repeated values with Conditional Formatting

For a quick visual check in Excel for Microsoft 365 on Windows, select the cells or column to inspect. On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a formatting style, and click OK. Excel formats values that occur more than once in the selected range.

To check email addresses in cells C2:C500, select C2:C500 before applying the rule. Selecting a whole table instead can produce a different result: Excel checks values across the entire selected range, so a word appearing once in a city column and once in a notes column may be marked as repeated. Select one field at a time when duplicates are defined by that field alone.

  • To check one column: select the data cells in that column, excluding the heading.
  • To check several separate columns independently: apply the rule to each column separately.
  • To check complete records: use Remove Duplicates with the relevant columns selected, or build a helper formula that compares those fields.

The rule changes formatting, not the underlying values, so it is safe for an initial review. To remove the highlighting later, select the cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. If the list grows, consider converting it to an Excel table first with Insert > Table; table formatting and calculated columns can then extend as you add records, though review conditional formatting after changes to the table or its range.

Use COUNTIF to flag duplicates in a helper column

A formula is useful when you want a clear Yes/No flag, need to filter the results, or want a rule that ignores empty cells. Suppose email addresses are in A2:A100 and you will put a check in B2. With that layout, enter =COUNTIF($A$2:$A$100,A2)>1 in B2 and fill it down. It returns TRUE for every address appearing more than once and FALSE for an address appearing once. For example, if sam@example.com is in A2 and A9, both corresponding checks return TRUE.

COUNTIF counts how many cells in the chosen range meet a criterion. The dollar signs keep the full lookup range fixed as the formula is filled down, while A2 changes to A3, A4, and so on. The comparison is not case-sensitive, so Sam@example.com and sam@example.com are treated as the same value. Leading or trailing spaces can still make two entries appear different to Excel, so inspect or clean suspect text.

If blank cells should not be marked, use a condition that first checks whether the current cell contains anything. With values in A2:A100 and the formula in B2, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) and fill down. The result is TRUE only when A2 is nonblank and occurs more than once in the specified range. Filter the helper column for TRUE to review just the repeated entries.

Sometimes you want to flag only the second and later occurrence, leaving the first one unmarked. With the list in A2:A100 and the helper formula beginning in B2, use =COUNTIF($A$2:A2,A2)>1 and fill down. The expanding range counts from the first data row through the current row. In a sequence such as blue, red, blue, blue, the formula returns FALSE, FALSE, TRUE, TRUE. This helps identify which later rows could be removed while preserving the first occurrence.

Find duplicate rows across multiple columns

When a duplicate means that several fields match together, compare those fields as a record. For example, a repeated transaction might be defined by matching both the date and invoice number, even when amounts differ elsewhere in the table. Conditional Formatting’s standard Duplicate Values rule does not combine columns into a multi-field key automatically.

One practical approach is to add a helper column that counts matching combinations. Suppose dates are in A2:A100, invoice numbers in B2:B100, and the check goes in C2. Enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1 in C2 and fill down. TRUE means the same date-and-invoice combination occurs more than once. COUNTIFS checks each pair of criteria on the same row, avoiding the mistake of treating a date match alone or an invoice-number match alone as sufficient.

Before relying on a multi-column check, specify the identifying fields. If invoice numbers are unique across all dates, checking only invoice number may be enough; if they restart each year, year may also belong in the combination. Avoid concatenating fields without separators, because different pairs can produce the same joined text—for instance, AB with C and A with BC. COUNTIFS avoids that particular ambiguity and lets you choose the matching columns explicitly.

Remove duplicates without losing the wrong data

When you have reviewed the matches and want to delete repeated records, make a copy of the worksheet or workbook first. Select a cell in the data, then choose Data > Remove Duplicates. In the dialog, confirm whether the first row contains headers and select the columns Excel should use to decide which records are duplicates. Click OK to remove later matching rows and keep the first occurrence.

The column checkboxes determine the definition of a duplicate. If you select only Email, rows with the same email address count as duplicates even if their names or other details differ. If you select Name and Email, both fields must match for rows to be treated as duplicates. Selecting every column removes only rows whose values match across all selected columns. This is a common source of confusion: the command does not automatically know which field is a unique identifier.

Excel reports how many duplicate values were removed and how many unique values remain. Check the result, especially if row order or associated information matters. The first occurrence is retained, so sort the data beforehand if you need a particular record—such as the latest update—to remain. The command changes the selected data, and undo may be available immediately with Ctrl+Z on Windows or Command+Z on Mac, but a backup is safer than depending on undo, particularly after additional edits.

Search, filter, and review results

Ctrl+F on Windows (Command+F on Mac) opens Find, but it searches for text or values you specify; it does not automatically identify all repeated entries. It can help investigate a suspected value after highlighting or filtering has narrowed the list. The Find All option can show multiple matches for a search term, but that is different from finding every value that occurs more than once.

For a formula-based check, turn on filters with Data > Filter, then use the dropdown in the helper column to show TRUE rows only. You can also sort the source column A to Z or Z to A to bring identical entries together. Sorting makes visual review easier, but it does not remove duplicates or prove that rows are duplicates by a multi-column definition. Keep related columns selected when sorting so the contents of each record stay together.

For an extract of distinct values rather than a marked list, Excel for Microsoft 365 includes the UNIQUE function. With source values in A2:A100 and an empty destination cell D2, enter =UNIQUE(A2:A100). It spills a list of distinct values into cells below D2; it does not delete or alter the original list. UNIQUE is available in Microsoft 365 and Excel 2021 or later. To return only values that occur exactly once, the function can be used with its once-only argument: with the same source range and destination, enter =UNIQUE(A2:A100,,TRUE). This returns values appearing exactly once, not values that have duplicates.

Common causes of misleading results

Two entries that look identical on screen may not be identical to Excel. Text may include extra spaces, nonprinting characters, or different punctuation. Numbers stored as text can behave differently from numeric values in some operations. A date can also have an underlying time component that is not visible in the cell’s display format. If a result seems wrong, inspect the raw value and formatting rather than assuming the duplicate rule is faulty.

  • Extra spaces: compare the original values and consider cleaning them in a separate helper column before deleting records.
  • Different formats: a value displayed as 1,000 may be stored differently from text that looks like 1,000.
  • Dates with times: two cells may display the same date while their underlying time values differ.
  • Case differences: COUNTIF and the built-in duplicate rule do not distinguish uppercase from lowercase text.
  • Repeated blanks: decide whether empty cells should count as missing data or as duplicates; a blank-check condition can exclude them from a formula result.

For cleanup, preserve the original column and create a cleaned helper version rather than overwriting data immediately. Excel’s TRIM function can remove ordinary extra spaces at the beginning and end of text and reduce repeated spaces between words, but it does not remove every possible invisible character. Check a sample of cleaned output and verify that the transformation is appropriate for the data before using it to decide what to delete.

Choose the right method for the job

Use Conditional Formatting when you need a fast visual scan and want to leave the source list untouched. Use COUNTIF or COUNTIFS when you need an auditable flag, want to filter or sort matches, or must define duplicates across selected fields. Use Remove Duplicates only when you have decided what constitutes a repeated record and are ready to change the data. Use UNIQUE when the goal is a separate list of distinct values rather than an in-place cleanup.

For recurring imports or larger repeatable cleanup tasks, Power Query can provide a refreshable workflow. In Excel for Microsoft 365 on Windows, select the data and choose Data > From Table/Range to open the Power Query Editor. Select the columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Close and load the result when ready. Power Query keeps the transformation steps so they can be applied again when the source is refreshed, but confirm the selected columns and resulting output before relying on it.

Frequently Asked Questions

How do I find duplicates in Excel without deleting them?
Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, or add a COUNTIF helper formula. Both let you review repeated values without removing source records.

How do I highlight duplicates in one column only?
Select the data cells in that column, excluding the header, and apply the Duplicate Values conditional formatting rule. Selecting a larger range can compare values across fields you did not intend to compare.

Can Excel find duplicate rows based on two columns?
Yes. Use COUNTIFS with both columns as criteria, or use Data > Remove Duplicates and select those two columns. The rows count as duplicates only when the chosen fields match on both.

Does Remove Duplicates keep the first entry?
Yes. Excel keeps the first occurrence in the current row order and removes later records matching the selected columns. Sort first if you need a particular occurrence to remain, and keep a backup.

Why are apparent duplicates not being detected?
Check for extra spaces, hidden characters, text-versus-number differences, and dates that include different times. Also confirm that the selected range or columns match your intended definition of a duplicate.

Does Excel’s duplicate check distinguish uppercase and lowercase?
No. The built-in duplicate rule and COUNTIF comparisons are not case-sensitive. Values such as ABC and abc are treated as matching for these checks.

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.