Skip to playerSkip to main content
Learn how to highlight duplicate cell in another sheet in Excel.

To highlight duplicate cells in another sheet in Excel, start by selecting the range of cells where you want the duplicates highlighted. Then, navigate to the "Conditional Formatting" option in the toolbar and choose "New Rule." Select "Use a formula to determine which cells to format" and input the condition, where "OtherSheetName" is the name of the sheet containing the data to be compared. This checks for duplicates between the current sheet and the specified sheet. Apply the desired formatting style, and duplicates will be highlighted accordingly. To identify duplicates between two Excel sheets, follow the same steps but adjust the condition to compare between the two sheets. This method allows for efficient comparison and highlighting of duplicate cells across different sheets in Excel.

Here are steps outlined in my vide.

Highlight duplicate cell in another sheet
1) Go to Sheet 416A
2) Select cells A2 to A26
3) Home ~ Style ~ Conditional Formatting
4) New Rule...
5) Select "Use a formula to determine which cells to format".
6) =ISNUMBER(MATCH(A2, '416B'!$A$2:$A$10, 0))
7) Format
8) Fill tab.
9) Select fill color as Red
10) OK
11) OK


Let's break down this formula

=ISNUMBER(MATCH(A2, '416B'!$A$2:$A$10, 0))

1) MATCH(A2, '416B'!$A$2:$A$10, 0): This part of the formula searches for the value in cell A2 of the current worksheet within the range '416B'!$A$2:$A$10, which represents the range of cells in column A of the worksheet named "416B". The MATCH function returns the relative position of the value within the specified range. The third argument 0 indicates an exact match.
2) ISNUMBER(): This function is wrapped around the MATCH function. It checks if the result of the MATCH function is a number. If MATCH finds a match, it returns the relative position of the value. If it doesn't find a match, it returns the #N/A error. ISNUMBER then converts this result into either TRUE (if the value is found) or FALSE (if it's not found).

Highlight duplicate cell in another sheet,How do I highlight duplicates in Excel from different sheets?,How can I identify duplicates between two Excel sheets?,How do you highlight a cell in Excel based on another sheet?,How do I compare two Excel sheets for matches?,

Check out my complete suite of Microsoft Excel Tips and Tricks.
https://www.youtube.com/@jjnet247/shorts
https://www.tiktok.com/@exceltips247
https://www.instagram.com/exceltips247/
https://www.dailymotion.com/ExcelTips247
https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/
https://x.com/ExcelTips247/media
https://www.reddit.com/r/Excel247/
https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #tips #tipsandtricks #microsoftexcel #accounting #fyp #fypシ #exceltips #exceltricks

Category

📚
Learning
Transcript
00:00We have compared two data sets on the same worksheet, but do you know how to compare two data sets
00:04in two different worksheets?
00:06Let me set the stage here.
00:07For example, we have two worksheets here, Worksheet 416A and 416B.
00:13The goal here is to identify duplicates in Worksheet 416B and then highlight the corresponding duplicate cell on Worksheet 416A.
00:22Here's how you do it.
00:23First, you go to Worksheet 416A and basically highlight column A data here like this.
00:28And after that, you go Home, Style, Conditional Formatting, New Rule.
00:34In this pop-up here for New Formatting Rule, you're going to select User Formula to determine which cell to
00:40format.
00:41And the formula that you're going to use, let's start off with a match function.
00:46The first argument on the match function is your lookup value, which is going to be A2, which is going
00:51to be in Worksheet 416A.
00:54Basically type A2 here, comma.
00:57After that, the second argument will be your lookup array.
01:01We're going to have to look up from Worksheet 416B here and basically highlight the column or the array of
01:09data set where you're going to look for that particular data for A2, which is the first argument here.
01:14And the third argument for the match is we're going to say 0 for the exact match.
01:20And basically what this match function does is returns the position where the match was found.
01:28And if it's not found, it will return an error message.
01:31So basically what we can capitalize out of this is to use a function called isNumber, which means if it
01:38returns the position, we're going to say yes.
01:40Go ahead and highlight it or conditional format it.
01:44And then don't forget the last parenthesis here.
01:46And after that, you're going to click on format.
01:49And then make sure you're on a fill tab here and select the color that you want to highlight when
01:54the match is made.
01:55I'm going to say highlight it with red and click OK and OK one more time.
02:00As you can see, any names that appears in Worksheet 416B is actually highlighted on this worksheet here, 416A.

Recommended