Highlighting Duplicates in Microsoft Excel

How to Find and Remove Duplicate Entries in Excel

Finding and optionally deleting duplicate entries in your spreadsheets is easy with Excel’s built-in features. You can either highlight duplicates to view them or use Excel’s tools to remove them automatically.

Highlight Duplicate Values in Excel

To highlight duplicates in your dataset:

1. Select the Dataset: Open your spreadsheet and select the range of cells (including column headers) where you want to check for duplicates.
2. Apply Conditional Formatting:
   • Go to the Home tab on the ribbon.
   • In the Styles section, click Conditional Formatting.
   • Choose Highlight Cells Rules and then select Duplicate Values.
3. Choose Formatting:
   • In the “Duplicate Values” box, choose Duplicate from the first drop-down menu.
   • Pick your preferred formatting style from the second drop-down. If you want custom formatting, click Custom Format.
4. Click OK: Excel will now highlight any duplicate entries in the selected range, making them easy to spot.

Remove Duplicate Values in Excel

To remove duplicates from your dataset:

1. Select the Dataset: Highlight the range of cells containing the duplicates, including the column headers.
2. Open the Remove Duplicates Tool:
   • Go to the Data tab on the ribbon.
   • In the Data Tools section, click Remove Duplicates.
3. Configure the Remove Duplicates Box:
   • If your dataset includes column headers, check the My Data Has Headers box.
   • Ensure the correct columns are selected for checking duplicates.
4. Click OK: Excel will remove the duplicate entries and leave only the unique values in your dataset.

Unlock powerful features with a genuine Office 2021 Professional Plus Key – get the best deal at the lowest price today!