How to Highlight Rows in Excel Based on Duplicates

  1. Select cells A1:A8.
  2. On Excel’s Home tab choose Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.
  3. In this case, Excel displays the Duplicate Values dialog box and also provides a preview of the formatting to be applied to your worksheet, as shown in Figure 1. Notice that the Duplicate Values dialog box is dual-purpose, as you can choose Unique Values instead of Duplicates.
  4. If the default red/pink combination isn’t to your liking, click the drop-down list on the right and then choose Custom Format to display a Format Cells dialog box from which you can make a variety of choices. Click to confirm your choices.

Figure 1: Use Conditional Formatting to highlight duplicates within your data.