Highlight Duplicate Records in an Excel List

With Excel’s conditional formatting, you can highlight cells based on specific rules. There are some built-in rules available, and you can use formulas to create your own formatting rules.

Highlight Duplicates

In this example, we want highlight the duplicate rows in a table. There is a built-in rule for highlighting duplicate values in a single column, but nothing that will check an entire row.
highlightduplicatesribbon
So, we’ll create our own rule, and it will require a new column on the worksheet, before we add the conditional formatting.

Concatenate the Data

In the sample data, there are two identical rows, and these should be highlighted after we apply our conditional formatting.
highlightduplicaterows03
The first step is to use the CONCATENATE function to combine all the data into one cell in each row. Add a new heading in cell G1 – AllData – and in cell G2, enter this formula, to combine the data from all the cells in that row.
=CONCATENATE(A2,B2,C2,D2,E2,F2)
highlightduplicaterows04
Next, copy the formula down to the last row of data.

Apply the Conditional Formatting

Then, a conditional formatting rule is set, to color the rows that are duplicate records. We’ll use the COUNTIF function to check for duplicates in the AllData column.
=COUNTIF($G$2:$G$8,$G2)>1
highlightduplicaterows01
If there is more than one instance of a data combination, that indicates a duplicate row, and the cells in columns A:F will be coloured. The two rows with duplicate records are highlighted, so our conditional formatting formula worked!
highlightduplicaterows05

Download the Sample File

For detailed instructions, and to download the sample file, please visit my Contextures website: Highlight Duplicate Records in a List



Watch the Video

To see the steps for setting up the conditional formatting, please watch this video tutorial.

__

_________________________
Save