Conditional Formatting | Remove Duplicates | IF Function
When working with a large database, you may find many duplicates. To identify or remove duplicates is important in your data cleaning and analysis. The following is the method to find and remove duplicates in one column. Please check how to find duplicates in two columns or how to find duplicates in multiple columns if you have more than one column.
1. Conditional Formatting to Find Duplicates
Conditional formatting is a quick way to find the duplicates in your data, for the entire spreadsheet or the selected data cells. Please see here for more details or the steps below:
Step 1: Select the column you need to find duplicates, e.g., Column A;
Step 2: Click the Home tab and click "Conditional Formatting" in the Styles section; click "Highlight Cells Rules" and select "Duplicate Value" from the drop-down list;
Step 3: Select "Duplicate" in the first box and select the formatting style in the second box;
Step 4: Click OK and duplicates will be in a different color;
2. How to Remove Duplicates
Step 1: Click anywhere in your data;
Step 2: Click ""Remove Duplicates" in the Data Tools section from the Data tab;
Step 3: In the Remove Duplicates window, select the column you need to remove duplicates (e.g., column A – "First Name");
Step 4: Click OK at the bottom and you will get the message of the results;
3. Use the IF Function to Find Duplicates
Step 1: Sort the column from A to Z (e.g., column A);
Step 2: Insert a helper column and type formula =IF(A2=A3,"duplicate","") in the first cell;
Step 3: Copy down and the first value of the duplicates will mark as "duplicate".
