The fastest way to spot duplicates in Excel

Excel has a built-in tool that highlights duplicate values in seconds without requiring formulas or manual checking. Select the cells you want to check, go to the Home tab, click Conditional Formatting, then choose Highlight Cell Rules and Duplicate Values. Excel will when ready color any repeated entries, making them visible at a glance.

This method works best when you have a single column or a small range to check — say, a list of customer IDs or product codes. If you need to find duplicates across multiple columns or remove them entirely rather than just highlight them, you will need a different approach.

Key Takeaways

  • Conditional Formatting highlights duplicates when ready but does not remove them — it only colors the cells so you can see which values repeat.
  • The Remove Duplicates feature deletes duplicate rows permanently, keeping only the first occurrence of each value, and works on multiple columns at once.
  • A COUNTIF formula lets you mark duplicates yourself and gives you control over which occurrence to keep or remove.
  • Always copy your data to a new sheet or save a backup before removing duplicates, because the deletion cannot be undone.

Using Remove Duplicates to delete repeated rows

If you want to actually delete duplicate entries rather than just see them, use the Remove Duplicates feature. Select all the data including headers, go to the Data tab, and click Remove Duplicates. A dialog box will appear showing all your columns — uncheck any columns you do not want Excel to consider when finding matches, then click OK.

Excel will delete every row that matches an earlier row in the columns you selected, keeping only the first occurrence. If your data has a header row, make sure the My data has headers checkbox is checked so Excel does not treat the header as a duplicate. The tool will tell you how many duplicate rows it removed when it finishes.

This action is permanent and cannot be undone with Ctrl+Z after you close the file, so save a copy of your original data first. If you are unsure which duplicates to remove, use highlighting instead and review them manually before deleting.

Finding duplicates with a COUNTIF formula

A formula gives you more control than the built-in tools because you can see exactly which rows are duplicates and decide what to do with them. In a new column next to your data, type =COUNTIF($A$2:$A$100,A2), replacing A2 and A100 with your actual data range. Copy this formula down the entire column.

The formula will show a number for each row — if the number is 1, that value appears only once; if it is 2 or higher, that value is a duplicate. You can then sort by this column to group all duplicates together, or filter to show only rows where the count is greater than 1. This method lets you review duplicates before removing anything.

If you want to mark only the second and later occurrences (not the first), use =COUNTIF($A$2:A2,A2) instead. This counts how many times the value has appeared up to the current row, so the first occurrence shows 1 and all later ones show 2 or higher.

Checking for duplicates across multiple columns

When you need to find rows that are completely identical across several columns, the Remove Duplicates tool is the simplest option — just select all the columns you want to check and run the feature. Excel will only mark a row as a duplicate if every selected column matches a previous row exactly.

If you want to highlight these matches instead of deleting them, you can use a helper column with a formula. In a new column, type =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2), adding a range for each column you want to check. Copy it down, and any row where the count is greater than 1 is a duplicate across all those columns.

What to do when duplicates have slight differences

Sometimes duplicates are not exact matches — they might have extra spaces, different capitalization, or slightly different spelling. Excel's built-in tools will not catch these because they look for exact matches only. You will need to clean the data first.

Use the Find & Replace tool (Ctrl+H) to remove extra spaces: search for two spaces and replace with one space, then repeat until no more replacements happen. For capitalization differences, you can use the UPPER or LOWER function in a helper column to standardize the text, then check for duplicates in that column. If spelling varies, you will need to review the list manually or use a more advanced tool outside Excel.

Preventing duplicates from happening in the first place

If you are entering data into Excel regularly, you can prevent duplicates before they occur. Select the column where you want to prevent duplicates, go to the Data tab, and click Data Validation. In the dialog, set Allow to Custom and enter the formula =COUNTIF($A$2:$A$100,A2)=1, replacing the range with your actual column.

Now Excel will reject any entry that already exists in that column and show an error message. This works best for columns like customer IDs or product codes where each value should be unique. You can customize the error message to tell users what went wrong, making it clear that the value is already in use.

Frequently Asked Questions

Can I undo Remove Duplicates after I close the file?

No. Once you close the file, the deletion is permanent and Ctrl+Z will not recover it. Always save a backup copy before using Remove Duplicates, or use the highlighting method first to review what will be deleted.

Will Remove Duplicates keep the first or last occurrence?

It keeps the first occurrence and deletes all later ones. If you need to keep a different row, sort your data so the row you want to keep appears first, then run Remove Duplicates.

How do I find duplicates if the values are in different cases, like "John" and "john"?

Excel treats these as different values. Create a helper column using =UPPER(A2) to convert all text to uppercase, then check that column for duplicates instead. Delete the helper column when you are done.

Can I highlight duplicates in one column based on another column?

The built-in highlighting tool only finds duplicates within a single column. Use a COUNTIF formula in a helper column instead, checking only the column that matters, then manually highlight the results or filter by the formula column.

What if I want to keep all duplicates but just see which rows match?

Use Conditional Formatting to highlight them, or add a COUNTIF formula in a helper column to mark which rows are duplicates. Both methods let you see the matches without deleting anything.