- #Delete duplicates in excel how to
- #Delete duplicates in excel code
- #Delete duplicates in excel series
The syntax for the function is as follows: = COUNTIF ( range, condition) The COUNTIF function helps count cells in a range that satisfy a given condition. We can then delete these visible rows and remove the filter to obtain rows containing only unique product names. The result is the set of all duplicate rows. Then, we will use this result to filter out those rows that occur for the second time or more. From the Developer tab, select Visual Basic.įirst, we will use the COUNTIF function to count the first occurrence of a product as 1, its second occurrence as 2, and so on.To enter the above code, copy it and paste it in your developer window. Note: You cannot undo changes made by this VBA script, so we suggest you keep a backup copy of your dataset before running the code.
#Delete duplicates in excel code
The code then removes all the rows from the range that contain duplicate product names. In the above code, we specified this as Column 1 or the Product Names column. It takes the selected range, as well as the columns that you want to base the duplicate removal on. This code uses a VBA built-in command for removing duplicates in list-objects. Rng.RemoveDuplicates Columns:=Array(1), Header:=xlYes If you are comfortable with a little coding, then you can use this method to remove duplicate rows based on a single column.Įven if you’re not that keen on coding, you can just copy-paste the following code: Sub Delete_duplicate_rows() So instead, you can have the code and add it to the Quick Access Toolbar, so that you can access it with a single click. This method is useful when you have to do this quite often and don’t want to follow many steps. Let me know if you use any other methods to identify or remove duplicates.You can do this easily using a short VBA code as well. With these methods, you can remove duplicates, and you can have a clean set of data to perform further data analysis. Thus, no cleanup is required after the duplicate removal. The remaining data moves up, and you don’t end up with any blank rows. The great thing is that the entire duplicate data set is removed completely. With the table tools menu, you have another option to Remove Duplicates, which works pretty much the same way as the first method. Table Tools MenuĬlick on it, to activate the Table Tools Menu. Once the data is converted into a Table, you will see a new Tab called Table Tools appear in the Menu bar, at the end of the menu set. You can press Control + T to convert a data set into a Table. If you convert your data into a table, you can use a lot of in-built functionality that only emerged in Excel 2007 onwards. Second Method: Convert Data Range into a Data Table To Get Most out of Excel, Learn the Pivot Table techniques in our Online Training on Basic / Intermediate Excel.
Learn the Key Features of Excel Quickly & Easily, by Joining the
This is a quick and simple way to remove duplicates in Excel. Only the first unique value would remain. Data without Duplicates.ĭo keep in mind that when a duplicate values is found, the first occurrence is considered original, and the second instance is removed. The Resulting data set only has unique values. Once you click OK, the duplicates are removed, and you are shown another popup informing you of the duplicates removed, and the number of unique values remaining. Remove Duplicates Button on Data tab in Excel 2010Īll you need to do is to highlight the row of cells which contain the duplicates, and click on the Remove Duplicates button on the Data tab.Įxcel will popup a window to ask about the column, and whether headers exist. Starting from Microsoft Excel 2007, and continuing in Excel 2010 & Excel 2013, there is a dedicated button on the Data tab, called Remove Duplicates.
#Delete duplicates in excel series
You had to write a complex formula, to identify the duplicates first, and then delete them manually or through a macro or a series of steps.
In Excel 2003 and previous versions of Excel, it was extremely frustrating to delete duplicates.
#Delete duplicates in excel how to
In this case, read on, and find how to remove the duplicate values.įirst Method: How to Remove Duplicate Cells in Excel Data Showing Duplicate Valuesīut you may not only want to find the duplicates… you actually want to eliminate them completely. The duplicate values will be highlighted in yellow. You can use this technique to identify duplicates in Excel. As soon as you begin working with a larger set of data, this problem of having duplicate values begins to creep in.Īnd it is most difficult to pick up duplicates.