Excel tutorials

How to highlight duplicate cells in multiple ways in Excel

How to Highlight Duplicate Values or Duplicate Data

In order to Highlight the Background of the Color Cell in Excel we use Conditional Formatting. For this Excel tutorial I have prepared several ways to highlight the background color of a cell if there is duplicate values or text in it. In the following text, notice that there are multiple duplicate highlighting methods.

  • Highlight cells only with double value or text (do not highlight the first data)
  • Highlight all cells that contain duplicate data including the first data
  • Only highlight the cells in which the data appears for the first time

Highlight the cell color only if the data is duplicated when it appears in the column

The first example of this tutorial refers to the cell highlight only when the data is duplicated. In this way, we will not highlight the cells in which the data appeared for the first time.

Only highlight duplicate values

For the conditional formatting of cell ranges in the 'A' column, we can use the formula below. (This formula will only indicate cells that appear as duplicates.) Note the above, 'A5' and 'A8' cells are highlighted, as there is duplicate data from 'A2' and 'A7' cells.

=COUNTIF($A1:A$1;A1)>1

The same result can be obtained using a different formula that you can see below. (this formula will highlight the color and the empty cell that appears as a duplicate)

=IF(COUNT(IF($C$1:$C1=$C1;1;""))>1;TRUE;FALSE))

Highlight all cells in which the same value or text appears

Unlike the first case, in this example below, notice that 'Conditional Formatting' marked the color of all the cells with the same text or value.

Highlight all cells that contain the same data

The formula for conditional formatting used in the 'A' column is below.

=COUNTIF($A$1:$A$8;A1)>1

How to highlight duplicates using conditional formatting rules

Using the command "Format only unique or duplicate values" we can also highlight cells that contain the same value or the same information.

Format only unique or duplicate values

How to highlight duplicate values using the "Duplicate Values ..." command

Another way of conditional formatting, by which we can highlight all the cells that contain the same data, is to click on 'Conditional Formatting' on the HOME tab and then on 'Highlight Cells Rules' and choose 'Duplicate Values ...'

Conditional formatting of double values

Highlight color of all the cells in a range, appearing for the first time and there are duplicates

This case is different under all of the above. Notice in image highlighted cell. Excel highlighted only the cells where a particular data appeared for the first time. So Excel did not highlight those cells where the duplicate appeared.

Highlight all the cells that appear for the first time and there are duplicates

The conditional formatting formulas used in this case are the following:

Cell range 'A1:D2'. Notice in the picture above that the cell range 'A1:D2' is named "range". This name was used as an argument within the formula shown below.

=IF(ROW(A1)>MIN(ROW(range));COUNTIF(OFFSET(range;0;0;MIN(ROW(A1)-MIN(ROW(range)));MAX(COLUMN(range)));A1)+COUNTIF(OFFSET(range;MIN(ROW(A1)-MIN(ROW(range)));0;1;MIN(COLUMN(A1)));A1);COUNTIF(OFFSET(range;0;0;1;MIN(COLUMN(A1)));A1))=1

Formula conditional formatting in the 'G' column.

=COUNTIF($G1:G$1;G1)=1

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.