# How to Use Formula to Automatically Remove Duplicates in Excel

Get FREE Advanced Excel Exercises with Solutions!

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 6 easy ways to use formula to automatically remove duplicates in Excel.

## 6 Easy Methods to Use Formulas to Automatically Remove Duplicates

In the following dataset, you can see that we have the Student Name, Marks, and Grade columns. Here, you can easily notice that the names of some students have been repeated along with their marks and grades. Next, using this dataset, we will go through 6 easy methods to use formula to automatically remove duplicates in Excel. Here, we used Excel 365. You can use any available Excel version. ### 1. Use Excel UNIQUE Function to Eliminate Duplicates

In this method, we will use the UNIQUE function to automatically remove duplicates in Excel using formula. Here, one thing must be noted the UNIQUE function is only available in Excel 365 and higher versions of Excel.

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

• Completely Remove 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

Here, we will remove the duplicate values completely from our data set.

• First of all, we will type the following formula in cell G5.
`=UNIQUE(B5:D15,FALSE, TRUE)` Formula Breakdown

• UNIQUE(B5:D15,FALSE, TRUE) → the UNIQUE function finds a number of unique data in an array.
• B5:D15 → indicates an array.
• FALSE → indicates by_col.
• TRUE → indicates that the duplicates will be removed completely.
• After that, press ENTER.
• Here, as soon as you press ENTER, the unique values will be shown.
• As a result, you can see the unique values in cells G5:I11. 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

Here, we will remove the duplicate values completely from our data set.

`=UNIQUE(B5:D15,FALSE,FALSE)` • Afterward, press ENTER.
• Therefore, you can see the result in cells G5:I13.
• Here we’ve kept one copy of all the names that had duplicates, except Angela Hopkins.
• Both 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. CombineCONCATENATE, FILTER, and COUNTIF Functions to Remove Clone

In this method, we will use the combination of the FILTER, CONCATENATE, and COUNTIF functions to remove duplicates in Excel using formula.

Steps:

• First of all, we will take a new column and insert the following formula in cell E5.
`=CONCATENATE(B5:B15,C5:C15,D5:D15)`

Here, the CONCATENATE function sums up three columns into one. • After that, press ENTER.
• Therefore, you can see the new column is filed automatically with merged values. • Afterward, we will go to another new column and insert the following formula in cell G5.
`=FILTER(B5:B15,COUNTIF(\$E\$5:\$E\$15,\$E\$5:\$E\$15)=1)` Formula Breakdown

• COUNTIF(\$E\$5:\$E\$15,\$E\$5:\$E\$15)=1 → the COUNTIF function counts cell numbers that meet the criteria.
• FILTER(B5:B15,COUNTIF(\$E\$5:\$E\$15,\$E\$5:\$E\$15)=1) → the FILTER function filters a range of data based on defined criteria.
• After that, press ENTER.
• Therefore, you can see the unique values in cells G5:G11.
• Moreover, we will drag the Fill Handle rightward up to the total number of your columns (3 in this example). • Therefore, you will get the whole data set without the duplicate values. 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)

### 3. Merge IFERROR, INDEX, SMALL, CONCATENATE, IF & COUNTIF Functions

The previous two methods are for only those who use the new versions of Excel.  This method is helpful when you have an older version of excel and want to remove duplicates automatically using formula.

Here, we will use a combination of the IFERROR, INDEX, SMALL, CONCATENATE, IF, and COUNTIF functions.

Steps:

• In the beginning, we will take a new column and insert this formula in cell E5.
`=CONCATENATE(B5:B15,C5:C15,D5:D15)` Formula Breakdown

• CONCATENATE(B5:B15,C5:C15,D5:D15) The CONCATENATE function merges the three columns into one single column.
• Here B5:B15, C5:C15, and D5:D15 are the three columns of my data set. You use your one.
• Afterward, since it is an Array Formula, we will press CTRL+SHIFT+ENTER unless you are in Office 365.
• Hence, you can see the complete column with merged values. • Next, to remove the duplicate values, we will type the following formula in cell G5.
`=IFERROR(INDEX(B5:D15,SMALL(IF(COUNTIF(E5:E15,E5:E15)=1,ROW(E5:E15)-ROWS(E2:E4),""),ROW(E5:E15)-ROWS(E2:E4)),{1,2,3}),"")` Formula Breakdown

• The ROWS function finds out the number of rows in an array.
• The IF function does a logical comparison between a given value and the value we expect.
• The COUNTIF function counts a number of cells based on criteria.
• The INDEX function finds the data from a data range.
• The SMALL function gives the output of kth lowest value in an array.
• The IFERROR function returns a blank cell when the formula contains an error.
• At this point, press ENTER.
• Hence, you can see that the cells G5:I11 do not contain any duplicate values.
• Therefore, you can 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.

### 4. Utilize Excel VLOOKUP Function for Removing Duplicates Automatically

In this method, we will use the VLOOKUP function to automatically remove duplicates using formula in Excel. Here, we have modified the dataset for this purpose. Therefore, you can see the Book List-1 and Book List-2 columns. You can easily notice duplicate values in Book List-1. Next, we will use VLOOKUP to get rid of duplicates. Steps:

• First, create another column beside Book List-2 naming Values.
• Here, we will apply the VLOOKUP function in the Values column. • Then, select cell D5 and write down the following formula.
`=VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE)` Formula Breakdown

• VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE) → the VLOOKUP function searches for a value in an array.
• Lookup_value is C5.
• Table_array is \$B\$5:\$B\$14.
• Col_index_num is 1.
• [range_lookup] is FALSE as we want the exact match
• Output: The Golden Bowl
• Explanation: The Golden Bowl is the duplicate value.
• Next, press ENTER.
• Moreover, we will drag down the formula with the Fill Handle tool. • Therefore, you can see the complete Values column.
• If there are any unique values in those two columns, the function will return the #N/A error. • Consequently, we have got our duplicate and unique values, and now we have to filter them.
• To do that select the headings of the dataset >> go to the Data tab.
• Then from the Sort & Filter group > select Filter. • Now we have the Filter icon in every column of the dataset.
• Next, click on the drop-down icon in the Values column, and from the sorting option, check on #N/A and click OK. • Finally, we have successfully removed duplicates from the dataset. ### 5. Integrate VLOOKUP, IF, and ISERROR Functions

In this method, we will use the combination of the VLOOKUP, IF, and ISERROR functions to remove duplicates automatically in Excel.

Steps:

• First, we will use the same dataset from the previous methods.
• After that, we will type the following formula in cell D5.
`=IF(ISERROR(VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE)),"Unique","Duplicate")` Formula Breakdown

• VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE) → this formula will find out the exact match of cell C5 value in the range of cells \$B\$5:\$B\$14. Here, Lookup_value is C5, Table_array is \$B\$5:\$B\$14. Col_index_num is 1 and [range_lookup] is (FALSE) as we want the exact match.
• IF(ISERROR(VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE)),”Unique”,”Duplicate”) → If the value is true, the formula will return “Unique”. If the value is false, the formula will return “Duplicate’’.
• Output: D5
• Explanation: D5 returns Duplicate since the formula detects a duplicate value.
• Afterward, press ENTER.
• Therefore, you can see the result in cell D5.
• Furthermore, we will drag down the formula with the Fill Handle tool. • Therefore, you can see the complete Values column.
• Next, we will remove the Duplicates using the Filter tool.
• To do that select the headings of the dataset >> go to the Data tab.
• Then from the Sort & Filter group, select Filter. • Therefore, you can see the column headings have a Filter icon.
• Furthermore, we will click on the drop-down icon of the Values columns.
• Here, we will unmark Duplicates since we want to remove them.
• Moreover, click OK. • Hence, you can see that we only have unique values in the dataset. ### 6. UniteTEXTJOIN and COUNTIF Functions

In this method, we will use the TEXTJOIN and COUNTIF functions to remove duplicates automatically in Excel. Here, to do so, we have added Combined and Count columns in our dataset. We will use the TEXTJOIN function to find the combined values in the Combined column. After that, we will use the COUNTIF function to find the duplicate number in the Count column. Steps:

• In the first place, we will type the following formula in cell D5.

`=TEXTJOIN("", FALSE,B5:C5)` Formula Breakdown

• TEXTJOIN(“”, FALSE,B5:C5) → the TEXTJOIN function adds text from multiple ranges.
• Output: Frank Orwell99
• Explanation: Frank Orwell99 is the combined text of cells B5 and C5.
• At this point, press ENTER.
• Therefore, you can see the result in cell D5.
• Moreover, we will drag down the formula with the Fill Handle tool. • Therefore, you can see the complete Combined column. • Next, we will type the following formula in cell E5.
`=COUNTIF(\$D\$5:\$D\$15,D5)`

The COUNTIF function counts the number of cells based on criteria. • Along with that, we will drag down the formula with the Fill Handle tool. • Therefore, you can see the complete Count column.
• Here, you can see in the Count column, the value is 1 for unique values, and the value is 2 for duplicate values.
• Furthermore, we will remove the duplicates by removing the cells that have 2 in the Count column.
• To do so, select the headings of the dataset >> go to the Data tab.
• Then from the Sort & Filter group, select Filter. • Therefore, you can see the column headings of the dataset have a filter icon.
• Moreover,  we will click on the drop-down icon of the Count column.
• Then, we will unmark 2 >> click OK. • Therefore, you can see that the dataset has no duplicate value. ## How to Remove Duplicates Using Remove Duplicates Tool

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. Here, we will use the Remove Duplicate tool to remove duplicates automatically in Excel.

Steps:

• First of all, Select the whole data set.
• Then, go to the Data tab.
• Moreover, from the Data Tools group >> select Remove Duplicates. • At this point, a Remove Duplicates dialog box will appear.
• Here, we will select all the columns >> click OK. • Therefore, you will see a confirmation dialog box appear.
• Then, click OK. • Therefore, you can see that we have removed the duplicate Student Name.
Note: In this method, one copy of the duplicate row will remain. You can’t completely remove the duplicate rows. ## Practice Section

You can download the above Excel file and practice the explained methods. ## Conclusion

Here, we show you 6 easy methods for formulas to automatically remove duplicates in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.

## Related Articles #### 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 Advanced Excel Exercises with Solutions PDF  