# 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 the duplicates. Well, there are different types of cases that require different types of approaches for removing the duplicates. Considering all the different scenarios, I’ve come up with all possible solutions to remove duplicates in Excel.

## Introduction to the Dataset

I’m going to use a 3-column data table to show you 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.

Before getting started, one important thing that I’d like to focus on. That is defining the duplicates first.

For example, 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.

## 1. Remove Duplicates in Excel without Using Any Formula

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

### 1.1 Remove Duplicates in Excel 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.

**Guides to Follow**

**Step_1: **Click on any cell of your datasheet.

**Step_2:** Then go to the **Data** tab **⋙ Data** **Tools** group.

**Step_3:** In the **Data Tools** group, find the **Remove Duplicates** command. Then just click on it.

**Step_4:** Select ‘**My data has headers**’ if you have any.

**Step_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.

**Step_6:** After selecting the columns, click **OK**.

**Final Result**

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.

### 1.2 Erase Duplicates but Keep the First Instance Using 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. Applying the **Advanced Filter**, only the first row will be filtered out. The second one will be counted as a duplicate. This is how you can use the **Advanced Filter** feature to remove all the duplicate values but keep the first instance of data.

**Now Follow the Guide**

**Step_1: **Select the **Data** tab **⋙ Sort & Filter** group **⋙ Advanced** command.

**Step_2:** Under the **Action** part, two options are available.

**Filter the list, in place.**

Selecting this option will filter in all the unique values and keep them in the current location of the data.

**Copy to another location.**

Selecting this you will have options to choose another destination to keep the filtered data.

However, I want to replace the source data with the filtered data. So I’m selecting ‘**Filter the list, in place**’.

**Step_3:** Now insert your data range beside ‘**List Range**’. Or you can select the data range manually by clicking on the upper arrow icon across the ‘**List Range**’.

**Step_4:** Now check ‘**Unique records only**’ to filter out all the duplicates.

**Step_5:** After that hit **OK**.

**Final Result**

All the duplicate rows will be filtered out. This means the duplicate rows will be hidden. If you look at the row numbers, you will see some of the row numbers are missing. This is because the **Advanced Filter** feature has hidden them.

Going back to the previous state of the data is quite easy.

Just select the **Data** tab **⋙ Sort & Filter** group **⋙ Clear** command.

You can also press **CTRL + Z** instead.

### 1.3 Remove Duplicates Using Power Query (Case-Sensitive & Semi-Dynamic)

So far all the methods I’ve discussed are static. This means when you change the source data, the output doesn’t update.

If 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**. Well, the **Power Query** creates a semi-dynamic system for removing duplicates.

It’s semi-dynamic because you have to** Refresh** your Excel sheet in order to respond to the changes.

**Here’s the Usage Guide**

**Step_1:** Go to the **Data** tab **⋙ Get & Transform Data** group **⋙ From Table/Range**.

**Step_2:** Insert/Select the range of your data in the **Create Table** dialog box.

**Step_3:** Check ‘**My table has headers**’ if you have any.

**Step_4:** Then click **OK**.

Excel will take you to the **Power Query Editor**.

Now,

- Press and hold the
**CTRL**button. Then click on the column headers to choose different columns. - To select the entire table, press and hold the
**SHIFT**button. Then click on the column header of the first and last column headers.

🔴 Right-click on a column header. Select** Remove Duplicates** from the context menu.

This is one way to remove duplicates from specific columns.

To remove duplicates from the entire table,

**Step_5:** Click on the **Table** drop-down icon at the top-left corner of your data table. Then select **Remove Duplicates** from the drop-down menu.

It will remove all the duplicates from the data table right away.

But wait a second. Look at the following picture.

Rows 2 & 3 both contain the same data. The only difference between them is their letter cases.

The** Power Query** doesn’t remove that because the **Power Query** is a case-sensitive tool. As their letter cases are different, the **Power Query** considers them as unique.

If you don’t want this,

**Step_6:** Then just select the column, **Customer** Name. Then follow: **Transform** tab **⋙ Text Column** group **⋙ Format** drop-down **⋙ Capitalize Each Word**.

Now again, click on the **Table** drop-down icon at the top-left corner of the data table.

Then select the command** Remove Duplicates**.

This time it just works perfectly. It removes all the duplicates. Even when the letter cases are not similar.

Now, look at the** Query Setting** dialog box on the right side of your screen.

- You can change the
**Table**name under the**PROPERTIES**drop-down. - Under the
**APPLIED STEPS**drop-down, you can see all the steps that you have taken so far. You can click on any of the steps there to go back and forth.

Now it’s time to load the data table into the worksheet.

Go to the **Home** tab and click on the **Close & Load** command.

It will instantly close the **Power Query Editor** window. Then it will import the data table into a new fresh worksheet.

**Final Result**

So, you will get your data table back in the worksheet like the following picture.

Now when you make any changes to your source data, just go to the **Data** tab and click **Refresh All**.

The output table will also get updated.

The output table doesn’t update itself automatically. It requires clicking on the **Refresh All** button. Thus, it’s a semi-dynamic process.

## 2. Remove Duplicates in Excel Using Formula

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

### 2.1 Dynamic Formula to Automatically Remove All Duplicates with 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**

- It supports all versions of Microsoft Excel.
- 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.

=CONCAT(B2:D2)

Here,** B2:D2** is the range of the first row. After inserting this formula into a cell, 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. After that, 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 Argument Explanation**

**B2:D16**is the range of the data table.**A2:A16**is the range of the helper column.**ROWS(A1:A1)**returns**1**. It refers to the immediate previous row number of the helper column. Here’s the helper column starting from row**2**. If your helper column starts from row**5**, then it would be**ROWS(A1:A4)**and so on.**{1,2,3}**refers that the output will be columns**1,2,&3**in the range**B2:D16**.**COUNTIF(A2:A16, A2:A16)=1**here**1**is used to remove duplicates but keep the 1st instance of each duplicate value. If you insert**2**here instead of**1**, it deletes every instance of the duplicates.

### 2.2 Dynamic Formula to Automatically Remove Duplicates with 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 is consist 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 Argument Explanation**

**B2:D16**is the range of the data table.**#A2**is the range of the helper column. When I select the range**A2: A16**, it automatically changes into**#A2**.**COUNTIF(A2#,A2#)=1**here**1**is used to remove duplicates but keep the 1st instance of each duplicate value. If you insert**2**here instead of**1**, it deletes every instance of the 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 Argument Explanation**

**B2:D16**is the range of the data table.**#A2**is the range of the helper column. When I select the range**A2: A16**, it automatically changes into**#A2**.**COUNTIF(A2#,A2#)=2**here**2**is used to remove every instance of the duplicates. If you insert**1**here instead of**2**, it deletes the duplicates but keeps the 1st instance of each duplicate value.

### 2.3 Dynamic Formula to Automatically Remove Duplicates without Helper Column [Supports Excel for MS 365]

This formula is consist of the **UNIQUE** function. This function is available only in

**Excel for Microsoft 365 for Windows****Excel for Microsoft 365 for Mac****Excel for the web****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 Argument Explanation**

**A2:C16**is the data range. You need to insert your data range here.**FALSE**is used to return unique rows. To return unique columns, insert**TRUE**instead.**FALSE**returns all distinct rows. This means it removes all duplicates but keeps the first instance of every duplicate value.

**Clear all Duplicates**

**Syntax**

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

**Formula**

=UNIQUE(A2:C16,FALSE,TRUE)

**Function Argument Explanation**

**A2:C16**is the data range. You need to insert your data range here.**FALSE**is used to return unique rows. To return unique columns, insert**TRUE**instead.**TRUE**returns unique rows only. It removes every instance of duplicate values.

### 2.4 Vlookup to Remove All Duplicates in Excel Using VLOOKUP Function [Supports All Versions of Excel]

The vlookup 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.

**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**

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

**Formula Argument Explanation**

**E2&F2&G2**: They are the top cells of my second data table. They are basically the lookup value.**$A$2:$A$16**: First column range of my first data table.**$B$2:$B$16**: Second column range of my first data table.**$C$2:$C$16**: Third column range of my first data table.**$A$2:$A$16&$B$2:$B$16&$C$2:$C$16**: Combines the**3**cells in each row from the first table. Thus, they create a virtual data table having**1**column only which is used as a lookup table range.**1**: This is the column index number. This refers to the first column of the virtually created data table by**$A$2:$A$16&$B$2:$B$16&$C$2:$C$16**.**FALSE**: Refers to the exact match between the lookup value and the lookup range.- The
**VLOOKUP**function returns the**#N/A**error when it doesn’t find any matching data. The**#N/A**means the value is unique. Otherwise, the**VLOOKUP**function returns the duplicate value itself. - The
**ISERROR**function returns a**TRUE**when the**VLOOKUP**function returns the**#N/A**error. Otherwise, it returns**FALSE**. To interpret it in simpler lines, the**ISERROR**function returns**TRUE**for the**Unique**values and**FALSE**for the**Duplicate**Values. - The
**IF**function returns**Unique**when the**ISERROR**function returns**TRUE**. For a**FALSE**returned by the**ISERROR**function, the**IF**function returns**Duplicate.**

**Step_1:** Now select the entire **Filter** column. Then press **CTRL + SHIFT + L** to apply the **AutoFilter**.

Or, you can follow the **Data** tab **⋙ Sort & Filter** group **⋙ Filter** command.

This will also apply the **AutoFilter** to the **Filter** column.

**Step_2:** After that, extend the drop-down menu at the bottom-right corner of the **Filter** column header.

**Step_3:** Select **Duplicate** from the list and click **OK** to filter only the duplicate value.

**Step_4:** Now select all rows of the filtered duplicate rows.

**Step_5:** Press **CTRL + –** to delete all the duplicate rows.

**Step_6:** Now select the **Data** tab **⋙ Soft & Filter** group **⋙ Clear** command to remove the **AutoFilter** from the selection area.

**Final Result**

Only the unique values are left.

### 2.5 Vlookup to Remove All Duplicates in Excel Using 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 the 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.

**Here’s the Formula**

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

**Formula Argument Explanation**

**E2&F2&G2**: They are the top cells of my second data table. They are basically the lookup value.**$A$2:$A$16**: First column range of my first data table.**$B$2:$B$16**: Second column range of my first data table.**$C$2:$C$16**: Third column range of my first data table.**$A$2:$A$16&$B$2:$B$16&$C$2:$C$16**: Combines the**3**cells in each row from the first table. Thus, they create a virtual data table having**1**column only which is used as a lookup table range.**“”**: The**XLOOKUP**function leaves a cell empty when it finds a unique value. Otherwise, you return the duplicate value itself.**0**: It represents an exact match while lookup vertically.

The above formula will return only the duplicate records. To filter out the duplicate records,

**Step_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.

**Step_2:** Press **CTRL + SHIFT + L** to apply the **AutoFilter**.

Or just select the **Data** tab **⋙ Sort & Filter** group **⋙ Filter command.**

**Step_3: **Expand the **AutoFiler** drop-down and uncheck **Blanks** only.

**Step_4:** Then click **OK**.

**Step_5:** Select all the filtered rows and press **CTRL + –** to delete them all.

**Step_6:** Now go to the **Data** tab **⋙ Sort & Filter** group **⋙** **Clear** command.

**Final Result**

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

### 2.6 Formula to Remove Duplicates in Excel but Keep First Instance with COUNTIF Function

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 Argument 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 Argument 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**.

**Final Result**

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

## 3. Remove Duplicates with Condition in Excel

### 3.1 Remove Duplicates in Excel without Shifting Cells by Replacing Duplicates with Blank 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,""))

**Formula 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))

**Formula 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))**.

### 3.2 Remove Duplicates in Excel in a Single Column Using Conditional Formatting

We can also remove duplicate values out of a single column with the help of the **Conditional Formatting** feature in Excel.

**Step_1:** Select a column range first.

**Step_2:** Then go to the **Home** tab **⋙ Conditional Formating** 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. Choose a color combination and click **OK**.

**Step_3:** Now select the column again but this time with the column header too.

**Step_4:** Press **CTRL + SHIFT + L** to apply the **AutoFiler**.

**Step_5:** Expand the **AutoFiler** drop-down at the lower-right corner of the column header name.

**Step_6:** Select **Filter by Color**.

**Step_7:** 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.

**Step_8:** Select all the filtered results and press **CTRL + –** to delete them all.

**Step_9:** Now navigate to the **Data** tab **⋙ Sort & Filter** group **⋙ Clear** command.

**Final Result**

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

## 4. Remove Duplicates in Excel Using VBA

### 4.1 Remove Duplicates Automatically in a Specified Range Using Excel VBA

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.*

### 4.2 Remove Duplicates in a Pre-Selected Range Using Excel VBA

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.*

### 4.3 VBA Code to Remove Duplicates 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")

*At 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

At first, select any cell of your dataset. Then press **CTRL + A** to select the entire data table.

Then press and hold the **ALT** key.

After that, press **A** (Selects the **Data** tab) **⋙** and press **M** (Selects the **Remove Duplicates** command).

Now the **Remove Duplicates** dialog box will appear.

- Check
**‘My data has headers’**if you have any. - Then choose the columns.
- 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?

Sometimes we consider two values to duplicate because they apparently look the same. But actually, they are not. There might be some invisible characters present that make them unique from one another. Or, the decimal places after the fraction numbers might differ. That can also make values unique from one another that seems apparently duplicated.

Anyways, I’m dropping here some possible reasons and their corresponding solutions. Hope that you will find them useful.

**Presence of Spaces:**Your data may contain either leading or trailing spaces or both. As the spaces are invisible, you might struggle to find the actual reason why you just can’t remove the duplicates. And your duplicate removal formulas just not working. If you do suspect having spaces in your data, you can clean them using the**TRIM**function. After cleaning all the spaces including leading and trailing spaces, you can try again to remove the duplicates. Hope this time it will work just fine.**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**Text**cell format in one cell and**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 than try again removing the duplicates. Hope this might work this time.**Different Decimal Places:**In the case of decimal fraction numbers, the decimal places after the decimal point can make the same number**Unique**to one another. Let me give you an example. The numbers**10**and**10.00**both have equal value. But in the eyes of Excel, they are not the same. Thus, if you want to remove them considering them as duplicates, it won’t just work. To fix this issue, make sure all the numerical values have the same number of decimal places after their decimal point.**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:**Applying a wrong formula can also prompt removing duplicates not working in Excel. One subconscious mistake often occurs when we need to use a mixture of absolute and relative cell references. Suppose, you ought to use absolute cell references to lock a cell address. But you used relative or mixed cell references instead. Thus, when you copy down your formula, the cell range may change. Thus, removing duplicates won’t just work perfectly.

## Conclusion

I’ve discussed some effective techniques to find, highlight, and delete duplicates in Excel. I except, 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!