How to Remove Duplicate Names in Excel (7 Simple Methods)

Microsoft Excel is a frequently used program, but when it comes to Removing Duplicate data, it may be a bit complicated. When working with large datasets, removing duplicates in Excel is a common chore. But removing duplicates is quite an easy task in Excel. In this article, we tried to illustrate 7 quick and simple methods on how to remove duplicate names in Excel.
We’ll use a sample dataset as an example, to help you understand the concept better. Here, we are given some Names, States, and Occupations in three different columns. These columns contain duplicate names. Now, we will see, how to remove duplicate names.

How to Remove Duplicate names


Download Practice Workbook


7 Simple Methods to Remove Duplicate Names in Excel


Method 1: Remove Duplicate Names in Excel using the Remove Duplicates Feature

Excel has so many built-in tools to help you perform different kinds of Arithmetic and Logical operations. To remove repeated entries Excel has a special tool called Remove Duplicates in the Data tab. Without any further ado, let’s jump into this,
First, click on any cell or specific range in the data set. Excel can understand the range for you automatically.
Now click on the Data tab and select Remove Duplicates.


Now, a dialogue box will pop up like the image below.


As we are just removing duplicate names, that’s why we have to uncheck the rest of the two columns States and Occupation as shown in the above picture. Now click OK.
After clicking OK in the dialogue box, now, Excel will tell us how many duplicate names it has found. In this scenario, it has found only three like the following image.

remove duplicate by data tab
Now click OK again and we will get the following result.


If you compare it with the sample data you will notice that James, Olivia, and Emma these three duplicate names have been removed.

Read More: Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)


Method 2: Remove Duplicate Entries in Excel by Advance Filter Option

Excel Advanced Filter helps you to remove duplicate values and extract unique values to the same location or any other location in the Excel sheet you prefer.
First, we will select any cell in the name column or the entire range. Then go to the Data tab and click the Advanced Filter option.

How to remove duplicate names usng advance filter
Now a dialogue box will appear like the following picture.

remove duplicates by advance filter
We will check the Unique records-only option from here and click OK.
So, here we go, the column is left with unique values only and all the duplicate names are deleted.
Isn’t it simple?

Read More: How to Remove Both Duplicates in Excel (5 Easy Ways)


Method 3: Remove Duplicate Data in Excel using Pivot Table

Pivot Table is one of the most advanced features in Excel for analyzing data. But we can also remove duplicate data using this tool. You are asking how? I am going to show you then.
First, we will select the entire table, then will go to the Insert tab and click on the Pivot Table option as shown in the image below.

remove duplicate names by pivot table
After that, we will get the Dialogue Box as follows.

remove duplicate names by pivot table
From here, we will select the Existing Worksheet. If you want your data in a new worksheet we can choose New worksheet. Then we will select any blank cell in the Location and click OK.
This PivotTable fields list will appear. Now we will select the names and drag them down to the Rows as shown in the image.


Here you go, all the duplicate names have been removed.

Read More: How to Remove Duplicates and Keep the First Value in Excel (5 Methods)


Method 4: Remove Duplicate Entries in Excel with Power Query

Excel’s Power Query allows you to import data from a variety of sources, clean it up, and alter it. This tool makes removing duplicates in Excel a piece of cake.
If you are using Excel 2013 or the lower version you have to install a power query. You can download it from here.
In Excel’s updated version this option is available in Data Tab. But, don’t worry, the functions are kind of the same, it doesn’t differ much and this article is useful enough to guide you for all the Excel versions.
First, we have to select the entire table we are working with. Now click on the Data tab if you are using Excel updated versions.

remove duplicate by power query
If you are using the 2013 or lower version then do as the following image shows.

We will select the entire table, then go to the Power Query/Data tab and click on From Data/Range. After that, a dialogue box will appear. We will select My Table has headers if we selected columns with a header, otherwise, we have to uncheck it. Then click OK.

remove duplicate names using power query
This will take us to a new window. We call it the Power Query Editor window. This window looks like this.


Now, click on the Names header and left-click the mouse button, and select Remove Duplicates.

remove duplicate entries
Now, we will get the result as follows. Then to import data in our worksheet, just click Close & Load as shown in the image below.


We are done, data is imported into a new worksheet, and you can see the following image.

how to remove duplicates by power query in excelEasy. Isn’t it?

Read More: How to Remove Duplicate Rows in Excel Table


Similar Readings


Method 5: Remove Duplicate Names in Excel Using Formula

We can use formulas two remove duplicate names. I am going to show you two simple methods here.

5.1 Remove Duplicate Names using COUNTIF Function

We can use the COUNTIF function to remove duplicate names in Excel. Let’s see,
First, click on cell E5 and type the following formula.

=COUNTIF($B$5:B5,B5)

remove duplicate names by formula

Now press ENTER key.

Now we will use AutoFill for the rest of the series.

removing duplicates by COUNTIF function
What is happening here?
We are telling Excel to count names serially and Excel is returning us the data per occurrence of the name. Emma has a count of 1 in cell E6 but in cell E10 as it occurs for the second time, that’s why our formula is returning the count as 2.
Now, click Filter in the Data tab.

removing duplicate names by countif
After that, click on the Filter Option in the Count header and unclick 2, then click OK.


After clicking OK the result will be as follows.


All the duplicate names are filtered. Now we can unfiltered, then copy the table for further work.


5.2 Remove Duplicate Names using UNIQUE Function

Now we are going to learn about the UNIQUE function in Excel.
First, click on the cell where we want our data and type the following formula.

=UNIQUE(B4:B12)

how to remove duplicate
Now press ENTER key.

how to remove duplicate names by Unique function
This formula is returning us unique values.

Read More: How to Remove Duplicates from Column in Excel (3 Methods)


Method 6: Delete Duplicate Entries in Excel Using Conditional Formatting

First, select the range we want for Conditional Formatting. Now, go to the Conditional Formatting then Highlight Cells Rules then click New Rules.


Now, a dialogue box will appear.
After that, type the following formula as shown in the picture and click Format…

=COUNTIF($B$5:$B5,$B5)>1

how to remove duplicate names by conditional formatting
Here, the COUNTIF function will count the values occurring more than once based on the given criteria and will Format those values based on your used Format.
After clicking OK a new dialogue box will appear and we have to choose a color from here. After choosing Fill color, click OK.


Now this will return us to the Conditioning Formula box, again we will click OK and after that, we will get the result as follows.

removing duplicte names by conditional formatting
Emma has a count of 1 in cell E6 but in cell E11 as it occurs for the second time, that’s why our formula is returning the count as red color. As here we have found the duplicate values.
Now we can use the Filter command to remove or Filter the duplicate entries. You can follow method 5.1 to apply the Filter as well as to remove the duplicate entries.

Read More: How to Remove Duplicates Based on Criteria in Excel (4 Methods)


Method 7: Removing Duplicate Names from Single Column Using VBA

We can remove duplicate entries using Excel VBA also. Here is how,
Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.


In the pop-up code window, from the menu bar, click Insert -> Module.

How to remove duplicate names using VBA in excel
Now copy the following code and paste it into the Module.

Sub RemoveDuplicatesFromSingleCol()
Range("B5:B15").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

removing duplicate entries by vba
Through this code, we are telling Excel to remove all the duplicates from a single column by using the RemoveDuplicates method.
Here, the column ranges from B4 to B15.
Now our code is ready to run. Press F5 on your keyboard or from the menu bar select Run -> Run Sub/User Form. You can also just click on the Small Play Icon in the sub-menu bar to run the macro.


You will notice that the duplicate values from the given column have been removed by running the VBA code.


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice worksheet where you may practice these methods.

How to removie duplicate name entries


Conclusion

These are seven different ways to remove duplicate names in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback. You may also browse this site’s other Excel-related topics.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo