How to highlight duplicates from another sheet

Answer

To highlight duplicates in Excel where the comparison range is on a different sheet, follow these steps:

1. Select the cells you want to check for duplicates on your current sheet.

2. Go to the Home tab on the ribbon.

3. Click Conditional Formatting → New Rule.

4. Choose Use a formula to determine which cells to format.

5. Enter a formula using the COUNTIF function to reference the other sheet. For example, if you are checking values in Sheet1 column A against values in Sheet2 column A, the formula is: 

   =COUNTIF(Sheet2!A:A, A1)>0

   Explanation:

   • Sheet2!A:A is the range to check for duplicates.

   • A1 is the first cell in your selected range on the current sheet.

   • >0 indicates that if a match exists, the formula returns TRUE, triggering the formatting.

6. Click Format, choose your desired highlighting style (fill color, text color, or border), and press OK.

7. Click OK again to apply the rule.

Notes:

• The formula works dynamically, so as you add new values to either sheet, the highlighting updates automatically.

• For Mac, the steps are the same. However, when typing sheet names with spaces, enclose them in single quotes, e.g.,

  =COUNTIF('Sheet 2'!A:A, A1)>0

• Avoid selecting entire columns unnecessarily, as using large ranges may slow down Excel in big workbooks.

This method ensures that duplicates are clearly highlighted based on values from another sheet without manually comparing the data.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report