In Microsoft Excel, duplicated values entered by accident can cause issues. Fortunately, we don’t have to go through entire lists to look for repetition. The Remove Duplicates function comes in handy to remove all identical items from a list with just a click. But sometimes you might need to only identify the duplicates without deleting them. There are two easy ways to highlight identical values. You can either do it through conditional formatting or by formulas.
Here’s how to find duplicates in Excel using both methods.
Find duplicate values by Conditional Formatting
The first way you can highlight duplicates is by using the Conditional Formatting tool. Here’s how.
Step 1: With your data selected, you must click on Home at the top of the Excel menu window. From the formatting options that appear, click on the one that says Conditional Formatting.
Step 2: Click on the first option that is titled Highlight Cells Rules.
Step 3: Highlight Cells Rules will open a second drop-down menu for you. Look for the option that says Duplicate Values and click on that.
Step 4: A Duplicate Values pop-up window will open up. Click on the Values With option box and select your desired highlighting option.
Step 5: Once you have selected your preferred highlighting method, hit the OK button. You will have all your duplicates highlighted that way.
Find duplicate values by formulas
Another way to highlight duplicates is by using formulas. Here are some formulas that you can use to highlight duplicates in a list.
Step 1: To start, click on a blank cell next to the list of values in which you wish to identify duplicates. In the cell, enter this formula:
=IF(COUNTIF($A$1:$B$7,A1)>1,”DUPLICATE”,””)
If you wish to have duplicates highlighted excluding the first, you can use this formula:
=IF(COUNTIF(A$1:A1,A1)>1,”DUPLICATE”,””).
Step 2: Drag the handle down to the cell to which you want to apply this formula and you will see the duplicate values identified.
For some extra Microsoft Excel tips, check out our guide on how to create a pivot table in Excel.
Editors’ Recommendations
