Create an Excel Formula with IFERROR, INDEX, SMALL, CONCAT, and COUNTIF Functions to Automatically Remove Duplicates (For Older Versions)
Related Content: How to Remove Duplicates Based on Criteria in Excel (4 Methods)ģ.
But you can’t keep one copy of the duplicate values as mentioned in the earlier method.In this method, you can remove all the values that appear more than once.➤ You will get the whole data set without the duplicate values. ➤ Finally, drag the Fill Handle rightward up to the total number of your columns (3 in this example) It regenerates the first column of the data set removing all the duplicates.Keep the absolute cell reference as intact as used here.Here B4:B14 is the first column of my data set, and $E$4:$E$14 is the new column that I produced.
To remove the duplicate values completely from our data set, you can use this formula: Using the UNIQUE function, you can remove the duplicates in both ways.Ĭompletely Removing the Values that Appear More than Once: Keeping One Copy of the Values That Appear More than Once.Completely Removing the Values That Appear More than Once.You can remove the duplicate values from a data set in two ways: You can use the UNIQUE function of Excel to remove duplicates from a data set. Use UNIQUE Function to Automatically Remove Duplicates in Excel (For New Versions) Today our objective is to discover a formula to remove the duplicates automatically.ġ. 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.īut unfortunately, the names of some students have been repeated along with their marks and grades. Excel Formula to Automatically Remove Duplicates.xlsxģ Uses of Excel Formula to Automatically Remove Duplicates