How to find duplicates in Excel ?
Let’s say that we have a long list of something and we assume that some elements of this list are repeated more than 1 time. I would like to see these repetitions explicitly, i.e. highlight duplicates with color. There are several ways to do this in Excel.
Contents
Find Duplicate in Excel
Select all the cells with data and on the tab Home.
– Home
– Conditional Formatting
– Highlight Cells Rules
– Duplicate Values
In the window that appears then, you can set the desired formatting (filling, font color, etc.)
Find Duplicate in Google Sheets
If you want to highlight not single cells, but entire rows at once, you will have to create a conditional formatting rule with a formula. To do this, select all the data in the table and select
– Format
– Conditional formatting
– Custom Formula is
– Use a formula to determine which cells to format
and then enter the formula:
=COUNTIF($A$2:$A$20,$A2)>1
where
- $A$2:$A$20 is the column in the data where we are checking for uniqueness
- $A2 – reference to the first cell of the column
- Read Also :
- 10 Ways to Improve Website Indexing and Crawlability
