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.
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.
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.
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.
Now a dialogue box will appear like the following picture.
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?
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.
After that, we will get the Dialogue Box as follows.
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.
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.
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.
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.
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.
Easy. Isn’t it?
Read More: How to Remove Duplicate Rows in Excel Table
- How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)
- Excel VBA: Remove Duplicates from an Array (2 Examples)
- How to Delete Duplicates in Excel but Keep One (7 Methods)
- Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)
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.
Now press ENTER key.
Now we will use AutoFill for the rest of the series.
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.
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.
Now press ENTER key.
This formula is returning us unique values.
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…
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.
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.
Method 7: Removing Duplicate Names from Single Column Using VBA
In the pop-up code window, from the menu bar, click Insert -> Module.
Now copy the following code and paste it into the Module.
Sub RemoveDuplicatesFromSingleCol() Range("B5:B15").RemoveDuplicates Columns:=1, Header:=xlYes End Sub
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.
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.
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.