When working with large Excel files, people tend to make mistakes and add duplicate entries in the worksheets. Here’s how to find duplicate entries in Excel spreadsheets the easy way.
Note: This works when you’re working with multiple columns.
First, open the Excel file you want to check. Then click the Data tab.
Click on Advanced from Sort and Filter options.
Select Copy to Another Location under action menu.
Now highlight the range field to select the data you want to check for duplicate entries. Also select the area where you want to paste the filtered results, check the Unique Fields only option and click OK.
Excel displays the result in the area selected excluding any duplicate entry. Select a new sheet or workbook depending on your needs.
The procedure is a bit different when you want to filter a single column. If you use the above procedure to find duplicate entries from a single column sheet, you’ll get the following error.
To find duplicate entries from a single column, use the conditional formatting feature in Excel. Click the Home tab and select Conditional Formatting.
Expand the Conditional Formatting menu and go to Highlight Cell Rules >> Duplicate values.
The Duplicate Values field comes up. Change formatting of the duplicate entries. For example, I selected to fill the duplicate entries with red background and color. Click OK when done.
It will highlight the duplicate entries in the selected color. This makes them easier to distinguish from the other fields.
If you have a large Excel spreadsheet and want to easily find duplicate entries, this method is simple and easy to use. No third party plugin required.
9 Comments
Leave a Reply
Tenika
Remove Duplicates in the Data section of the ribbon is also a useful & simple way to remove duplicates from within one or several columns.
No
..an even easier way is to capture your database and run a pivot table report, selecting the column that has the suspected duplicates as the data target and choose “count”. Once its run – sort on the count results and all the duplicates will be at the top (or bottom) showing the number of times excel found the same entry.
If you want you can the double click the count value result and a new sheet will open showing the guilt-ridden items.
You can also use the same method if you need to combine more than one column, just concatenate the chosen columns eg (=B6&C6&F6) use this new column as the data target – ditto above. Make sure all the database is captured when using the pivot table.
Abdul
O I love this post . . . Thnx
Tom
Just an FYI…but the Excel Remove Duplicates function is flawed and cannot be relied upon when dealing with large amounts of data. Formatting gets in the way.
Dominique Pere
I agree with you..this is one of the reason which has propelled us to build Podbox. A web-app acts as a complimentary application to support cleaning features from excel and any various softwares. Find duplicates entries and de-deplicate them without breaking a sweat with Podbox.
DuplicateFilesDeleter
thanks for your nice information!
keving
niladry
i cant select duplicate file in this way. please help me.
Connie
ok, i found the duplicates.
But,, now how do i make the one that i want, to be black type and no pink background?
Ken Blanchard
The conditional formatting worked perfectly. I did have to concatenate two fields to get the unique values I was looking for and then Poof, done. Awesome technique, thanks,