If you work with large datasets in Excel, you may encounter duplicate values that make your data less accurate or harder to analyze. Fortunately, Excel has some built-in tools that can help you find and remove duplicates, and this guide will help you with it.
Excel: How to Delete Duplicates Manually or Automatically (2023)
You can either use the ‘Conditional Formatting’ feature to manually delete the duplicate values or use the ‘Remove Duplicate’ feature to automatically delete duplicate values on Excel and here’s how you can do it:
1. Conditional Formatting
Conditional formatting is a way to apply different formats to cells based on certain criteria. You can use conditional formatting to highlight duplicate values in your data to manually delete your data. This is useful when you want to review duplicate values before deleting them. Hence, here are the steps to use conditional formatting for duplicates:
- Firstly, select the cells you want to check for duplicates and click the ‘Home’ tab.
- Now click the ‘Conditional Formatting’ button.
- Select ‘Highlight Cells Rules’ from the menu, then click the ‘Duplicate Values’ option. This will open the Duplicate Values dialogue box.
- Pick the formatting option you want to apply from the box next to the values, then click the ‘OK’ button.
- Excel will highlight all the cells that have duplicate values in the selected range.
- Now you can manually delete the highlighted duplicate values.
2. Remove Duplicates
The Remove Duplicates feature allows you to delete duplicate values automatically from your data, and here’s how you can do it:
- Firstly, select the cell range where you want to delete the duplicate value.
- Now click the Data tab from the toolbar.
- Then click the ‘Remove Duplicates’ button.
- Under the ‘Columns’ box, check or uncheck the columns where you want to remove the duplicates. For example, if you only want to remove duplicates based on two columns, check those two columns and uncheck the rest.
- Now click the ‘OK’ button, and Excel will delete all the rows that have duplicate values in the selected columns and keep only the first instance of each value.
- Also, it will show you how many duplicate and unique values were removed or remained.
- This feature works effectively when any outlines or subtotals from your data are removed first.
That’s everything covered on how to delete duplicates in Excel. Also, check out our other guides, such as How to Spell Check in Excel or How to Remove the Password or Unprotect the Excel sheet.