Note: Before Removing duplicates, you must save the original data somewhere in the excel so that there is no data loss while doing this. Remove Duplicates dialog box will pop up which will show the columns which should be considered while deleting the duplicates.Īs in our example, there is only one column it should be selected as the duplicate rows will be removed based on this column. Under Data tab in the Data Tools group click on Remove Duplicates. To remove the duplicates from the range of cells first select the range having the duplicates. Here, the IF function analyse the array returned by the COUNTIF function, for TRUE it returns Duplicate and for FALSE it returns “-”. Now, to put it in a proper format let us modify the formula in a way: At the first occurrence of the cell content, COUNTIF counts FALSE and on further occurrences of the same content, it counts TRUE. Here, the COUNTIF function will return the array of TRUE and FALSE. Now, we will discuss the construction of one such formula which will let us identify the duplicates in Excel. We can construct several formulas to find the duplicates using the combination of different functions available in Excel. See in the picture below, the duplicates are highlighted in red colour.Īs such, there is not a unique or single formula to identify the duplicates in Excel. In the second drop-down box select the colour format in which you have to highlight the duplicates and click OK. Here, you have two drop-down boxes, in first you have to select Duplicate among the option Unique and Duplicate. Now, go to Styles group of Home tab and click on Conditional Formatting>Highlight Cells Rules>Duplicate Values.Ī Duplicate Values dialog box will pop out. To identify the duplicates, we have an inbuilt function in Excel and for that first select the range of the cells where you want to identify or find the duplicate values. Remove duplicates rows based on one or more columns.
#Excel remove duplicates two columns how to#
In this section, we will first learn to identify the duplicates in Excel and then we will see how to delete the duplicates? We will also discuss the scenario when there are multiple rows carry the duplicate data in several columns. It is difficult to understand the data with duplicates. It happens that many times we got stuck in an Excel workbook which contains duplicates cells.
0 kommentar(er)
