How to Remove Duplicates in Excel (9 Simple Ways)

Removing duplicates in Excel means keeping unique values and erasing the multiple instances.
In this Excel tutorial, you will learn how to remove duplicates in Excel with different useful tools and functions.

Multiple instances of duplicate values and rows occur while performing multiple tasks with very large worksheets or consolidating several sheets into an extensive one. You may also enter information multiple times mistakenly into a dataset. So, you need to erase them once you find those duplicates.

Suppose we have a dataset containing Employee ID, Employee Name, Joining Year, and Salary. There are some duplicate rows. Now we will delete duplicate rows using the Remove Duplicates command.

This Excel tutorial covers:

• Use of the Remove Duplicates command for single and multiple columns
• Erase of duplicate rows from a Table with the Remove Duplicates command
• Application of Advanced filter to remove duplicates
• Use of UNIQUE function to bring out only unique values
• Removal of duplicates but keeping 1st instances with IF-COUNTIF formula
• Application of Excel formula and Filter tool to remove duplicates
• Removal of duplicates based on criteria with the Advanced Filter tool
• Use of Power Query for removing duplications
• Application of VBA Macro to remove duplicates from any selection.

1. Using Remove Duplicates Command to Erase Duplicates in Excel

We will remove duplicates from a single or multiple columns using the Remove Duplicates command. The Remove Duplicates command is the built-in veteran Excel tool to erase duplicate instances. It can be useful for removing duplicates from both single and multiple columns.

1.1. From a Single Column

We will remove duplicates from a column using the Remove Duplicates command. As you can see in the image below, we have a list of Employee Names. First, we will find duplicates and remove them with the Remove Duplicates command.

• Initially, we can find and highlight duplicates with the Conditional Formatting tool.

• Then, select the range B6:B19 and click as follows: Data => Data Tools => Remove Duplicates.

• The Remove Duplicates dialog box will appear. Then, check Employee Name => OK.

• A pop-up dialog box will be visible saying 5 duplicate values found and removed. Then hit the OK

• Therefore, we obtain all unique values removing duplicates from a list.

Keyboard Shortcut to Remove Duplicates

You can also use the keyboard shortcut to remove duplicates in Excel.

• Press the Alt + A + M keys sequentially and you will get the Remove Duplicates dialog box.
• Then, check Employee Name => OK.
• Finally, you will obtain a list of unique values.

1.2. Across Multiple Columns

Now we will show you how to remove duplicates based on two or multiple columns using Excel’s Remove Duplicates command. First, we will highlight duplicate rows with the Conditional Formatting tool. As you can see from the below image, the 6th, 9th, 10th,12th, 18th, and 19th rows are duplicates. We will remove these duplicate rows based on Employee ID and Employee Name with the Remove Duplicates command.

• Next, select the range B6:E19 and go to Data => Data Tools => Remove Duplicates.
• Thus, The Remove Duplicates dialog box will appear. Then, Check Employee ID and Employee Name => OK.

• Therefore, we’ll get unique rows removing duplicate rows.

2. Removing Duplicate Rows From Excel Table

Now you will learn to remove duplicate rows from Excel Table using the Remove Duplicates command. The Remove Duplicates command is also visible in the Table Design tab when you select the table. Likewise, we have a dataset in a table format and we will erase duplicates by applying the Remove Duplicate tool here.

• First, select the table and click sequentially: Table Design => Remove Duplicates.
• Thus, The Remove Duplicates dialog box will appear.
• Then, We will remove these duplicate rows based on the Employee ID and Employee Name column which means if these two parameters are similar then the rows will be considered duplicates. So, check Employee ID and Employee Name => OK.

• As a result, all the duplicate rows will be removed from the table.
• The Employee ID containing A124 & Employee Name with Morris King has been removed from the 2nd instance.

3. Removing Duplicate Rows Based on Multiple Excel Columns

In this section, we will remove duplicate rows based on two or multiple columns using the Excel Advanced Filter tool.  The Advanced Filter tool in Excel is used to find data that meets multiple complex criteria.

• Initially, select the B6:E19 range and click as follows: Data => Sort & Filter => Advanced.
• Then, an Advanced Filter dialog box will appear.
• Next, select Copy to another location from the Action field.
• Further, Select List range $B$5:$E$19, and output location $B$23.
• Also, check the Unique records only option => OK.

• Therefore, all the filtered unique data will show in the B23:E33 range.

4. Applying Excel UNIQUE Function to Remove Duplication

By applying the UNIQUE function, you can simply get unique values and remove duplicates in Excel. The UNIQUE function keeps only 1st instance and ignores the rest of a dataset. It returns with an array format.

• Insert the following UNIQUE formula in the B6 cell.

• And you will get the unique names in an array format.

5. Removing Duplicates in Excel But Keeping 1st Instance Only

You can keep the 1st instance only and remove duplicates by using the IF-COUNTIF together in an Excel formula. The COUNTIF function counts the number of instances and the IF function returns the first value only ignoring 2nd or 3rd instances. Keep in mind that this formula doesn’t return array format, so you must use the Fill Handle tool for an entire column.

• By applying the following IF-COUNTIF formula, we obtain the Morris King in the D6 cell.

• Use the Fill Handle tool to copy the formula in the adjacent cells.

6. Using Excel Formula and Filter Tool to Remove Duplicates

Now we will show you how to remove duplicates with Excel formula and Filter tool combined. To do so, first, we will find duplicate rows with the IF-COUNTIFS formula. Then we will use the Filter tool to display the duplicate rows only. Now, the Visible cells only option from the Go To Special dialog box will be used to remove duplicate rows.

• By using the following IF-COUNTIFS formula in the F6 cell, we’ll obtain the output text Unique.

• Drag down the Fill Handle tool to identify the unique and duplicate rows.

• Now, we will find out the duplicate rows only with the Filter tool.
• To do so, select the B5:F5 range and click as follows: Data => Sort & Filter => Filter.

• Next, click on the Duplicate Row? dropdown => check Duplicate row => OK.

• Now we need to select only duplicate rows.
• So, select sequentially: Home => Editing => Find & Select => Go to Special.
• Thus, a Go To Special dialog box shows up.
• Select the Visible cells only option => OK.

• As a result, all the visible cells will be selected. Now our job is to delete them.
• To delete duplicate rows from the Context menu list, Right-click on the Mouse => Delete Row => OK.

• Therefore, you will see no data in the spreadsheet.
• Now click on the Filter drop-down => check Unique => OK.

• Finally, all the unique rows will appear.

7. Removing Duplicates Based on Criteria in Excel

With the Advanced Filter tool, you can remove duplicates based on criteria in Excel. Consider a situation when you are likely to import products from some vendors and there are some duplicate rows. Here we’ll find the duplicates based on 2 criteria- The vendor name will be Believe Farmers & the price will be over $15000.

• So, select the B6:F14 range and click as follows: Data => Sort & Filter => Advanced.
• Then, an Advanced Filter dialog box will appear.
• Next, select Copy to another location from the Action field.
• After that, Select List range $B$5:$E$19, criteria range $E$16:$F$17, and output location $B$19.
• Also, check the Unique records only option => OK.

• As a result, all unique rows containing Believe Farmers and prices over $15000 will be brought out.

8. Apply VBA Macro to Remove Duplicates in Excel

Now we will make a VBA Macro that is applicable anywhere in the worksheet. For a large dataset, it is very useful and saves time. However, you can develop VBA code to apply multiple datasets, worksheets, or workbooks at the same time. It will remove duplicates automatically.

• Write the following VBA code in the Module and save the Macro.

Sub Remove_Duplicates_in_Excel()
Dim inp As Range
Set inp = Application.InputBox("Select input range:", Type:=8)
If WorksheetFunction.CountA(inp) > 0 Then
    inp.RemoveDuplicates Columns:=Array(1), Header:=xlNo
Else
    MsgBox "No Data Selected."
End If
End Sub

• Now select as follows sequentially: Developer => Macros => Remove_Duplicates_in_Excel => Run.
• Select the input range and get the output after removing duplicates.

9. Using Excel Power Query Tool to Remove Duplicates

You can also use the Power Query tool to remove duplicates in Excel. Power Query allows to import data from any external or internal sources. But in this approach, you need to perform several steps. Please follow the steps carefully.

• First, select B5:E19 range and click sequentially, Data => From Table/Range.

• Then it will open with a Power Query Editor.
• Next, we will find duplicates based on Employee ID. So, Right-Click on the 1st column and select the Remove Duplicates command.

• Thus, by removing duplicates, all the unique values will show.
• After that, select Home => Close & Load drop-down => Close & Load To.

• Thus, the Import Data dialog box will appear.
• Since, we are likely to import data into a table format in the existing worksheet, select Table => Existing worksheet => $B$24 => OK.

• Therefore, all the unique rows will be inserted into the worksheet erasing the duplicate rows.

How to Undo Remove Duplicates in Excel?

Suppose, we removed the duplicates. But it is no longer needed. So, we need to undo the remove duplicates operation in Excel.

• Just simply click the Undo command from the Quick Access Tool.

• Therefore, all the data including duplicates will be shown.

How to Hide Duplicate Values Instead of Removing Them in Excel?

Previously, we learned to remove duplicates. But in this section, we will learn to hide duplicate values instead of removing them in Excel with the Filter tool.

• First, we must figure out unique and duplicate rows using the IF-COUNTIFS formula like method-6.

• Select the B5:F5 range and select as follows: Data => Sort & Filter => Filter.
• Just click on the Filter drop-down => Check Unique => OK.

• As a result, unique values will show hiding duplicates.

What Can be Possible Reasons If You Can’t Remove Duplicates in Excel Properly?

Remove Duplicates tool does not work for two lists. Rather, it treats the cells as duplicate rows. Look at the following image, there are 2 lists of Sales Region.

Issue:

When we select the range B6:D14 and apply the Remove Duplicates command, it says No duplicate values found with a pop-up message. But the region of Ohio is clearly present in both lists. So, the Remove Duplicate tool is not sufficient to find all duplicate values properly here, right?

Solution:

• So, we will make a list in the F column copying data from Sales Regions 1 and 2.

• Now, select the F6:F23 range.
• Then, press the Alt + A + M keys to apply the Remove Duplicates command.

• Thus, the Remove Duplicates dialog box shows up.
• Next, check Sales Regions => OK.

• As a result, we obtain every Sales Region only once.

Download Practice Workbook

Download the free workbook for details and practice yourself.

Conclusion

Throughout the article, we explained several tips and tricks to remove duplicates in Excel. You can use the Remove Duplicates command to erase duplicate values from single and multiple columns. Also, you can remove duplicate rows from a Table with the Remove Duplicates command from the Table Design tab. Application of the Advanced filter is also useful for removing duplicates. It also applicable with multiple criteria. The UNIQUE function extracts unique values whereas, the IF-COUNTIF formula finds out the first instances leaving multiple instances blank. On the other hand, you can apply the Excel formula and Filter tool to remove duplicates. Power Query is useful for removing duplications by importing data from any external or internal sources. Finally, the application of VBA Macro facilitates you to remove duplicates from any selection.

Remove Duplicates in Excel: Knowledge Hub

• How to delete duplicates in excel but keep one
• Remove both duplicates in excel
• How to remove duplicate names in excel
• Excel remove duplicates rows except 1st occurance
• Hide duplicates in excel
• How to remove duplicates in excel using vlookup
• Formula to automatically remove duplicates in excel
• Excel remove duplicate rows
• Excel remove duplicate rows based on one column
• Excel remove duplicates from column
• Excel hide duplicate rows based on one column

<< Go Back to Duplicates in Excel | Learn Excel