You are currently viewing How to Identify and Highlight Duplicates in Excel

One of the main concerns when working with a huge list of information is to check if there is any data being repeated by mistake. It is time-consuming and difficult to check this manually. Microsoft Excel has a good solution to identify and highlight duplicates in excel data entries. In this article, I will explain to you how to use Microsoft Excel’s ‘Conditional Formatting’ rule to identify duplicate data automatically and highlight them to bring to your notice immediately.

Follow these steps to Identify and Highlight Duplicates in Excel

To start with, we are taking a list of 15 fruits as sample data. Now we will use conditional formatting in excel and set rules, which will identify if any entry is repeated.

Step 1: Select the Range of Cells

First, we will have to select the range of cells to apply conditional formatting. So here, I am selecting 20 cells in column B (heading excluded) as highlighted below. You can select the entire column if you want. Similarly, you can also select rows as well. In the first 15 cells, we have the data already filled, and the next 5 cells are empty for now.

MS Excel Select Cells for Conditional Formatting

Step 2: Go to Conditional Formatting Settings

Once we have selected the cell, we will go to Conditional formatting and set a new rule. Under the Home tab, you will see Conditional Formatting in the Styles section.

MS Excel Conditional Formatting Option

Click on Conditional Formatting and click New Rule.

MS Excel Conditional Formatting New Rule

Step 3: Set Rules or Conditions

In the New Formatting Rule window, under the Select a Rule Type option: Select Format only unique or duplicate values and then click on the Format button below.

MS Excel Conditional Formatting New Formatting Rule Window

Step 4: Set Formatting

In this Format Cell window, I suggest you go to the Fill tab and select a color (we selected yellow), and click OK. You can do more formatting like making the text bold and changing text color too, depending upon your requirements.

MS Excel Conditional Formatting Format Cells Window

You will see a preview in the New Formatting Rule window, click OK.

MS Excel Conditional Formatting Format Preview

Testing the Conditional Formatting Rule

So now that the rules are set, let’s add or change some data and see how this works. We typed a duplicate entry in as Apples, which is already entered in

So, when I typed Apples in, Excel immediately traced the duplicate entry and highlighted both the cells. This is how it works and as you saw, it is very simple to work with conditional formatting.

MS Excel Conditional Formatting Identify and Highlight Duplicates


So, in this article, you learn how to use Conditional Formatting rules in Excel to identify and highlight duplicate entries in excel. In conditional formatting, there are more useful rules you can work with. We will make articles about it soon and link them here.

Share this article:

Leave a Reply