The Duplicate Values option on the Highlight Cell Rules continuation menu in Excel 2016 enables you to highlight duplicate values within a selected cell range.
To highlight duplicate values in a cell range, follow these steps:
Select the range of cells in the worksheet where you want duplicates formatted in a special way.
Click the Conditional Formatting button in the Styles group of the Home tab of the Ribbon; then choose Highlight Cell Rules→Duplicate Values from the drop-down menu.
Excel opens the Duplicate Values Columns dialog box containing two drop-down lists: the first where you indicate whether Excel is to format identical values (Duplicate, the default) in the range or the standalone values (Unique) in the range, and the second where you indicate the type of formatting applied to either the duplicates or one-of-a-kind values.
Click the type of preset formatting (Red Fill with Dark Red Text, Yellow Fill with Dark Yellow Text, Green Fill with Dark Green Text, and so forth) or click the Custom Format option and select the custom formatting in the Format Cells dialog box.
If you define a custom format rather than select one of the preset formats, use the options on the Number, Font, Border, and Fill tabs of the Format Cells dialog box to designate all the formatting to be applied, and then click OK to close the Format Cells dialog box and return to the Compare Columns dialog box (where Custom Format appears in the third drop-down list box).
Click OK to close the Duplicate Values dialog box.
Excel then formats all the cells in the selected cell range whose values are exact duplicates with the conditional formatting you selected.