This Excel tip from Learn Excel Now is on removing duplicates in Excel. The request is directly from a Learn Excel Now fan who asked us how to quickly remove duplicates. She told us:
“I pulled a list of all company sales from our database. It exported in Excel. The list is all of our sales, but many of our clients buy multiple products several times through the year. Yesterday, my boss asked me ‘how many unique clients have purchased in the last month?’ Those figures aren’t available because we’ve always only counted total sales. I started counting the individual companies 1-by-1, but there are so many of them. I know there has to be a way to remove the duplicates so I can see each company listed only once. Can you help me?”
So, this post was designed for one of our fans – let’s call her Rachel – and anyone else who wants to know about removing duplicates in Excel.
The following table contains multiple cases of duplicates. Similar to Rachel’s request, they are the names of companies in a sales document.
Now, let’s get in there and remove those duplicates! Here’s how you do it:
- Highlight the column(s) or row(s) containing the duplicate values you want to remove
- Go to the ‘Data’ Tab on the Excel Ribbon
- Find the “Remove Duplicates” tool and click it
- Verify the table range on the dialogue box OR select the correct range on the dialogue box
If there are more values in the table but you want to stick to a column or row, select “Use Current Selection
Excel will automatically remove all duplicates. Now you and review and count the total unique values within the range.
We here at Learn Excel Now hope you now feel confident in removing duplicates in Excel. This useful tool is great for running quick analyses, finding unique values and cleaning up datasets for other functions or formatting.
Like Learn Excel Now? Sign up for the e-newsletter to receive weekly Excel tips and special offers just for you!
And don’t forget to follow us on social media and share our Excel tips with your networks!
Kevin – Learn Excel Now
Erasmus Schneider says
Great tip, thanks
but what if I want to know how many times each duplicate appears, e.g. how many times each company had a sale? If the list is short I can do a “countif” function and enter the individual names as criteria, but that does not work if the list is really long with many hundreds of different entries, each with a different number of replicates. Is there an easy way to do that?