Find and Remove Duplicate Values in Excel

To find and highlight (not remove directly) duplicates in Excel using conditional formatting, you can follow these steps:

 

  1. Select the Range: Click on the top-left cell of the range where you want to check for duplicates.

 

  1. Open Conditional Formatting: Go to the `Home` tab on the Excel ribbon.

 

  1. Apply Conditional Formatting:

   - Click on `Conditional Formatting` in the ribbon.

   - Choose `Highlight Cells Rules`.

   - Select `Duplicate Values...` from the dropdown menu.

 

  1. Set Formatting Options:

   - In the dialog that appears, you can choose how you want Excel to format the duplicate values. For example, you can select a format like `Light Red Fill with Dark Red Text` to highlight duplicates with a specific color.

   - Click `OK` to apply the formatting.

 

  1. Adjust Conditional Formatting (Optional): If needed, you can adjust the rule by going to `Conditional Formatting > Manage Rules...`. Here, you can modify the rule settings, such as changing the formatting style or the range of cells.

 

  1. View Highlighted Duplicates: Excel will now highlight the duplicate values in the selected range according to the formatting you specified.

 

  1. Remove Duplicates (Optional): To actually remove the duplicates after identifying them, you can use the built-in `Remove Duplicates` feature:

   - Select the range of cells containing duplicates.

   - Go to the `Data` tab on the Excel ribbon.

   - Click on `Remove Duplicates` in the `Data Tools` group.

   - In the dialog box that appears, choose the columns where you want to remove duplicates and click `OK`.

 

Using these steps, you can effectively find and visually highlight duplicates in your Excel spreadsheets using conditional formatting. Remember, conditional formatting does not remove duplicates directly; it only helps in identifying them visually.

Related Videos