Have you ever wanted to remove duplicates from a list in Excel?
It’s a common issue. You have a long list of names or other values where some of them are repeats, and you need a list where each value appears only once.
There are two quick and easy ways to do this.
Method 1: The Remove Duplicates Button
The fastest and easiest is with the “Remove Duplicates” button.
Simply copy your original list, highlight it, then go to the Data ribbon. In the Data Tools section, there’s a button with two blue bars sandwiching a grey bar:
Press that, and you’re done.
This is the five second solution. But the downside is that it doesn’t dynamically update.
What if this is a spreadsheet you’ll be working with in the future? Do you need your short list to automatically include any new values you add to your long list?
Then you need to use a formula instead.
Method 2: The UNIQUE Formula
You used to need really complicated confusing formula to do this, but Microsoft recently updated Excel with a much simpler way.
Simply use the formula =UNIQUE, then select the list you want to remove duplicates from as your range. Make sure your range ALSO includes a bunch of extra cells as the bottom, so the formula will pick up any new values you add in the future.
The downside with this method is that it adds a zero at the bottom of your list. I explain how to fix that at 1:40 in the video embedded above.
Is there anything you’d like to know how to do in Excel? Submit your questions to get them answered for free! Or if you have more complicated questions specific to your business, book a free consultation to learn how we can save you time and money.