— Excuse me, have you seen my twin here?
– You have already asked.
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.
Method 1: Repeating cells
Select all cells with data and on the tab Home (Home) press the button Conditional Formatting (Conditional Formatting), then choose Cell Selection Rules – Duplicate Values (Highlight Cell Rules — Duplicate Values):
In the window that appears then, you can set the desired formatting (filling, font color, etc.)
Method 2. Selecting the entire line
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 Home — Conditional Formatting — Create Rule — Use Formula to Highlight Formatted Cells (Home — Conditional formatting — Create rule — Use a formula to determine which cells to format)and then enter the formula:
=COUNTIF($A$2:$A$20;$ A2)> 1
=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
Method 3: No Key Column
Let’s complicate the task. Suppose we need to search for and highlight repetitions not in one column, but in several. For example, there is such a table with a full name in three columns:
The task is still the same – to highlight matching full names, meaning the match in all three columns at once – first name, last name and patronymic at the same time.
The simplest solution would be, of course, to add an additional service column (which can be hidden later) with the text function CONCATENATE (CONCATENATE)to collect full name in one cell:
Having such a column, we, in fact, reduce the problem to the previous method.
If you want to solve everything without an additional column, then the formula for conditional formatting will be more complicated:
- Comparing two data ranges, looking for differences and matches
- Retrieving unique elements from a range