Site icon Best Excel Tutorial

How to highlight duplicates in Excel

Highlighting duplicates in Excel is a common task, allowing you to quickly identify and handle repeated values in your data. Excel provides a straightforward way to do this using Conditional Formatting. Here’s how you can highlight duplicates in Excel:

Steps to Highlight Duplicates in Excel

  1. Select Your Data: Click and drag to select the cells you want to check for duplicates. This can be a single column, multiple columns, or a specific data range.
  2. Go to Conditional Formatting:
    • Click on the Home tab.
    • Look for the Styles group.
    • Click on Conditional Formatting.
  3. Highlight Cell Rules:
    • In the dropdown menu of Conditional Formatting, hover over Highlight Cells Rules.
    • From the side menu, select Duplicate Values.
  4. Choose Formatting:
    • A dialog box will appear with options for formatting.
    • Excel usually defaults to highlighting duplicate values in red text, but you can change the highlight color or text color by clicking on the drop-down next to the preview box.
    • After selecting how you want the duplicates to be formatted.
  5. Excel will automatically highlight all duplicate values in the range you selected based on the formatting style you chose.

Additional Tips:

By following these steps, you can easily highlight duplicates in your Excel data, allowing for better data cleaning, error spotting, or simply understanding your data’s distribution and repetition. This feature is especially useful in large datasets where manually spotting duplicates would be inefficient.

Exit mobile version