Being able to quickly and easily find duplicates in your data can be a game changer. If you’re using a CRM, you’ll usually have a dupe function, but it doesn’t always help if you’ve got a separate file of data, or don’t have the time to wade through everything it throws up just to find the ones you actually need to resolve.
There are loads of ways of doing this. This is a quick and easy method that doesn’t need any experience of pivot tables, Power Query, or anything like that. It also assumes you want to see the duplicates so you can decide what to do next: merge, delete, or put to one side.
- Open your data in Excel
- Highlight the column you’re going to match on (e.g. email address or ID)
- On the ribbon: Home → Conditional Formatting → Highlight Cells → Duplicate Values
- Choose how you want duplicates to be formatted – pick a font or cell colour that stands out
- Click OK
At this point, you could scan through the list (if it’s not too long) to find the duplicates. If you’ve got more data, you can surface them more quickly using sort.
- Select all your data
- On the ribbon: Data → Sort
- In the sort box:
a. Sort by – choose the same column you used above (e.g. email or ID)
b. Sort on – choose Cell Colour
c. Order – choose the duplicate highlight colour
d. Select On Top
e. Click OK
Your highlighted duplicates should now appear together at the top of the list. You haven’t fixed them yet, but you’ve brought them to the surface so they’re much easier to review.
Make sure you sort the whole dataset, not just one column – otherwise things can get out of sync very quickly.
Grab a cuppa and take a moment to enjoy the satisfaction of mastering a new Excel skill.
If you want to take a proper look at what’s sitting in your data – duplicates or otherwise – I’ve got some time over the coming months to help you get things into a much cleaner, more manageable place.
And if you know a team that’s quietly battling their spreadsheets (and pretending everything’s fine), I’d really appreciate an introduction.
