Table of Contents
In order to highlight duplicates from another sheet in Excel, select the range of cells from the other sheet and then go to the Home tab and select Conditional Formatting > Highlight Cells Rules > Duplicate Values. Select the formatting style and click OK to apply it. The duplicate values from the other sheet will be highlighted in the selected range.
To highlight duplicate values from another sheet in Excel, 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 from Another Sheet in Excel
Suppose we have the sheet in Excel called all that contains the names of all teams in the western conference of the NBA:
And suppose we have another sheet called playoffs that contains only the names of the teams in the western conference who made the playoffs:
Now suppose that we would like to highlight each team name in the all sheet that represents a duplicate value from the playoffs sheet.
To do so, highlight the cells in the range A2:A16 in the all sheet, 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 =ISNUMBER(MATCH(A2, playoffs!A:A, 0)) in the box, then click the Format button and choose a fill color to use.
Once we press OK, each team name in the all sheet that represents a duplicate value from the playoffs sheet will be highlighted:
Each of the eight teams that appear in the playoffs sheet are now highlighted in the all sheet.
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.