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.

**Table of Contents**Expand

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

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

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

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

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

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

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

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

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

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

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

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