How to Find Duplicate Entries in Excel

How-to basics · Updated November 20, 2026

To find duplicate entries in Excel, select the cells you want to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel marks repeated values so you can review them without changing your data. If you need a formula-based check, use COUNTIF in a helper column. Before removing anything, decide what counts as a duplicate and make a copy of your workbook.

What You’ll Need

  • An Excel workbook opened in Microsoft Excel for Microsoft 365 on Windows.
  • A clear idea of which field or combination of fields must match. For example, duplicate email addresses are different from duplicate full records.
  • A backup copy if you plan to delete duplicate records. Conditional formatting only marks cells; Remove Duplicates changes the selected data.

Step-by-Step

  1. Choose the data you want to compare. Open the worksheet and identify the column or range to check. If you are checking email addresses, select the email cells rather than the entire worksheet. If a row represents a person or transaction, decide whether a duplicate means the same value in one column or a repeated combination of fields. For example, two people can share a surname without being duplicate records.

    Include the complete data range if you will later remove duplicate records, including related columns such as name, address, and account number. Keep the header row in the selection only if you intend to use the option indicating that the data has headers.

  2. Highlight duplicates using Excel’s built-in rule. Select the cells to check, excluding the heading if possible. On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog box, leave Duplicate selected, choose a formatting style, and click OK. Excel highlights every matching value in the selected range, including the first occurrence and later repeats.

    This is the quickest visual check and does not alter or delete cell contents. In Excel for Microsoft 365 on Mac, select the range and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The labels can vary slightly between releases, but the Duplicate Values rule is the one to choose.

  3. Check the highlighted results before taking action. Scan the marked cells and their neighboring information. A duplicate value may be legitimate: for example, several orders can belong to the same customer. If you see a repeated value that should be unique, compare the full records to confirm whether they refer to the same item. Consider sorting the data by the checked column to bring matching values together; sorting rearranges the rows, so make sure you sort the entire table rather than one column by itself.

  4. Use a formula when you want a reusable flag. Suppose the values to check are in A2:A7, with a header in A1. Enter the following formula in B2, then fill it down through B7. Each row returns TRUE when its value appears more than once in the checked range, and FALSE otherwise:

    =AND(A2<>"",COUNTIF($A$2:$A$7,A2)>1)

    For example, if A2:A7 contains Ana, Bo, Ana, Cora, Bo, and Dia, the formula marks both Ana cells and both Bo cells TRUE. It skips empty cells. The dollar signs keep the checked range fixed as you copy the formula, while A2 changes to match each row. You can filter the helper column to show TRUE rows, or use it to guide a manual review.

  5. Apply the formula as conditional formatting instead of adding a helper column. Select A2:A7, then choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format, enter the same formula shown above, choose a format, and confirm. Because the rule is applied to A2:A7, its relative cell reference checks each selected row. This option is useful when you want to see duplicates in place but do not want an extra column in the worksheet.

  6. Use Remove Duplicates only after confirming the records. Save a separate copy first. Select the complete table, including all columns that belong together, then choose Data > Remove Duplicates. In the dialog, check My data has headers if the top row contains field names. Select the columns Excel should use to decide whether a row is repeated, then choose OK. Excel reports how many duplicate values it removed and how many unique values remain.

    When you select more than one column, Excel treats rows as duplicates only when the values match across the selected columns. If you select just the email column, rows with the same email can be removed even if other fields differ. Use that setting only when it matches your intent. Remove Duplicates deletes rows from the selected range; it is not just a highlighting option.

  7. Check the result and keep a record of the decision. Review Excel’s removal message and scan the remaining records, especially the first and last rows of the affected range. If duplicates were removed unexpectedly, use Undo immediately if it is available or reopen your backup. For recurring work, keep the original data on a separate worksheet and record which columns define a duplicate so the same rule can be applied consistently next time.

Common Mistakes

Selecting the wrong range: A rule only checks the cells you select. If you select a partial list, a value repeated elsewhere on the sheet may not be identified. Conversely, selecting multiple unrelated columns can make Excel compare values in places you did not intend. Check the selection border before applying a rule.

Confusing repeated values with repeated records: A value such as a city name may appear on many valid customer records. For duplicate rows, compare the fields that identify a record, not just any repeated field. With Remove Duplicates, selecting the wrong columns can remove valid rows.

Deleting before reviewing: Conditional formatting is a safe way to inspect matches. Remove Duplicates permanently changes the current range, and it keeps one occurrence while removing the others. Save a copy first and confirm which occurrence should remain.

Assuming text is identical because it looks identical: A leading or trailing space, a nonprinting character, or a different data type can make entries behave differently. COUNTIF is also not case-sensitive, so Ana and ANA are treated as a match by that function.

Tips

Turn a plain data range into an Excel table with Insert > Table if you will keep adding records. Tables make it easier to work with headers and filter rows, though you should still confirm the range and columns used by a duplicate rule. When you add new data, check that your conditional formatting rule or formula covers the new rows.

For a simple one-column list, the built-in Duplicate Values rule is usually the clearest choice. Use a helper formula when you need a TRUE/FALSE flag to filter, count, or review. Keep the helper column beside the source data so it is clear which row each result belongs to, and label it something descriptive such as Duplicate check.

Clean the data only when you understand the effect. Removing extra spaces or standardizing capitalization can reveal entries that should be considered equivalent, but it can also merge records that are intentionally different. Preserve the original column if you use a cleaned version for comparison.

On Mac, the conditional-formatting and Remove Duplicates commands are available from the Home and Data tabs as described above. Excel for the web also includes duplicate-value formatting and data removal options, but dialog layouts can differ from the Windows desktop version. If a command is not visible, check the relevant ribbon tab or open the workbook in the desktop app.

Troubleshooting

No duplicates are highlighted: Confirm that the correct cells are selected and that the values are genuinely the same. Check for extra spaces, different punctuation, or text-versus-number differences. If you used a formula, verify that the range in the COUNTIF part includes all records and that the formula is applied to the intended cells.

Too many cells are highlighted: Check whether the selection includes blank cells, headings, or unrelated fields. The formula above deliberately excludes blank cells. The built-in rule can be easier to interpret when you select only the populated data cells, not the entire worksheet. Also remember that duplicate-value formatting treats matches without regard to letter case.

The formula shows an error or the same result on every row: Enter the formula in the first data row, not the heading, and fill it down. Check that the formula begins with an equals sign and uses the commas and parentheses shown. If you use a different worksheet layout, update the cell references and the fixed range. A formula copied outside the intended range may still refer to the original data, which can produce misleading flags.

Remove Duplicates removes records you wanted to keep: Undo at once if possible, or restore the saved copy. Repeat the command with the complete table selected and review the checked columns in the dialog. If only one column is checked, Excel may remove rows that share that value even when other columns differ.

The highlight does not update as expected: Check the conditional formatting rule’s Applies to range through Home > Conditional Formatting > Manage Rules. Extend that range if new rows were added, or recreate the rule for the correct range. If a rule is applied to an Excel table, verify that the new rows are actually part of the table.

Frequently Asked Questions

Does Excel highlight the first occurrence or only later duplicates?
The built-in Duplicate Values rule highlights all occurrences of a repeated value, including the first one. The helper formula above also flags every occurrence in its checked range.

How can I flag only the second and later occurrences?
With values in A2:A7, enter this formula in B2 and fill it down to B7: =COUNTIF($A$2:A2,A2)>1. It returns FALSE for the first occurrence and TRUE for each later repetition. For example, the second Ana in the sample list is TRUE.

Does Excel treat uppercase and lowercase as different?
No. The Duplicate Values rule and COUNTIF do not distinguish letter case. If case matters, use a case-sensitive comparison method rather than relying on these options.

Can I find duplicate rows across several columns?
Yes. Select the relevant table columns when using Data > Remove Duplicates, then check the columns that define a repeated record. Excel considers rows duplicates when their values match across those selected columns. Make a copy before removing anything.

Does highlighting duplicates delete them?
No. Conditional formatting only changes how matching cells appear. To delete repeated records, use Data > Remove Duplicates after confirming the range and comparison columns.

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.