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.
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.
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)
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.
The formula for conditional formatting used in the 'A' column is below.
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.
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 ...'
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.
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.
Formula conditional formatting in the 'G' column.
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.