Table of Contents
The process of highlighting duplicates from another sheet in Excel involves using the “Conditional Formatting” feature. This feature allows the user to set a rule that will automatically highlight any duplicate values found in a selected range of cells from a different sheet. This can be useful for cross-checking data from multiple sheets and identifying any repeated values. By following the steps for conditional formatting, users can easily highlight duplicates from another sheet and efficiently manage their data in Excel.
Excel: Highlight Duplicates from Another Sheet
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: