How to Find Duplicates in Excel and Remove Their Rows (3 Methods)

The duplicate remover is a very handy built-in tool that will do all of the heavy lifting for you. It'll automatically find any cells with the same text. Here's how to use it:

1. Select your entire table
    

   Select the area you would like to inspect, or the entire document with Ctrl + A, and switch to the “Data” tab in your ribbon. While there, click “Data Tools > Remove Duplicates”.

   

2. Tick or untick “My data has headers”
    

   If your table starts at row 1, you can untick “My data has headers” otherwise, leave it on and you'll see your columns ticked in the window below. Press “OK”.

   

3. Wait for Excel to delete duplicate rows
    

   The in-built Excel duplicate formula will try to find genuine duplicates and automatically remove them. Once it's done, you can press “OK”. You can see that we have used to delete duplicate rows of the “Japan” entry.

   

If you don't trust Excel's duplicate remover to do its job properly, you can use Advanced Filtering instead. This tool will help you to perform a quick duplicate check but will hide duplicate rows rather than removing them.

1. Run the Advanced Filtering tool
    

   Select the data you want check. Then, in your Excel ribbon, switch to the “Data” tab, and click “Sort & Filter > Advanced”.

   

2. Tick “Unique records only” and press “OK”
    

   The unique records box will ensure only rows that are different to each other will be displayed in your documents.

   

3. Delete or hide any remaining duplicates
    

   By default, Excel's Advanced Filtering will assume that there are headers in your table. This means that whatever is in the top row you selected will stay there. Because of this, you may find you still have a single duplicate row in your Excel document. Delete or hide it manually if required.

   

So, you know how to delete duplicate rows in Excel, but what if you want to replace them with entirely different text. The best method for this, unsurprisingly, is the Find & Replace tool. Here's how you can use it for this function:

1. Copy the row that's been duplicated
    

   Select the row that you suspect has been duplicated and press Ctrl + C to copy its contents. Then press Ctrl + F and switch to the Replace tab. Paste your row into the “Find what” field with Ctrl + V and click the “Options >>” button.

   

2. Match the cell contents and find and replace the row
    

   It's important that you then tick “Match entire cell contents” to ensure only exact duplicates are removed. You can then enter the text you want to replace the duplicates with and click “Replace All”.

   

3. Wait for Excel to delete duplicate rows
    

   Once the process is complete, you'll get the message “Excel has completed its search and has made x replacements”. You'll notice that each cell of the row you deleted has been replaced by the text specified. Press “OK” and close the find and replace window.

   

4. Paste your original row back in
    

   Excel's find and replace removes all content that matches its query, including your original row. You can use the data you already have in your clipboard to paste it back in its original position.

   

5. Delete the replaced rows if necessary
    

   To do so quickly, Ctrl + click the cell number to the right of each row, then right-click one and select “Delete”. This will delete them all in one go.

   

6. Check for any remaining duplicates
    

   The find and replace in Excel should delete all duplicate rows but check again just to be sure. If you accidentally added an additional letter to one, it wouldn't match exactly and therefore would not be removed. Save your document when you're done.