Table of Contents
To highlight duplicates in Excel while excluding the first instance, you can use the Conditional Formatting feature. First, select the range of cells that you want to check for duplicates. Then, go to the Home tab and click on Conditional Formatting. From the dropdown menu, select “Highlight Cells Rules” and then “Duplicate Values.” In the “Duplicate Values” window, choose “Duplicate” from the first dropdown menu and select the format you want for the duplicate cells. Finally, click on the “Format” button and go to the “Fill” tab to choose a color for the formatting. This will highlight all duplicate values in the selected range, except for the first instance.
To highlight duplicate values in Excel except for the first instance, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.
The following example shows how to use this option in practice.
Example: Highlight Duplicates Except First Instance in Excel
Suppose we have the following column in Excel that contains the names of various basketball teams:
Now suppose that we would like to highlight each of the duplicate team names in column A except for the first instance.
To do so, highlight the cells in the range A2:A12, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
In the new window that appears, click Use a formula to determine which cells to format, then type =COUNTIF($A$2:$A2, A2)>1 in the box, then click the Format button and choose a fill color to use.
Once we press OK, all of the duplicate team names in column A except for the first instance will be highlighted:
We can see that each of the duplicate team names except for the first instance have been highlighted.
For example, the first instance of “Mavs” is not highlighted but every other occurrence of “Mavs” is highlighted:
Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: