how to remove duplicates in excel

To remove duplicates in Excel, you can use the built-in Remove Duplicates feature or the UNIQUE function. Here are the main methods:

Using the Remove Duplicates Feature

1. Select the range of cells or the entire table where you want to remove duplicates.
2. Go to the Data tab on the Ribbon.
3. Click on Remove Duplicates in the Data Tools group.
4. In the dialog box, select the columns you want to check for duplicates. You can check one or multiple columns depending on your criteria.
5. If your data has headers, check the My data has headers box.
6. Click OK.
7. Excel will remove duplicate rows based on your selection and show a message indicating how many duplicates were removed and how many unique values remain

Using the UNIQUE Function

• Select an empty column where you want the unique values to appear.
• Enter the formula =UNIQUE(range), replacing range with the actual range of cells you want to extract unique values from (e.g., =UNIQUE(A2:C14)).
• Press Enter, and Excel will spill the unique values into the selected column.
• This method does not delete duplicates from the original data but creates a new list of unique values

Additional Tips

• Use Conditional Formatting to highlight duplicates before removing them: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
• For large datasets or automated workflows, you can use Power Query to remove duplicates by loading the data into Power Query and using its Remove Duplicates option, then loading the cleaned data back into Excel

• Be cautious when removing duplicates, especially in large datasets, as it permanently deletes duplicate rows and could remove useful data if not done carefully

These methods cover most scenarios for removing duplicates efficiently in Excel.