How to Remove Duplicate Names in Excel (6 Simple Methods) -

The following dataset contains the names of some salesmen, their state addresses, sales amounts in dollars, and target achievements.

Method 1 – Using the Remove Duplicates Feature

Select any cell from the dataset.
Select Data >> Remove Duplicates from the Data Tools

The Remove Duplicates dialog box will pop up.
Check a column name on which you will operate the Remove Duplicates. Make sure to select My data has headers.
Click OK.

All the duplicate names are removed. Only the unique names will prevail in the sheet.

If you want to remove duplicate values based on the whole contents of the rows, you need to check all the columns. Here, the 6th and 12th rows of the sheet are exactly the same. The same can be said for the 8th and 11th rows.

The following picture shows the unique rows only.

Note: The command may remove some necessary data. You should keep a copy of your original data in your Excel workbook.

Method 2 – Using an Advanced Filter

Steps:

Select the Names column.
Go to Data >> Advanced Filter from the Sort & Filter
Check Unique records only on the Advanced Filter dialog box and click OK.

You will see all the duplicate names removed from the data table.

Here, we removed duplicate data based on Names. You can also do the same for the States column too. Hopefully, the following video will be helpful on this matter.

We removed the duplicate ‘States’ data similarly.

Note: We cannot get summarized data for each unique entry using the Advanced Filter feature.

Method 3 – Using a Pivot Table

Steps:

Select the whole data table.
Select Insert >> PivotTable.
In the PivotTable dialog box, choose the sheet where you want to keep the PivotTable. We selected a New Worksheet for our PivotTable.
Click OK.

The PivotTable will appear in a new sheet. Using this PivotTable, we can make a data table without Duplicate Names. Watch the video below to understand this idea.

Advantage: The main advantage of using PivotTable is getting the summarized data for each entry. We got the unique names that appeared first in the data table when we used Advanced Filter.

Method 4 – Using a Formula

Steps:

4.1. Dynamically Remove Duplicate Names Using UNIQUE Function

To make the procedure dynamic, convert the dataset to a table.
Select the whole data range and press Ctrl + T
Check that My Table has headers, and click OK on the dialog box.
Select any cell where you want to keep the unique names and type the formula below.

=UNIQUE(B4:E14)

The formula will return the unique rows only.

The returned range does not have any formatting. So format it according to your convenience.

Advantage: The procedure is dynamic. Once you insert a new name, it will automatically update the range obtained using the UNIQUE function. In the above image, we added unique data in the 15th row and duplicate data in the 16th row. You can see the unique data in the formula’s output range.

4.2. Remove Duplicate Names Using COUNTIF Function

In the helper column, enter the formula below and use the Fill Handle feature to AutoFill the lower cells.

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

Watch the video below on extracting the unique names by applying a Filter in the data table.

The following steps were covered in the video.

Select any cell of the data table.
Press Ctrl + Shift + L to apply Filter on it.
Select the drop-down icon beside the Helper column and uncheck 2 and 3.
Click OK.

You will see only the unique names after this operation.

However, the Filter command doesn’t remove the duplicate names; it just hides them. To permanently remove the duplicate names, follow the video below.

In the video, the following tasks were done.

Select the drop-down icon beside the Helper column. Unchecked 1, and click OK.
The duplicate names will be filtered.
Delete these rows with duplicate names and press Ctrl + Shift + L.

Now, you will see the unique names only.

Method 5 – Using the Power Query Editor

Steps:

To open the Power Query Editor, convert your dataset to a table.
Select the table and go to Data >> From Table/Range.

In the Power Query window, you will see the data table appear. It is accessible to various features to analyze.

The following steps were shown in the video.

Click on the first column name (Names).
Hold the Shift key and click on the last column name (Target Acquired).
Select Remove Rows >> Remove Duplicates.
You will see only unique data in the Power Query Editor.

You can also remove all the Duplicate Names and data in a row from the drop down options shown in the above picture.

Select the Close & Load command from the Power Query ribbon. This will automatically import the data table to a new sheet.

If you want to keep the unique names only and remove other duplicate names, right-click on the heading of the Names column and select Remove Duplicates.

You will see the changed query with the unique names only. Using the Close & Load command, you can load it in a new sheet.

Advantages: Using the Power Query Editor to remove duplicate names allows us to update the table dynamically. Say you add one duplicate and one unique data point in the table, as shown in the following image.

To update the data table, refresh it. Please follow the video below to understand this topic.

We covered the following steps in the video.

Place the cursor anywhere on the table extracted from the Power Query Editor.
Right-click and select the Refresh command from the Context Menu.

Your table from the Power Query Editor can be updated with the non-duplicate values.

Method 6 – Using VBA

Steps:

6.1. Removing Duplicates from Fixed Cell Range

To open the VBA window, press Alt + F11.

The VBA window will appear.
Now, select Insert >> Module. This command will open a VBA module.

Sub RemoveFromFixedRange()
Range("B4:E14").RemoveDuplicates Columns:=1
End Sub

Go back to your sheet and select Developer >> Macro or press Alt + F8.
Select the Macro named RemoveFromFixedRange and click OK.
Watch the video below to better understand this matter.

After running the Macro, you will see only duplicate names with their first corresponding rows.

6.2. Removing Duplicates from Selected Cell Range

Enter the code below into a new Module:

Sub RemoveDuplicatesSelectedRange()
Dim mn_input_range As Range
Set mn_input_range = Selection
mn_input_range.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Code Breakdown

First, a sub-procedure named RemoveDuplicatesSelectedRange() is initiated.
Next, we declared a variable mn_input_range as Range.
Following that, we set the input range variable to Selection.
After that, we used the RemoveDuplicates Method to remove the duplicates from the selected range.

Run the code.

Follow the video below to see how to remove duplicates from a selected range.

If you want to keep the data in the first 4 rows and remove the duplicate names from the 5th row of the dataset.

Select the range B9:E14.
Run the Macro and remove the duplicate names from the 5th row of our dataset.

We can also introduce a Button to make our task easier.

Select Developer >> Insert >> Button from the Form Controls
Create the button by dragging the mouse and give it a name.

Right-click on the button and select Assign Macro from the Context Menu. You can also assign a Macro before giving the button name.

A dialog box will pop up.
Assign the macro you want to run with the button click and click OK.

Watch the following video to learn how to run the Macro to remove duplicate names using a button click.

How to Highlight Duplicate Names in Excel

If you want to highlight duplicate names, go through the following steps rather than removing them.

Select the column or range where your duplicate names will be highlighted.
Select Conditional Formatting >> New Rule.

A dialog box will pop up. Select ‘Use a formula to determine which cells to format’ and copy the formula below to set the rule.

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

Select a fill color that will highlight the duplicate names and click OK.

A preview will appear showing how the formatted cells will look like. Just click OK again.

The duplicate names will be highlighted in the data table. Thus, you can apply Conditional Formatting to highlight duplicate values.

How to Keep Duplicate Rows in Excel

Open the Power Query Editor with the data table. Method 5 shows how to open a Power Query Editor. Follow the video below to see how to keep duplicate rows in the Power Query Editor.

We showed the following steps in the video.

Select the first column heading, hold the Shift key, and select the last column heading.
Select Home >> Keep Rows >> Keep Duplicates.
You will see the Duplicate Rows.