How to highlight duplicate values in Excel

The article, How to highlight unique values in Excel, shows two easy ways to apply conditional formatting to unique values or the row that contains a unique value. In this article, we’ll do the same thing with duplicate values. We’ll first review the easy built-in rule that formats duplicate values. Then, we’ll apply a conditional format rule that highlights the entire record.

SEE: 69 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version. You can work with your own data or download the demonstration .xlsx file. The browser supports conditional formatting; however, you can’t use the browser to implement a formula rule.

How to highlight individual values in Excel

The simple data set shown in Figure A repeats a few values in column D: 1, 2, and 6. They’re easy to discern visually, but that won’t always be the case. Let’s use the built-in rule to highlight them:

1. Select the values you want to format; in this case that’s D3:D16.
2. Click the Home tab. Then, click the Conditional Formatting dropdown in the Styles group.
3. From the dropdown, choose Highlight Cells Rules, and then choose Duplicate Values from the resulting submenu (Figure A).
4. Choose a preset format from the dropdown to the right (Figure B).
5. When you click OK, Excel highlights the duplicate values in column D, as shown in Figure C.

Figure A

Figure B

Figure C

A built-in rule is easy to implement and might be adequate. When it isn’t, you might have to turn to a formulaic rule.

How to highlight rows in Excel

For better or worse, you can’t use a built-in rule to highlight the entire row when column D contains a duplicate value. For that, we’ll need a formula that relies on a COUNTIFS() in the form

COUNTIF(range,criteria)

where range identifies the entire data set (record) and criteria specifies the condition, which can be a cell reference, a value, or even an expression. Let’s try that now:

1. Select the data range, B3:E16–you want to highlight the entire row. If you use a Table, Excel will update range as you add and delete records.
2. Click Conditional Formatting in the Styles group and choose New Rule.
3. In the top pane of the resulting dialog, click the last option, Use a Formula to Determine Which Cells to Format.
4. In the bottom pane, enter the formula
   =COUNTIF($D$3:$D$16, $D3)>1
5. Click Format, choose a format, and then click OK. This time I choose a font color, so you can see both rules at work. Figure D shows the rule and a preview of the format.
6. Click OK.

Figure D

The COUNTIFS() function itself counts the number of times a value occurs in column D. If that value is greater than one, meaning the value occurs more than once, the function returns True and the format is applied. When the count is 1 or less, the function returns False, and nothing happens.

You have two conditional formatting rules to work with now. One is built-in and highlights individual values. The other is a formula and highlights the entire record.