I love databases. It’s true. I’m a secret geek and I love finding new ways of working with Excel and making it do things even quicker and easier than anybody else! This month we’ve been doing lots of database work, there’s no particular reason, except perhaps as things quieten down for summer, customers are breaking out the ‘jobs for a rainy day’ to do lists and database cleansing is usually high up there!
One of the first questions that came up from my team is “what’s the best way to remove duplicates from this list” – so I thought I’d share some of this with you.
If you have a simple list of data with records that are exact duplicates of each other, then advanced filter is a great way of doing this quickly. There are lots of uses for the Advanced Filter tool in Microsoft Excel, and removing duplicates from a list is just one of them.
Setting up Advanced Filter
Open up your spreadsheet and select the sheet you want to remove duplicates from. Then highlight the data you want to remove duplicates from. This will work on single columns or whole ranges of data.
From the main navigation select Data tab > Sort & Filter group > Advanced
You’ll then see the advanced filter tool open up. Here you need to specify the data to look at. By default it will look at the data you have already selected, but you can change this if you need to.
Select the option to copy to another location and tick the box that says “Unique Records Only“. This hides duplicate values, allowing the final list to be copied somewhere else with no duplicates.
Leave the box for Criteria Range blank – this is used if you want to specify multiple criteria to filter the data by, but in this case all we want to do is remove duplicates and not apply any other filter criteria. So we leave it blank.
Finally, select the area of the sheet where you want your de-duplicated data to be copied to. In this example I’m adding it below the existing data, but you can choose to move it wherever you want to; a place on the same sheet, a different sheet, or even a different workbook.
When de-deping data this way, it is preferable to move the data to a new location, as this removes the duplicated records completely. You can choose to leave the list in the same place by selecting the option to ‘filter the list in place’ but if you do this, the duplicate values will only be hidden, and not fully removed.
Once you’ve completed your settings, click ‘OK‘
You’ll then see your new cleansed data appear in the location that you specified ready to work with it further. If you wish to, you can now delete or move the original data.