Now we are coming to a very useful but rather unknown function: Remove Duplicates. Let’s say, you got a list of names and some names are twice or more often on the list. With Remove Duplicates, you can delete those duplicated names automatically so that each name appears only one time. In this article we’ll learn how to easily remove duplicate values in Excel.
Contents
How to remove duplicates: The fastest method!
The steps for removing duplicate values in a column (or several columns) are quite easy (the numbers are corresponding to the picture above):
- Select the list or table that you want to remove the duplicates.
- Click on “Remove Duplicates” on the Data ribbon.
- If the cells next to your selected range are filled with data, Excel might ask you if you would like to extend your range. But if your data range is selected correctly, select “Continue with the current selection” and then “Remove Duplicates”.
- If your selected range has a header, pay attention to the check box “My data has headers”.
- Click “OK”. Now you will receive a message reporting how many duplicate values have been removed.
Please note that blank cells also count as one possible name. After removing duplicates, one blank cell will still be there, if your data contains at least one blank cell.
Use the UNIQUE function in Excel
Yes, there is a function in Excel that can do that! It’s called UNIQUE and was introduced in 2021 (Microsoft 365 subscribers have it first). The basic usage is very simple as shown in the following screenshot:
Just type =UNIQUE( and select the list (here B4:B8).
The advantage of this method: It is dynamic. If you add or remove an item from your list, also the list of unique items automatically adapts.
Other methods to condense lists to only show unique values
The method above is probably the fastest way to remove duplicate values. But there are also other ways to achieve this:
- Use PivotTables. PivotTables can do pretty much the same with a small advantage: They can easily be refreshed once the data changes. Please refer to this article in order to learn more about PivotTables.
- Do you only want to count the number of unique items in a list? We have a whole article just about this problem. Please follow this link to check out the five different methods!
- Aaaand, of course: There is always PowerQuery!
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
