Finding duplicates in Excel

Excel has two built-in ways to spot duplicate data: the Remove Duplicates tool, which deletes them automatically, and Conditional Formatting, which highlights them so you can review them first. Which one you use depends on whether you trust the duplicates are actually unwanted — Remove Duplicates is faster if you do, but Conditional Formatting is safer if you need to look at each one.

The fastest method is Remove Duplicates. Select the entire data range (including headers), go to the Data tab, click Remove Duplicates, choose which columns should be checked for matches, and click OK. Excel will delete any rows where those columns contain identical values and tell you how many rows it removed. This works well when your data is clean and you know duplicates serve no purpose.

If you want to see the duplicates before deleting them, use Conditional Formatting instead. Select your data range, go to the Home tab, click Conditional Formatting, choose Highlight Cell Rules, then Duplicate Values. Excel will color every duplicate cell so you can scan the spreadsheet and decide which ones to keep. You then delete the rows manually by right-clicking and selecting Delete.

Key Takeaways

  • Remove Duplicates (on the Data tab) deletes matching rows in one step, but you cannot undo it after closing the file, so save a backup first.
  • Conditional Formatting highlights duplicates with color so you can review them before deciding which rows to delete.
  • Both tools check only the columns you select, so you can find duplicates based on ID number alone, or on ID plus name, depending on what matters for your work.
  • If you delete a row by mistake, Ctrl+Z (or Cmd+Z on Mac) will undo it — but only while the file is still open.

Using Remove Duplicates step by step

Start by selecting all your data. Click the cell in the top-left corner of your data (usually A1), then hold Shift and click the cell in the bottom-right corner. Or click the box in the top-left corner of the spreadsheet (where the row numbers and column letters meet) to select the entire sheet.

Go to the Data tab at the top of the ribbon. Click Remove Duplicates. A dialog box will open showing all your columns. Uncheck any columns that should not be part of the match — for example, if you have an ID column and a Name column, and you only want to find rows where the ID is identical, uncheck Name. Click OK.

Excel will delete the duplicate rows and show you a message saying how many were removed and how many remain. The rows are gone when ready. If you made a mistake, press Ctrl+Z (or Cmd+Z on Mac) right away to undo. After you close and reopen the file, undo will no longer work, so save a copy of your original data first if you are not certain.

Using Conditional Formatting to review duplicates

Select your data range the same way: click the top-left cell, hold Shift, and click the bottom-right cell. Go to the Home tab. Click Conditional Formatting (usually on the right side of the ribbon). Click Highlight Cell Rules, then Duplicate Values.

A dialog box will ask what color to use. The default is light red. Click OK. Every cell that contains a value matching another cell in the same column will be highlighted. Scan through your data and decide which rows to keep. You might keep the first occurrence and delete the rest, or you might keep the most recent entry based on a date column.

To delete a row, right-click the row number on the left side and select Delete. Excel will remove that entire row and shift the rows below it up. Repeat for each duplicate row you want to remove. When you are done, you can remove the highlighting by going back to Conditional Formatting and clicking Clear Rules > Clear Rules from Entire Sheet.

When duplicates are harder to spot

Sometimes duplicates are not exact matches. A person might be listed as "John Smith" in one row and "J. Smith" in another, or a company might appear as "ABC Corp" and "ABC Corporation". Neither Remove Duplicates nor Conditional Formatting will catch these — both tools look for identical text, not similar text.

For fuzzy matches like these, you have two options. The first is to clean your data manually before running Remove Duplicates: use Find and Replace (Ctrl+H) to standardize abbreviations, or add a helper column with a formula that extracts just the first and last name, then run Remove Duplicates on that column. The second option is to sort your data by the column most likely to contain duplicates (like last name), then scan visually for rows that look similar and delete them by hand.

A third approach is to use a pivot table or a UNIQUE formula (in Excel 365 and newer versions). A pivot table groups identical values together so you can see them side by side. The UNIQUE function returns only the first occurrence of each value, which you can copy to a new sheet and compare against your original data. Both methods take longer but catch patterns that exact-match tools miss.

Protecting yourself before deleting

Before you run Remove Duplicates, save your file with a new name. Go to File > Save As, type a new filename (like "Sales Data - Original"), and click Save. Keep this copy untouched. Then go back to your working file and run Remove Duplicates on that. If something goes wrong, you still have the original.

Another safety step is to add a helper column that marks which rows are duplicates before you delete anything. In a blank column, type a formula like =COUNTIF($A$2:$A2,$A2) (adjusting the column letter to match your data). Copy this formula down to every row. Any row where the result is greater than 1 is a duplicate. You can then sort by this column to group duplicates together, review them, and delete only the ones you are sure about.

Removing duplicates from specific columns only

Sometimes you have data where certain columns should be unique, but others can repeat. For example, you might have a customer ID column that should never repeat, but a Purchase Date column where the same date appears many times. In this case, you want to find rows where the ID is the same, not rows where the date is the same.

Use Remove Duplicates and uncheck the columns that are allowed to repeat. Select your data, go to Data > Remove Duplicates, and uncheck every column except the ones that define a duplicate. If you only check the ID column, Excel will delete any row where the ID matches a previous row, regardless of what the other columns contain. This is the most common scenario for customer lists, inventory sheets, and transaction records.

Frequently Asked Questions

Can I undo Remove Duplicates after I close the file?

No. Once you close and reopen the file, the undo history is gone and the deleted rows cannot be recovered. This is why you should save a backup copy before running Remove Duplicates. If you delete rows by mistake while the file is still open, press Ctrl+Z when ready to undo.

What if two rows are identical except for one column?

Remove Duplicates will treat them as duplicates if you include all columns in the check. If you want to keep both rows because they differ in one important column, uncheck that column before running the tool. For example, if two rows have the same customer ID but different order dates, uncheck the date column so only the ID is checked.

Does Remove Duplicates keep the first or last occurrence?

It keeps the first occurrence and deletes all later ones. If you need to keep the last occurrence instead, sort your data in reverse order before running Remove Duplicates, then sort back to the original order afterward.

Can I find duplicates across multiple sheets?

No, Remove Duplicates and Conditional Formatting only work within a single sheet. To find duplicates across sheets, copy all the data into one temporary sheet, run the tool, then move the results back. Or use a VLOOKUP or INDEX/MATCH formula to compare values between sheets manually.

What is the difference between Remove Duplicates and Conditional Formatting?

Remove Duplicates deletes rows permanently and when ready. Conditional Formatting only highlights cells so you can see them — you must delete the rows yourself. Use Remove Duplicates when you are confident all duplicates are unwanted. Use Conditional Formatting when you want to review each one first.