top of page

How to Highlight Duplicates in Excel

Managing large datasets in Excel becomes more efficient when you can easily spot duplicate values. Whether you're working on financial data, customer records, or any other information, identifying duplicates is crucial for data accuracy. In this article, we'll guide you through the process of highlighting duplicates in Excel, making your data analysis smoother and error-free.

Highlight Duplicates in Excel

Follow the below simple steps to highlight duplicates in Excel:

Step 1: Prepare Your Data

Assuming you have a table, let's call it a "Sales Table," with columns like ProductID, Product Name, and Sales Amount. The objective is to identify and highlight any duplicate values within the ProductID column.

Highlight Duplicates in Excel - Create table

Step 2: Apply Conditional Formatting

Select the range of cells you want to check for duplicates. In our case, that would be the ProductID column.

Navigate to the "Home" tab in the Excel ribbon.

Click on Conditional Formatting" option. and select "Highlight Cell Rules".

Highlight Duplicates in Excel - Conditional Formatting

Step 3: Customize Highlighting

A dialog box will pop up, showing the options for customizing the highlighting.

You can choose the formatting style for the duplicated cells. Excel usually highlights duplicates with a light red fill and dark red text by default.

You can also pick a different style if you prefer.

 Highlight Duplicates in Excel - Format Cells

Step 4: Confirm and Observe

Once you've set the formatting, click the "OK" button.

As a result, Excel will automatically highlight duplicates in Excel sheet within the selected range.

Highlight Duplicates in Excel - Result


By following these straightforward steps, you can easily highlight duplicates in Excel, empowering you to maintain data integrity and enhance your data analysis tasks.


bottom of page