Excel Formula to Automatically Remove Duplicates (3 Quick Methods)

One of the most important and widely used activities in Excel is the removal of duplicate values from a data set. Today I’ll show how you can remove duplicate values from your data set automatically using an Excel formula.


Download Practice Workbook


3 Uses of Excel Formula to Automatically Remove Duplicates

Here we’ve got a data set with the Names of some students, their Marks in the examination, and the Grades they achieved at a school called Sunflower Kindergarten.

Data to Use Excel Formula to Remove Duplicates

But unfortunately, the names of some students have been repeated along with their marks and grades.

Today our objective is to discover a formula to remove the duplicates automatically.


1. Use UNIQUE Function to Automatically Remove Duplicates in Excel (For New Versions)

You can use the UNIQUE function of Excel to remove duplicates from a data set.

You can remove the duplicate values from a data set in two ways:

  • Completely Removing the Values That Appear More than Once
  • Keeping One Copy of the Values That Appear More than Once

Using the UNIQUE function, you can remove the duplicates in both ways.

Completely Removing the Values that Appear More than Once:

To remove the duplicate values completely from our data set, you can use this formula:

=UNIQUE(B4:D14,FALSE,TRUE)

UNIQUE Function to Remove Duplicates in Excel

Notes:

  • Three names of the students had duplicates: David Moyes, Angela Hopkins, and Brad Milford.
  • Among them, David Moyes and Brad Milford have been removed completely.
  • Angela Hopkins has not been removed because the marks and grades of two Angela Hopkins are not the same. That means they are two different students.

Keeping One Copy of the Values that Appear More than Once:

To keep one copy of the values that appear more than once, use this formula:

=UNIQUE(B4:D14,FALSE,FALSE)

UNIQUE Function to Remove Duplicates in Excel

Here we’ve kept one copy of all the names that had duplicates, except Angela Hopkins.

Both the Angela Hopkins have been kept because they are two different students.

Related Content: How to Remove Duplicates and Keep the First Value in Excel


2. Combine a Formula Using FILTER, CONCAT, and COUNTIF Functions to Remove Duplicates in Excel (For New Versions)

You can use a combination of the FILTER function, CONCATENATE function, and COUNTIF function to remove duplicates in Excel from your data set.

Step 1:

Take a new column and insert this formula:

=CONCATENATE(B4:B14,C4:C14,D4:D14)
  • Here B4:B14, C4:C14, and D4:D14 are the three columns of my data set. You use your one.
  • It merges the three columns into one single column.

CONCATENATE Function to Merge Three Columns in Excel

Step 2:

Go to another new column and insert this formula:

=FILTER(B4:B14,COUNTIF($E$4:$E$14,$E$4:$E$14)=1)
  • Here B4:B14 is the first column of my data set, and $E$4:$E$14 is the new column that I produced.
  • Keep the absolute cell reference as intact as used here.
  • It regenerates the first column of the data set removing all the duplicates.

FILTER Function to Automatically Remove Duplicates in Excel

Step 3:

Finally, drag the Fill Handle rightward up to the total number of your columns (3 in this example)

You will get the whole data set without the duplicate values.

Dragging the Fill Handle in Excel

Note:

  • In this method, you can remove all the values that appear more than once.
  • But you can’t keep one copy of the duplicate values as mentioned in the earlier method.

Related Content: How to Remove Duplicates Based on Criteria in Excel (4 Methods)


Similar Readings


3. Create an Excel Formula with IFERROR, INDEX, SMALL, CONCAT, and COUNTIF Functions to Automatically Remove Duplicates (For Older Versions)

The previous two methods are for only those who use the new versions of Excel.

Those who use the older versions of Excel can use a combination of the IFERROR function, INDEX function, SMALL function, CONCATENATE function, and COUNTIF function.

Step 1:

Take a new column and insert this formula:

=CONCATENATE(B4:B14,C4:C14,D4:D14)

  • Here B4:B14, C4:C14, and D4:D14 are the three columns of my data set. You use your one.
  • It merges the three columns into one single column.
  • It is an Array Formula. So select the whole column earlier and press CTRL+SHIFT+ENTER unless you are in Office 365.

CONCATENATE Function to Merge Three Columns in Excel

Step 2:

Go to another new column and insert this formula:

=IFERROR(INDEX(B4:D14,SMALL(IF(COUNTIF(E4:E14,E4:E14)=1,ROW(E4:E14)-ROWS(E1:E3),""),ROW(E4:E14)-ROWS(E1:E3)),{1,2,3}),"")
  • Here B4:D14 is my data set, E4:E14 is the new column that I made, and E1:E3 is the range before the column starts. You use your one.
  • {1, 2, 3} are the numbers of the columns of my data set. You use your one.
  • It regenerates the whole data set removing the duplicate rows.

Formula to Remove Duplicates in Excel

Note:

  • In this method, you can also remove all the values that appear more than once
  • But you can’t keep one copy of the duplicate values as mentioned in the earlier method.

An Alternative to Excel Formula to Remove Duplicates Automatically

Up to the last section, we’ve seen all suitable methods to remove duplicates using different formulas.

If you want, you can also remove duplicate values from your data set using the built-in tools of Excel.


Run Remove Duplicates Tool to Automatically Remove Duplicates in Excel

Step 1:

Select the whole data set.

Go to Data > Remove Duplicates tool in Excel Toolbar under the section Data Tools.

Remove Duplicates Tool in Excel Toolbar

Step 2:

Click on Remove Duplicates.

Put a check on all names of the columns you want to remove duplicates from.

Remove Duplicates Dialogue Box in Excel

Read More: How to Remove Duplicates from Column in Excel (3 Methods)

Step 3:

Then click OK.

You will get the duplicates removed automatically from your data set.

Remove Duplicates in Excel Using Excel Tools

Note:

In this method, one copy of the duplicate row will remain. You can’t completely remove the duplicate rows.


Conclusion

Using these methods, you can remove duplicates from your data set automatically in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo