Run Remove Duplicates Tool to Automatically Remove Duplicates in Excel If you want, you can also remove duplicate values from your data set using the built-in tools of Excel. Up to the last section, we’ve seen all suitable methods to remove duplicates using different formulas. In this method, you can also remove all the values that appear more than onceĪn Alternative to Excel Formula to Remove Duplicates Automatically.It regenerates the whole data set removing the duplicate rows.➤ Take a new column and insert this formula: 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. The previous two methods are for only those who use the new versions of Excel. Create an Excel Formula with IFERROR, INDEX, SMALL, CONCAT, and COUNTIF Functions to Automatically Remove Duplicates (For Older Versions) 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