How to Use Formula to Automatically Remove Duplicates in Excel

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.

Use Formulas to Automatically Remove Duplicates: 6 Easy Methods

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

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

Keeping One Copy of the Values that Appear More than Once

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

• In the first place, To keep one copy of the values that appear more than once, we will type the following formula in cell G5.

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

Read More: How to Remove Duplicates Based on Criteria 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.

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.

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

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.

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

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

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.

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

Read More: How to Remove Duplicates Using VLOOKUP in Excel

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.

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

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

The COUNTIF function counts the number of cells based on criteria.

• In addition, press ENTER.
• 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 the 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.

Read More: How to Find & Remove Duplicate Rows in Excel

Practice Section

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

Download Practice Workbook

You can download the following Excel file and practice while reading this article.

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.

Related Articles

• How to Remove Duplicate Rows Based on One Column in Excel
• How to Remove Duplicate Rows in Excel Based on Two Columns
• How to Remove Duplicates from Columns in Excel
• Hide Duplicate Rows Based on One Column in Excel 
• How to Remove Duplicate Rows in Excel Table

<< Go Back to Remove Duplicates in Excel | Duplicates in Excel | Learn Excel