Excel 2016 All-in-One For Dummies
Book image
Explore Book Buy On Amazon

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:

  1. Select the range of cells in the worksheet where you want duplicates formatted in a special way.

  2. 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.

  3. 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).

  4. 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.

About This Article

This article is from the book:

About the book author:

Greg Harvey, PhD, is the president of Mind Over Media, LLC., and a bestselling author of books on Excel, including all editions of Excel For Dummies and Excel Workbook For Dummies. He began teaching business users about computers back in the 1980s, and has been a dedicated educator ever since.

This article can be found in the category: