How to Remove Duplicates in Excel

This guide will show you how to remove duplicates in Excel step by step. Removing duplicates in Excel is important for keeping your data clean and accurate. Whether you’re working with a basic list of contacts or a complex set of data, duplicates can mess up your results and cause mistakes. Excel has several easy-to-use tools to find and get rid of these duplicates. Let’s learn how to use different methods in Excel to effectively remove duplicates and improve data management.

How to Remove Duplicates in Excel but Keep One?

You can easily remove duplicates based on one column in Excel. Follow the below steps to remove duplicates but keep first instance in Excel:

Step 1. Select Data

• Select the range of cells from which you want to remove duplicates.
• You can select the data with or without header.

Step 2. Open Remove Duplicates dialog box

• Go to the Data tab on the Ribbon.
• Click the “Remove Duplicates” button in the “Data Tools” section.

Step 3. Select Header and Column

• In Remove Duplicates dialog box, the “My data has headers” will be ticked if you have selected the data with header.
• Since we have only one column (Fruits), it will be automatically selected or manually you can select the same.
• Then click “OK”.

Step 3. Unique Values

• The duplicate values found and removed. Unique values remain. Note that counts may include empty cells, spaces, etc.
• Click “OK” to close the Microsoft Excel dialog box.

How to Remove Duplicates in Excel based on Two Columns?

Follow the below steps to remove duplicates in Excel based on 2 columns:

• Select Data: Select the cells from which you want to remove duplicate values.
• Remove Duplicates dialog box: Data tab ⇒ Data Tools section ⇒ Remove Duplicates button.
• Headers and Columns: If you have header and selected tha data with header then tick “My data has headers”. To delete duplicate values, select one ore more columns that contains duplicates. Then click “OK” to keep the unique values. .

How to Remove Duplicates in Excel using Formula?

Follow the below steps to remove duplicates in Excel using formula:

• Select Data: Select the cells from which you want to remove duplicate using formula.
• COUNTIF formula: Use the COUNTIF formula to find the unique and duplicate values as shown in the below example.
• IF formula: You can also use the IF formula to identify greater than 1 and consider it as “Duplicate”, else “Unique”.
• Filter and Remove: After identifying the duplicates in Excel, you can filter the data and remove duplicates easily.

Formula in cell B2:

=IF(COUNTIF(A$2:A2, A2) > 1, “Duplicate”, “Unique”)

How to Remove Duplicates in Excel using Shortcut?

Below is the keyboard shortcut key to remove duplicate values in Excel:

Conclusion

In Excel, getting rid of duplicate values helps to keep your data clean, reliable, and easier to work with. You can also find duplicate values in Microsoft Excel by using the conditional formatting and advanced filters.

Learn More