The fastest way to spot duplicates in Excel

Excel has a built-in tool called Conditional Formatting that highlights duplicate values in seconds. Select the column or range of cells you want to check, go to the Home tab, click Conditional Formatting, then choose Highlight Cell Rules and Duplicate Values. Excel will color any repeated entries so you can see them immediately.

If you want to remove duplicates entirely rather than just see them, use the Data tab instead. Select your data range, click Data Tools, then Remove Duplicates. Excel will delete the extra copies and keep one version of each entry. This method works best when you have a simple list and don't need to keep track of which rows were removed.

For more control over what counts as a duplicate — for example, if you want to check only certain columns — the Advanced Filter method gives you options. This approach takes a few more steps but lets you decide exactly which columns matter when Excel decides if two rows are the same.

Key Takeaways

  • Conditional Formatting highlights duplicates in place so you can review them before taking action.
  • The Remove Duplicates tool deletes extra copies automatically, keeping one version of each entry.
  • Advanced Filter lets you specify which columns should be checked when looking for matches.
  • Always make a backup copy of your spreadsheet before removing duplicates, since the action cannot be undone.
  • Exact matches only count as duplicates — "Smith" and "smith" are treated as different entries unless you change Excel's settings.

Using Conditional Formatting to highlight duplicates

Conditional Formatting is the safest way to find duplicates because it doesn't change your data. Select the range of cells you want to check — click the first cell, hold Shift, and click the last cell in your range. If your data has headers, include them or exclude them consistently; Excel treats headers the same as any other row.

Open the Home tab at the top of the screen. Click Conditional Formatting, which is usually on the right side of the ribbon. A menu will drop down; click Highlight Cell Rules, then Duplicate Values. A dialog box will appear asking what color you want duplicates to be. The default is light red; you can change it if you prefer. Click OK.

Any cell that contains a value appearing more than once in your range will now be highlighted. This includes the first occurrence and all copies. If you have 50 rows and the name "Johnson" appears three times, all three cells will be colored. You can now scroll through and decide what to do with each duplicate — delete it, edit it, or leave it alone.

Removing duplicates permanently with the Remove Duplicates tool

The Remove Duplicates tool is faster if you want duplicates gone and don't need to review each one. Select your data range the same way: click the first cell, hold Shift, and click the last cell. Include headers if your data has them.

Go to the Data tab. Look for Data Tools in the ribbon (the exact location varies by Excel version, but it is always on the Data tab). Click Remove Duplicates. A dialog box will appear showing all the columns in your range. By default, all columns are checked, meaning Excel will treat two rows as duplicates only if every single column matches exactly. If you want to check only certain columns — for example, only the "Name" column — uncheck the columns you want to ignore.

Click OK. Excel will delete all duplicate rows and tell you how many it removed. The action cannot be undone with Ctrl+Z if you close the file, so save a backup copy first if you are unsure.

Checking specific columns for duplicates

Sometimes you have data where one column matters more than others. For example, you might have a customer list where you care about duplicate email addresses but not duplicate names — two people named "Sarah Chen" are fine, but two rows with the same email are a problem.

Use the Remove Duplicates dialog to uncheck the columns you don't care about. If you only want to find duplicate emails, uncheck every column except the email column before clicking OK. Excel will then delete rows only when the email matches, even if the names or other details are different.

If you want to see which rows are duplicates in certain columns without deleting anything, use Conditional Formatting on just those columns. Select only the email column (or whichever column matters), apply Conditional Formatting, and you will see only the duplicates in that specific column highlighted.

Understanding how Excel defines a duplicate

Excel treats duplicates as exact matches. "Smith" and "smith" are different entries because of the capital letter. Spaces matter too — "John Smith" and "John Smith" (with two spaces) are not the same to Excel. If your data came from different sources or was typed by different people, small differences like these can hide duplicates.

Numbers are compared by value, not appearance. If one cell shows "100" and another shows "100.0", Excel sees them as the same number and will treat them as duplicates. Leading zeros matter for text — "00123" and "123" are different.

If you suspect duplicates are hidden by small differences, clean your data first. Use Find and Replace to remove extra spaces, or convert everything to the same case (all uppercase or all lowercase) before checking for duplicates. You can always undo these changes if you need the original formatting back.

Using Advanced Filter for complex duplicate checks

Advanced Filter gives you more control but requires more steps. Select your data range, go to the Data tab, and click Advanced (or Advanced Filter, depending on your Excel version). A dialog box will open with options.

Check the box that says No duplicates or Unique records only. You can choose to filter in place (hide duplicates but keep them in the file) or copy unique records to a new location. If you copy to a new location, Excel will create a new list with only one copy of each entry, leaving your original data untouched.

This method is useful when you want to create a clean list without changing your original data, or when you need to check for duplicates across multiple columns with specific rules. It takes longer than the other methods but gives you the most flexibility.

What to do after you find duplicates

Before you delete anything, decide whether the duplicates are mistakes or legitimate repeats. A customer database might have the same person listed twice with slightly different information — deleting one copy could lose important details. A mailing list with duplicates is almost certainly a mistake.

If you use Conditional Formatting to highlight duplicates, you can review each one and decide individually. If you use Remove Duplicates, Excel keeps the first occurrence and deletes the rest, so make sure the first copy is the one you want to keep. You can sort your data before removing duplicates if you want to control which copy survives.

After removing duplicates, spot-check your results. Scroll through the data and make sure the rows you expected to see are still there. If something looks wrong, close the file without saving and try again with a different approach.

Frequently Asked Questions

Can I undo Remove Duplicates after I save the file?

No. Once you save after removing duplicates, the deleted rows are gone. Always save a copy of your original file before using Remove Duplicates. If you make a mistake, close the file without saving and reopen the backup.

Why does Excel say there are no duplicates when I know there are?

Small differences hide duplicates. Check for extra spaces, different capitalization, or different number formats. Use Find and Replace to clean up spacing, or convert text to the same case before checking again.

If I use Conditional Formatting, will it update automatically if I add new rows?

No. Conditional Formatting applies only to the range you selected. If you add new data below, it will not be checked. Reapply Conditional Formatting to the new range, or select a larger range at the start to account for future data.

Can I check for duplicates across multiple sheets?

The built-in tools check only one sheet at a time. To find duplicates across sheets, copy all the data into one sheet, check for duplicates, then move the results back. Alternatively, use a formula like COUNTIF to manually check if a value from one sheet appears in another.

What happens to formulas when I remove duplicates?

If your duplicate rows contain formulas, those formulas are deleted along with the row. If the formulas in other rows reference the deleted rows, those references will show an error. Check your formulas after removing duplicates to make sure nothing broke.