How To Remove Duplicate Names In Excel

7 Ways To Detect And Remove Duplicate Values In Microsoft Excel

Duplicate values in your data tin can be a big problem! It can atomic number 82 to substantial errors and over gauge your results.

But finding and removing them from your data is actually quite easy in Excel.

In this tutorial, we are going to look at 7 different methods to locate and remove duplicate values from your information.

Video Tutorial

What Is A Indistinguishable Value?

Indistinguishable values happen when the same value or set of values appear in your data.

For a given set of information yous can define duplicates in many different means.

In the higher up example, there is a unproblematic set of data with three columns for the Make, Model and Year for a list of cars.

The first paradigm highlights all the duplicates based only on the Make of the machine.
The second prototype highlights all the duplicates based on the Make and Model of the car. This results in one less duplicate.
The 2nd image highlights all the duplicates based on all columns in the table. This results in even less values being considered duplicates.

The results from duplicates based on a single column vs the unabridged tabular array tin can exist very different. Yous should always exist aware which version you want and what Excel is doing.

Notice And Remove Indistinguishable Values With The Remove Duplicates Control

Removing duplicate values in data is a very common chore. It's so common, there's a dedicated command to do it in the ribbon.

Select a cell inside the data which you want to remove duplicates from and go to the Data tab and click on the Remove Duplicates control.

Excel will then select the entire set of information and open upwardly the Remove Duplicates window.

You and then demand to tell Excel if the data contains column headers in the first row. If this is checked, and then the kickoff row of data will exist excluded when finding and removing duplicate values.
You tin then select which columns to use to determine duplicates. There are also handy Select All and Unselect All buttons above you can use if you've got a long list of columns in your data.

When y'all press OK, Excel will then remove all the duplicate values it finds and give you a summary count of how many values were removed and how many values remain.

This control will alter your data and then information technology's best to perform the command on a re-create of your data to retain the original data intact.

Find And Remove Duplicate Values With Avant-garde Filters

There is besides another way to go rid of whatsoever duplicate values in your data from the ribbon. This is possible from the avant-garde filters.

Select a jail cell inside the data and go to the Data tab and click on the Advanced filter command.

This volition open up the Advanced Filter window.

You can cull to either to Filter the list in place or Copy to another location. Filtering the listing in place will hide rows containing any duplicates while copying to another location will create a copy of the information.
Excel will approximate the range of information, but you tin can arrange it in the List range. The Criteria range can be left blank and the Re-create to field will need to be filled if the Copy to another location option was chosen.
Bank check the box for Unique records only.

Press OK and you volition eliminate the duplicate values.

Advanced filters can be a handy option for getting rid of your duplicate values and creating a copy of your data at the same time. But advanced filters volition simply exist able to perform this on the entire tabular array.

Observe And Remove Indistinguishable Values With A Pivot Table

Pin tables are merely for analyzing your data, right?

You tin actually use them to remove duplicate data as well!

Yous won't really be removing duplicate values from your data with this method, you volition exist using a pivot table to brandish only the unique values from the data set.

First, create a pivot tabular array based on your data. Select a cell inside your data or the unabridged range of information ➜ get to the Insert tab ➜ select PivotTable ➜ printing OK in the Create PivotTable dialog box.

With the new blank pivot tabular array add all fields into the Rows area of the pin table.

You will and then demand to change the layout of the resulting pivot tabular array so it's in a tabular format. With the pivot tabular array selected, go to the Design tab and select Report Layout. In that location are two options you volition need to change hither.

Select the Show in Tabular Form selection.
Select the Repeat All Particular Labels option.

Y'all volition also need to remove any subtotals from the pivot table. Go to the Design tab ➜ select Subtotals ➜ select Do Not Show Subtotals.

You now accept a pivot table that mimics a tabular set of information!

Pivot tables only list unique values for items in the Rows area, so this pin table will automatically remove any duplicates in your information.

Notice And Remove Duplicate Values With Ability Query

Power Query is all about information transformation, so you tin can exist sure it has the power to observe and remove duplicate values.

Select the table of values which you want to remove duplicates from ➜ go to the Data tab ➜ choose a From Table/Range query.

Remove Duplicates Based On Ane Or More Columns

With Ability Query, you can remove duplicates based on one or more columns in the table.

You need to select which columns to remove duplicates based on. You can hold Ctrl to select multiple columns.

Correct click on the selected cavalcade heading and choose Remove Duplicates from the menu.

You can also admission this control from the Home tab ➜ Remove Rows ➜ Remove Duplicates.

          = Table.Distinct(#"Previous Stride", {"Make", "Model"})

If y'all look at the formula that's created, it is using the Table.Singled-out part with the second parameter referencing which columns to use.

Remove Duplicates Based On The Unabridged Table

To remove duplicates based on the entire table, you could select all the columns in the tabular array then remove duplicates. But in that location is a faster method that doesn't crave selecting all the columns.

In that location is a push in the tiptop left corner of the information preview with a selection of commands that tin be applied to the entire table.

Click on the table button in the top left corner ➜ and so cull Remove Duplicates.

          = Tabular array.Distinct(#"Previous Step")

If y'all look at the formula that's created, it uses the same Table.Distinct function with no second parameter. Without the 2d parameter, the part volition act on the whole table.

Continue Duplicates Based On A Unmarried Column Or On The Unabridged Table

In Ability Query, there are too commands for keeping duplicates for selected columns or for the entire table.

Follow the same steps equally removing duplicates, but use the Keep Rows ➜ Go along Duplicates command instead. This volition show you all the data that has a duplicate value.

Find And Remove Duplicate Values Using A Formula

You lot tin utilise a formula to help you lot find duplicate values in your data.

Beginning y'all volition demand to add a helper column that combines the data from any columns which y'all desire to base your indistinguishable definition on.

          = [@Make] & [@Model] & [@Year]

The to a higher place formula volition concatenate all 3 columns into a unmarried cavalcade. Information technology uses the ampersand operator to join each column.

          = TEXTJOIN("", FALSE , CarList[@[Make]:[Yr]])

If you lot take a long list of columns to combine, you can utilize the above formula instead. This fashion yous can just reference all the columns equally a unmarried range.

You will then demand to add some other column to count the duplicate values. This will be used later to filter out rows of data that appear more than once.

          = COUNTIFS($E$3:E3, E3)

Re-create the higher up formula down the column and information technology volition count the number of times the electric current value appears in the list of values above.

If the count is one then it's the get-go time the value is appearing in the data and you will keep this in your set of unique values. If the count is two or more than so the value has already appeared in the data and it is a duplicate value which can exist removed.

Add filters to your data list.

Go to the Data tab and select the Filter command.
Use the keyboard shortcut Ctrl + Shift + L.

Now you can filter on the Count column. Filtering on 1 will produce all the unique values and remove whatsoever duplicates.

You can then select the visible cells from the resulting filter to copy and paste elsewhere. Employ the keyboard shortcut Alt + ; to select just the visible cells.

Find And Remove Duplicate Values With Conditional Formatting

With conditional formatting, there's a way to highlight duplicate values in your data.

Merely like the formula method, yous need to add a helper column that combines the data from columns. The conditional formatting doesn't work with data across rows, so you'll need this combined column if you desire to observe duplicates based on more one column.

And so you need to select the column of combined data.

To create the conditional formatting, go to the Abode tab ➜ select Provisional Formatting ➜ Highlight Cells Rules ➜ Duplicate Values.

This will open up the provisional formatting Duplicate Values window.

Y'all tin can select to either highlight Duplicate or Unique values.
You lot can also cull from a choice of predefined cell formats to highlight the values or create your ain custom format.

Warning: The previous methods to notice and remove duplicates considers the first occurrence of a value as a indistinguishable and will leave it intact. However, this method will highlight the first occurrence and volition not brand whatever distinction.

With the values highlighted, y'all can now filter on either the duplicate or unique values with the filter by colour option. Make sure to add filters to your data. Go to the Information tab and select the Filter command or use the keyboard shortcut Ctrl + Shift + L.

Click on the filter toggle.
Select Filter past Color in the card.
Filter on the color used in the conditional formatting to select duplicate values or filter on No Fill up to select unique values.

You tin then select simply the visible cells with the keyboard shortcut Alt + ;.

Detect And Remove Duplicate Values Using VBA

At that place is a built in control in VBA for removing duplicates within list objects.

          Sub RemoveDuplicates()          Dim DuplicateValues As Range          Fix DuplicateValues = ActiveSheet.ListObjects("CarList").Range          DuplicateValues.RemoveDuplicates Columns:=Array(i, two, 3), Header:=xlYes          Cease Sub

The above procedure volition remove duplicates from an Excel table named CarList.

          Columns:=Assortment(i, two, 3)

The above role of the process volition set which columns to base indistinguishable detection on. In this instance it will exist on the unabridged table since all 3 columns are listed.

          Header:=xlYes

The above part of the procedure tells Excel the first row in our list contains column headings.

You will want to create a copy of your data before running this VBA lawmaking, equally it tin can't be undone after the lawmaking runs.

Conclusions

Duplicate values in your data can be a big obstacle to a clean data set.

Thankfully, there are many options in Excel to easily remove those pesky indistinguishable values.

So, what'due south your become to method to remove duplicates?

Most the Writer

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.