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.
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.
Click on Conditional Formatting and click 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.
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.
You will see a preview in the New Formatting Rule window, click OK.
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 sr.no.16 as Apples, which is already entered in sr.no.3.
So, when I typed Apples in sr.no.16, 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.
Conclusion
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.
