Forum Discussion
Highlight Duplicate values across multiple sheets
I have a spreadsheet with an exhaustive list of names (ALL NAMES). I then have several other sheets (Silver, Gold, Top, Art, Music, Latin, Key) that have the names of various award recipients (a ...
In the following, I will assume that the names are in A2 and down on each sheet.
Select A2:A1000 or however far down the data go on the ALL NAMES sheet.
A2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=OR(ISNUMBER(XMATCH(A2, Gold!A:A)), ISNUMBER(XMATCH(A2, Silver!A:A)), ISNUMBER(XMATCH(A2, Top!A:A)))
Add more ranges as needed. If a sheet name contains spaces or punctuation, enclose it in single quotes: 'Sheet Name'!A:A
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
I have referenced this countless times. Thank you!