Highlight Duplicates in Multiple Ranges

When you want to know what values are shared in 2 or more columns, highlighting the duplicate values is the best way to see them visually.

In this article, we will learn how to highlight duplicate values in two or more columns
Highlight Duplicate in Two or More Ranges
Here, I want you to highlight duplicate in ranges A1:A10, C2:C5 and D1:D6. Follow these steps:
1. To highlight duplicates, select all ranges. To select non-adjacent ranges, hold the CTRL key and use the mouse to select ranges.
01
2. Now go to Home->Conditional Formatting->Highlight Cells Rules->Duplicate Values.
02
(Or you can use ALT>H>L>H>D>ENTER sequential shortcut to highlight duplicates)
3. Hit Enter or click OK. (before you hit enter, you can see in a small drop-down, an option is available to highlight duplicates or unique values. And the default formatting option. You may select whatever you like.
You can see each duplicate value highlighted by colour.
03Alternate method:

How to find duplicate in Excel by using Conditional Formatting?

Let’s take another example to understand how we can find duplicates.

  • We have data in range A1:C15. Column A contains Student name, column B contains Mobile no. and column c contains Email Id.
  • Now, we want to find out the duplicates.

img1

  • Select the range A2:C15.
  • Go to the “Home Tab” and then in the “Styles” group, select “New Rule” from the Conditional Formatting dropdown menu.

img2

  • “New Formatting Rule” dialog box will appear. Now, select a rule type “Use a formula to determine which cells to format”.

img3

  • Write the formula in the box of “Format values where this formula is true:”
  • =COUNTIF($A$2:$C$15,A1)=2
  • Click on “Format” button. A ‘Format Cells’ dialog box will appear.
  • Go to Fill tab and Font, choose the color as per the requirement and click on OK.

img4

  • All duplicates will get highlighted with the color.

img5
This is how we can find duplicates in Microsoft Excel.
xlsx-1567
Download - Highlight Duplicates in Excel - xlsx
It is damn easy to highlight duplicate values in excel. Isn’t it? But if you have any questions regarding this topic or any other topic, feel free to ask it in the comments section below. I would love to interact with excel users like you.

Related Articles:

Highlight cells that contain specific text

How To Highlight Row in Excel if Row Contains Specific Text

Highlight Row Base on A Cell Value in Excel

How To Highlight Cells Above and Below Average Value

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube