FREE CHEAT SHEET
All the key points in one handy PDF
Plus, collect a FREE copy of our 30 Excel Power Tips.
Enter your details for immediate access.
Duplicates can cause lots of headaches in Excel.
They can throw out your tallies or counts, and can cause the wrong information to be reported.
If you have the same person listed in your database two or more times, perhaps with the same customer ID or same name, but with two different residential addresses because they moved house, then which one is the current record?
The likelihood of this occurring increases the larger your database becomes and the more people use it.
Excel provides 2 tools for handing this situation and cleaning up your database. First I’ll explain how to identify duplicate entries in your data then I’ll show you how to remove duplicates quickly.
It’s good to know how to deal with duplicates effectively.
The first tool is one of the options on the Conditional Formatting feature.
As its name suggests, there are two components - a condition and a format.
Conditional formatting allows you to check the value of a cell and if a certain condition is true then apply a particular cell format.
If the value of a cell is greater than 50, format the cell with white text and a red fill.
When checking for duplicates the test is:
Does this cell have an identical twin?
So, here’s the process:
Once this is done, any cell that has another cell with an identical value is highlighted.
To remove duplicates from a single column:
Highlighting and removing duplicates is easy for a single column of data, but it’s very rare that you have just one column. Normally, that data is part of a larger table and you need to remove the entire duplicate row.
On a larger table the first duplicate cell might be on row 5 and the next one on row 105 and the next one on row 505.
So is there an easy way to collect them all together in one place?
Good question. I’m glad you asked.
Yes, there is.
Excel allows you to sort by colour.
The duplicate rows are now all grouped together at the top of the table. From here you can choose which rows to remove and delete them manually.
Note: When you have several duplicate items, they may not be paired together even though all they are all listed at the top of the table. To rectify this you can add a second sort level that sorts the data by value, smallest to largest.
Consider this example. Two columns have been selected and checked for duplicate values.
As you can see Bloggs has been highlighted 3 times - twice for Fred and once for Del.
Also, Smith has been flagged as duplicate, even though one is Joe and one is Fred.
And finally, Fred has been highlighted 3 times - twice for Bloggs and once for Smith.
The only 2 entries that should be flagged here are Fred Bloggs.
To get around this, we can join first name and last name into a single full name using CONCATENATE. In cell D3, write the formula:
=CONCATENATE(B3, " ", C3)
The CONCATENATE function joins together all the items listed inside the brackets. As you can see, the first name and last name have been separated by a space.
Now, simply check the Full Name column for duplicate values.
I hope this has opened up your eyes and given you a few good ideas.
Don't forget, you can download a free cheat sheet with all the key points from this post.
Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.
Cheers. Here's to your learning and success. Enjoy the rest of your day.