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.

Table of Contents

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.

Introduction to the Dataset

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.

Remove Duplicates in Excel for Large Data Using Remove Duplicates Command

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.

Erase Duplicates but Keep the First Instance Using Advanced Filter

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.

Remove Duplicates Using Power Query

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.

Remove Duplicates Using Power Query

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.

Dynamic Formula to Automatically Remove All Duplicates with Helper Column

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.

Dynamic Formula to Automatically Remove Duplicates  with Helper Column but Keep the First InstanceClear 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.

Dynamic Formula to Automatically Remove Duplicates  with Helper Column but Keep the First Instance2.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

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

Dynamic Formula to Automatically Remove Duplicates without Helper Column

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.

Dynamic Formula to Automatically Remove Duplicates without Helper Column2.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.

Vlookup to Remove All Duplicates in Excel Using VLOOKUP FunctionStep_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.

Vlookup to Remove All Duplicates in Excel Using XLOOKUP Function

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.

Formula to Remove Duplicates in Excel but Keep First Instance with COUNTIF FunctionNow 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 Duplicates in Excel without Shifting Cells by Replacing Duplicates with Blank Cells

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

Remove All Instances of the Duplicates

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.

Remove Duplicates in Excel in a Single Column Using Conditional Formatting

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!

(Visited 179 times, 2 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *