How to Remove Duplicates in Excel [14+ Different Methods]

One of the most obvious tasks as part of the data cleaning process is to remove duplicates. Well, there are different types of cases that require different types of approaches for removing duplicates. Considering all the different scenarios, I’ve come up with all possible solutions to remove duplicates in Excel.

I’m going to use a 3-column data table to show you to remove the duplicates. It has customer names in the Customer Name column. Their corresponding area is listed in the Region column. And the amount of money each of the customers ordered is recorded in the Order Amount column.

If an entire row matches with another row, then I’m gonna call that a duplicate. Otherwise, if any of the columns has a different value, then I’m gonna leave it as a unique value.

In the picture below, the rows marked by sky blue color are unique. Though the customer names and the order amount is the same. Their region is different from one another which makes them unique. And the rows colored reddish are duplicates.

Remove Duplicates in Excel without Using Any Formula

First, I will show you removing duplicates without using any kind of formula. So let’s begin.

For Large Data Using Remove Duplicates Command (Static and Case-Insensitive)

Let’s say you have a very large file that contains huge amounts of rows and columns. In such a case, if very difficult to manually select rows and columns and later remove the duplicates.

To lessen the hassles while removing duplicates for a large set of data, Excel has come up with a command named Remove Duplicates.

All you need to do is select the column names and then you are ready to go. Excel will delete the duplicate rows from your worksheet.

1. Click on any cell of your datasheet.
2. Then go to the Data tab > Data Tools group.
3. In the Data Tools group, find the Remove Duplicates command. Then, just click on it. 
4. Select ‘My data has headers’ if you have any.
5. Then, check the column names, from where you want to remove the duplicates.
   I want to remove duplicates from my entire datasheet. So, I’ve selected all the columns. You can select one, two, or any number of columns.
6. After selecting the columns, click OK.

The Remove Duplicates command clears all the duplicates from the selected column names. But it keeps the first instance of the duplicates. This means, it just removes all the duplicate copies.

Note: This is a static process of removing duplicates. This means if any changes occur in the source data table, it doesn’t update the output.

Use the Advanced Filter (Static and Case-Insensitive)

The Advanced Filter allows us to filter out all the unique rows in the midst of a mixture of duplicate as well as unique values.

Suppose, you have two identical rows. Apply the Advanced Filter to filter out the first row and the second one will be counted as a duplicate. Now, use the Advanced Filter feature to remove all the duplicate values but keep the first instance of data.

To remove duplicates using the Advanced filter, go through steps below:

1. Select the Data tab > Sort & Filter group > Advanced command.
2. Under the Action part, two options are available: Filter the list, in place & Copy to another location.
   However, I want to replace the source data with the filtered data. So I’m selecting ‘Filter the list, in place’.
3. Now, insert your data range beside ‘List Range’.
   Or,  select the data range manually by clicking on the upper arrow icon across the ‘List Range’.
4. Now check ‘Unique records only’ to filter out all the duplicates and hit OK.
   All the duplicate rows will be filtered out. Cause Advanced Filter feature will hide the duplicate rows.
5. Select the Data tab > Sort & Filter group > Clear command.
   Or, press CTRL + Z.

Apply Power Query (Case-Sensitive & Semi-Dynamic)

So far all the methods I’ve discussed are static. As you need to update your source datasheet too frequently, you have to repeat the whole process of removing duplicates. Here comes the benefits of using the Power Query.

To apply Power Query, follow these steps to remove duplicates:

1. Go to the Data tab > Get & Transform Data group > From Table/Range option.
   
2. Select the data range in the Create Table dialog box.
3. Check ‘My table has headers’ if you have any and click OK.
   To remove duplicates from the entire table,
4. Click on the Table drop-down icon at the top-left corner of your data table.
5. Then, select Remove Duplicates from the drop-down menu.
   
6. In the Power Query Editor window, right-click on the column header and select Remove Duplicates from the context menu. It will remove all the duplicates from the data table right away. However, rows 2 & 3 both contain the same data with different letter cases.
7. As their letter cases are different, the Power Query considers them unique. [Power Query is a case-sensitive tool.]To avoid this,
8. Select the column. Then, follow: Transform tab > Text Column group > Format drop-down > Capitalize Each Word.
   
   
9. Hit the Table drop-down icon > Remove Duplicates command.
   Lastly, it removes all duplicates even when letter cases are not similar.
   Now, you can make changes using PROPERTIES & APPLIED STEPS drop-downs in the Query Setting dialog box.
10. Go to the Home tab > Close & Load command.
    It will close the Power Query Editor window and import the data table into a new worksheet.
    
    
11. Click on the Refresh All button to update the output. 

Remove Duplicates in Excel Using Dynamic Formula

Removing duplicates using formulas has some benefits over methods that don’t require any formula. Write a formula and apply the formula in a range of cells. If we input any duplicate rows, they will vanish automatically.

Use a Helper Column [Supports All Versions of Excel]

This formula requires a helper column to perform its task. The helper column is basically the concatenation of every cell in a row. To create the helper column either CONCAT or CONCATENATE function can be used. Here, I used the CONCAT function.

The main dynamic function is made up of IFERROR, INDEX, SMALL, IF, COUNTIF, ROW, & ROWS functions.

Feature of this Formula

• Supports all versions of Microsoft Excel.
• A dynamic formula, that remove duplicates automatically from a specified range.
• Also remove duplicates but keep the first instance of the duplicates.

Creating Helper Column

1. Create a helper column beside dataset.
2. Then, insert the formula in the first row: =CONCAT(B2:D2)
3. Next, copy it down.

It will merge all cells of each column respectively.

Main Formula

Use the formula below in a blank cell beside the main dataset. Then, press ENTER. As the formula is dynamic, it will populate by itself.

=IFERROR(INDEX(B2:D16,SMALL(IF(COUNTIF(A2:A16,A2:A16)=1,ROW(A2:A16)-ROWS(A1:A1),""),
ROW(A2:A16)-ROWS(A1:A1)),{1,2,3}),"")

Formula Explanation

This formula combines INDEX, SMALL, IF, ROW, COUNTIF, and IFERROR functions to extract unique rows from B2:D16 based on the criterion that each value in column A appears only once. The COUNTIF(A2:A16, A2:A16)=1 checks for uniqueness. IF returns an array of row numbers for unique values. SMALL retrieves the smallest values (unique rows), and INDEX extracts corresponding rows from B2:D16. The {1,2,3} array within INDEX ensures extraction of the first three columns. IFERROR handles errors, returning an empty string if any occur.

Helper Column but Keep the First Instance [Supports Excel for MS 365]

This formula also requires a helper column to work. This time I used the CONCATENATE function to create the helper column. The main formula consists of the FILTER and the COUNTIF functions.

Feature of this Formula

• It’s a dynamic formula.
• It can remove duplicates automatically from a specified range.
• It can remove all the duplicates.
• It can remove also duplicates but keep the first instance of the duplicates.

Creating Helper Column

Use the following formula to create a helper column beside the main dataset.

=CONCATENATE(B2:B16,C2:C16,D2:D16)

Here, B2:B16, C2:C16, & D2:D16 are the ranges of the first, second, and third columns of my data table respectively.

Clear Duplicates but Keep the First Instance

Use the formula below in a blank cell beside the main dataset. After that, press ENTER. As the formula is dynamic, it will populate automatically by itself.

Main Formula

=FILTER(B2:D16,COUNTIF(A2#,A2#)=1)

Formula Explanation

This formula utilizes COUNTIF(A2#, A2#)=1 to remove duplicates in A2:A16, retaining only the first instance of each value. Using 2 instead of 1 would delete all instances of duplicates.

Clear All Duplicates

Use the formula below in a blank cell beside the main dataset. After that, press ENTER. As the formula is dynamic, it will populate automatically by itself.

Main Formula

=FILTER(B2:D16,COUNTIF(A2#,A2#)=2)

Formula Explanation

This formula, COUNTIF(A2#, A2#)=2, removes all instances of duplicates in A2:A16. Inserting 1 instead of 2 retains the first instance of each duplicate value while removing the subsequent ones.

Without Helper Column [Supports Excel for MS 365]

This formula consists of the UNIQUE function. This function is available only in

1. Excel for Microsoft 365 for Windows
2. Excel for Microsoft 365 for Mac
3. Excel for the web
4. Excel 2021

Features of this Formula

• It’s a dynamic formula.
• Automatically removes duplicates in the specified range.
• It can remove all the duplicates.
• It can remove duplicates but keep the first instance.

Clear Duplicates but Keep the First Instance

Syntax

=UNIQUE(array,[by_col],[exactly_once])

Formula

=UNIQUE(A2:C16,FALSE,FALSE)

Function Explanation

Use A2:C16 as the data range. For unique rows, enter FALSE; for unique columns, use TRUE. FALSE removes duplicates, retaining only the first instance of each duplicate value.

Clear all Duplicates

Syntax

=UNIQUE(array,[by_col],[exactly_once])

Formula

=UNIQUE(A2:C16,FALSE,TRUE)

Function Explanation

In A2:C16, use FALSE for unique rows or TRUE for unique columns. TRUE removes all instances of duplicate values, ensuring only unique rows are retained.

Use VLOOKUP Function [Supports All Versions of Excel]

The lookup formula used in this method includes the IF, ISERROR, & VLOOKUP functions.

Features of this Formula

• It supports all versions of Excel.
• Using this formula you can remove every instance of all duplicates between two data tables.

Formula

=IF(ISERROR(VLOOKUP(E2&F2&G2,$A$2:$A$16&$B$2:$B$16&$C$2:$C$16,1,FALSE)),"Unique","Duplicate")

Formula Explanation

This formula uses VLOOKUP to check if the concatenation of E2, F2, and G2 exists in the virtual data table created by $A$2:$A$16&$B$2:$B$16&$C$2:$C$16. If the value is not found (ISERROR returns TRUE), it’s labeled “Unique”; otherwise, it’s labeled “Duplicate”.

I’m using two sets of data. Both contain 3 columns each. I want to remove all the rows that are common between two-column data sets. To do that, go through the steps below:

1. Select the entire Filter column. Then press CTRL + SHIFT + L to apply the AutoFilter.
   you can follow the Data tab > Sort & Filter group > Filter command.
   This will also apply the AutoFilter to the Filter column.
2. After that, extend the drop-down menu at the bottom-right corner of the Filter column header.
3. Select Duplicate from the list and click OK to filter only the duplicate value.
   
   
4. Now select all rows of the filtered duplicate rows.
5. Press CTRL + – to delete all the duplicate rows.
   
   
6. Now select the Data tab > Soft & Filter group > Clear command to remove the AutoFilter from the selection area.

Only the unique values are left.

Apply XLOOKUP Function [Supports Excel for MS 365]

This duplicate removal formula is composed of the XLOOKUP function. Using this formula, you can remove every instance of all duplicate values. Unfortunately, you can use the XLOOKUP function only in Excel for Microsoft 365 and later versions.

About the Setup

I’m using two sets of data. Both contain 3 columns each. I want to remove all the rows that are common between two-column data sets.

Formula

=XLOOKUP(E2&F2&G2,$A$2:$A$16&$B$2:$B$16&$C$2:$C$16,$A$2:$C$16,"",0)

Formula Explanation

This formula utilizes XLOOKUP to search for the concatenation of E2, F2, and G2 in the virtual data table $A$2:$A$16&$B$2:$B$16&$C$2:$C$16. If found, it returns the corresponding row from $A$2:$C$16; otherwise, it returns an empty string. The “0” indicates an exact match.

The above formula will return only the duplicate records. To filter out duplicate records, follow these steps below:

1. Select the range I1:K11.
   Here I’ve selected one extra row above the required selection range. Because there’s no column header here. Without a column header, the AutoFilter considers the first row of the data table as a table header.
2. Press CTRL + SHIFT + L to apply the AutoFilter.
   Or, just select the Data tab > Sort & Filter group > Filter command.
3. Expand the AutoFilter drop-down and uncheck Blanks only.
4. Then click OK.
   
   
5. Select all the filtered rows and press CTRL + – to delete them all.
   
   
6. Now go to the Data tab > Sort & Filter group > Clear command.

All instances of every duplicate value have been removed from the two data tables.

Insert COUNTIF Function but Keep First Instance

To remove duplicates using the COUNTIF function, you need a helper column.

To create a helper column, use this formula:

=CONCATENATE(B2:B16,C2:C16,D2:D16)

Formula Explanation

• B2:B16: First column range of the data table.
• C2:C16: Second column range of the data table.
• D2:D16: Third column range of the data table.

Now create a blank column beside your dataset. Then, count every instance of all the records using the following formula of the COUNTIF function:

=COUNTIF($A$2:A2,A2)

Formula Explanation

• $A$2:A2: An expandable range of the first column of my dataset.
• A2: The first cell of the first column of my dataset. This works as a criterion.

Now select the entire count column and press CTRL + SHIFT + L to apply the AutoFilter. After that, expand the AutoFilter drop-down from the lower-right corner of the Count column header. Then, select 1 from the list and click OK.

You will get every distant record of your dataset. You can copy and paste this filtered result to a different location.

Remove Duplicates with Conditions in Excel

Replacing Duplicates with Blank Cells without Shifting Cells

Remove Duplicates but Keep Every Distinct Record

This formula will remove duplicates but keep the first instance of every duplicate value. In addition to that, it will replace all the duplicate copies with blanks. Thus, the duplicates will be deleted without shifting cells, rows, or columns. The formula consists of the IF and COUNTIF functions.

Formula

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,A2,""))

Usage Guide

Apply the formula in cell D2. They drag down the formula using Fill Handle up to cell D16. Then again drag the formula to the right-next column using the Fill Handle again.

Formula Explanation

• COUNTIF(A$2:A2,A2)=1 returns every instance of all the records present in the datasheet.
• IF(COUNTIF(A$2:A2,A2)=1,A2,””) keeps the first instance of every record intact. It replaces other instances of the records with blanks.
• IF(A2=””,””,IF(COUNTIF(A$2:A2,A2)=1,A2,””)) leaves all the blank cells as they are. But if a cell is not blank, it handles the cell using IF(COUNTIF(A$2:A2,A2)=1,A2,””).

Remove All Instances of the Duplicates

This formula will remove all the duplicates from your datasheet completely. This formula is also made up of the IF and COUNTIF functions.

Formula

=IF(A2="", "", IF(COUNTIF(A$2:A$16,A2)>1,"",A2))

Usage Guide

Apply the formula in cell D2. They drag down the formula using Fill Handle up to cell D16. Then again drag the formula to the right-next column using the Fill Handle again.

Formula Explanation

• COUNTIF(A$2:A$16,A2)>1 returns only the unique records present in the datasheet.
• IF(COUNTIF(A$2:A$16,A2)>1,””,A2)) replaces all instances of the duplicate records with blanks.
• IF(A2=””, “”, IF(COUNTIF(A$2:A$16,A2)>1,””,A2)) leaves all the blank cells as they are. But if a cell is not blank, it handles the cell using IF(COUNTIF(A$2:A$16,A2)>1,””,A2)).

Apply Conditional Formatting in a Single Column

We can also remove duplicate values out of a single column with the help of the Conditional Formatting feature in Excel. To apply Conditional Formatting in single column, follow these steps:

1. Select a column range first.
2. Then go to the Home tab > Conditional Formatting drop-down menu > Highlight Cells Rules menu > Duplicate Values command.
   A small dialog box appears that allows you to pick a color to highlight all the duplicates.
3. Choose a color combination and click OK.
4. Now select the column again but this time with the column header too.
5. Press CTRL + SHIFT + L to apply the AutoFilter.
   
   
6. Expand the AutoFilter drop-down at the lower-right corner of the column header name.
7. Select Filter by Color.
8. Then choose either Filer by Cell Color or Filter by Font Color. I’m choosing Filter by Cell Color.
   Now only the duplicate rows will be filtered out.
9. Select all the filtered results and press CTRL + – to delete them all.
   
   
10. Now navigate to the Data tab > Sort & Filter group > Clear command.

You will one only the Unique values based on one column that you’ve selected.

Remove Duplicates in Excel Using VBA

Use a VBA Code in a Specified Range

This piece of code can remove duplicates from the range A2:C16 based on columns 1,2, &3.

Sub Duplicate_Eraser()

Range("A2:C16").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

End Sub

Editable Line

Range("A2:C16").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

Just update the data range inside the Range function according to your datasheet. If you want to remove duplicates based on a single column, use just 1 instead of Array(1, 2, 3).

But if you want to remove duplicates based on multiple columns, update the Array function. For example, Array(2, 3) will remove duplicates based on columns 2 and 3 in a specified data range.  Array(5, 1, 3) will remove duplicates based on columns 1,3, &5. Here you don’t need to maintain the order.

Apply a VBA Code in a Pre-Selected Range

This piece of VBA code can remove duplicates from a pre-selected range. Here I’ve mentioned the word ‘Pre-Selected’ because you need to select your data range first. Then you can run the code. Otherwise, it won’t work.

Sub Duplicate_Eraser()

Dim gRng As Range
Set gRng = Selection
gRng.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

End Sub

Editable Line

gRng.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

Just update the column index numbers inside the Array function.

For example, Array(2, 3) will remove duplicates based on columns 2 and 3 in a specified data range.  Array(5, 1, 3) will remove duplicates based on columns 1,3, &5. Here you don’t need to maintain the order.

Write a VBA Code from an Excel Table

This code will remove duplicates from an Excel Table of the active worksheet.

Sub Duplicate_Eraser()

ActiveSheet.ListObjects("Table_Name").DataBodyRange.RemoveDuplicates Columns:=Array(1, 2, 3), _
Header:=xlYes

End Sub

Editable Lines

ActiveSheet.ListObjects("Table_Name")

First, update the Table_Name inside the ListObjects property.

RemoveDuplicates Columns:=Array(1, 2, 3)

Now update the column index numbers inside the Array function.

For example, Array(2, 3) will remove duplicates based on columns 2 and 3 in a specified data range.  Array(5, 1, 3) will remove duplicates based on columns 1,3, &5. Here you don’t need to maintain the order.

Shortcut to Remove Duplicates in Excel

Follow the steps to learn the shortcuts to remove duplicates in Excel:

1. At first, select any cell of your dataset.
2. Then press CTRL + A to select the entire data table.
3. Next, press and hold the ALT key.
4. After that, press A (Selects the Data tab) > and press M (Select the Remove Duplicates command).
   Now, the Remove Duplicates dialog box will appear.
5. Check ‘My data has headers’ if you have any.
6. Then choose the columns.
7. And hit OK.

This will remove all the duplicates from your datasheet. But it keeps the first instance of every duplicate record.

Remove Duplicates in Excel Not Working?

Addressing duplicate values in Excel can sometimes be challenging due to factors like invisible characters, cell formatting, decimal places, range selection, and formula accuracy. If you encounter difficulties in removing apparent duplicates, consider the following solutions:

• Presence of Spaces: Leading or trailing spaces in your data may hinder duplicate removal. Utilize the TRIM function to eliminate these spaces, ensuring a clean dataset. After space removal, attempt to remove duplicates again.
• Cell Format Issue: Suppose, you have inserted two numerical values in two different cells. Well, let me make it easier for you. You inserted 100 in two different cells. But you applied the Text cell format in one cell and the Number cell format in another cell. In this case, Excel will consider both of them Unique. Because their format is different from one another. If such things happen to you, just make sure you applied the right cell format. After that try again to remove the duplicates. Hope this might work this time.
• Different Decimal Places: Decimal fraction numbers with varying decimal places might be treated as distinct values. Standardize the number of decimal places for all numerical values to enable successful duplicate removal.
• Range Issue: If you insert a wrong range in your duplicate removing formula, it won’t return your desired result. So make sure you use the correct range while removing duplicates using formulas or VBA codes.
• Wrong Formula: Ensure the accuracy of your formulas. Incorrect usage of absolute and relative cell references can disrupt the duplicate removal process. Be conscious of using the right references to prevent unintended changes in cell ranges and to achieve effective duplicate removal.

Conclusion

I’ve discussed some effective techniques to find, highlight, and delete duplicates in Excel. I expect you’ve found this article useful. You can read more articles relating to Excel from the Blog page of our website. Have a fantastic workday!

Frequently Asked Questions (FAQs)

What is the formula to remove duplicates?

The formula to remove duplicates in Excel involves using the Remove Duplicates feature:

1. Highlight the column or range containing potential duplicates.
2. Navigate to the Data tab on the ribbon at the top.
3. Find and click on the Remove Duplicates option in the Data Tools group.
4. In the Remove Duplicates dialog box, select the columns where you want to identify and remove duplicates.
5. Click OK to execute the removal process.

This simple process utilizes the built-in functionality of Excel to efficiently identify and remove duplicate values from the specified columns. Adjust column selections as needed for your specific dataset.

Can Excel automatically remove duplicates?

Yes, Excel can automatically remove duplicates with the Remove Duplicates feature:

1. Highlight the column or range containing potential duplicates.
2. Navigate to the Data tab on the ribbon.
3. Find and click on Remove Duplicates in the Data Tools group.
4. In the Remove Duplicates dialog box, select the columns where you want to remove duplicates.
5. Click OK to automatically identify and remove duplicate values.

This built-in Excel feature streamlines the process, allowing users to effortlessly remove duplicate entries based on selected criteria. Adjust column selections as needed for your specific dataset.

How do you remove duplicates in Excel count?

To remove duplicates in Excel and count unique values:

1. Highlight the column or range containing potential duplicates.
2. Navigate to the Data tab on the ribbon.
3. Find and click on Remove Duplicates in the Data Tools group.
4. In the Remove Duplicates dialog box, select the columns where you want to remove duplicates.
5. Click OK to remove duplicates.

For counting unique values, use the COUNTA or COUNT functions on the cleaned dataset. This process ensures that duplicate values are removed from the specified columns, and you can subsequently count the remaining unique values using appropriate counting functions. Adjust column selections as needed for your specific dataset.