How to remove duplicates in Excel

Before deleting those duplicate values from your Excel spreadsheet outright, you’ll need to find them first. Here’s how to do that:

Step 1: Highlight all the cells you want Excel to scan for duplicates. Then select the Home tab from the Ribbon Menu at the top of the screen.

Step 2: On the Home tab, click on Conditional Formatting. From the menu that appears select Highlight Cells Rules.

Step 3: Then choose Duplicate Values. In the dialog box that appears, click on the drop-down menu after the phrase Values With and choose your desired color formatting for the cells that are deemed to be duplicates.

Step 4: Click OK. All of the duplicates from the selected cells should have been detected and marked with your chosen color formatting. You should now be able to easily see which values in your spreadsheet are duplicates.

Now that you know which values in your spreadsheets are duplicates, you can remove them. Here’s how:

Note: Removing duplicates in Excel usually involves permanently deleting them, so you may want to make another copy of the original version of this data before proceeding with removing the duplicates.

Step 1: Highlight the group of cells that contains the duplicate values you’d like to delete.

Step 2: Click on the Data tab on the Ribbon Menu at the top of the screen. Then navigate to the Data Tools section of the Data tab menu.

Step 3: In the Data Tools section, click on the Remove Duplicates button. (It looks like three blue and white cells with a red X.)

Step 4: In the Remove Duplicates dialog box that appears, and in the Columns section, check any boxes next to the spreadsheet columns where you’d like the duplicates to be deleted. Uncheck the ones you’d like left alone.

Step 5: Then click OK.

Excel will then scan the selected columns for duplicates and remove them. When it’s done, a dialog box will pop up notifying you how many duplicates were removed and how many unique values were left alone. It may also say that it couldn’t find any duplicate values to remove. If that’s the case, scroll to the next section.