How to find duplicates in Excel


How to find duplicates in Excel

Select your range of cells. As with any Excel function, you have to decide which data you want to work with.

excel duplicates 1 1200x433 1Adam Birney / Android Authority

Head up to the Home tab and locate the Styles section. Now click on Conditional Formatting to open a dropdown menu.

excel duplicates 2 1200x477 1Adam Birney / Android Authority

Go to Highlight Cell Rules and select the Duplicate values option.

excel duplicates 3 1200x331 1Adam Birney / Android Authority

Now you should see a menu pop up. This is where you can decide how you want to format your duplicate cells. For our example, we’re going to color the duplicates green. Now click OK to identify the duplicates.

excel duplicates 4 1200x293 1Adam Birney / Android Authority

How to find only duplicates or triplicates in Excel

Maybe you’re looking for a way to pair up data points, and you need to eliminate third points. You can expand the duplicate function to work with triplicates and above using the same steps. However, it’s slightly trickier as you also start using the COUNTIF function. Here is how to do it:

First, select your cell range and return to Conditional Formatting in the Styles section. Instead of going to Highlight Cell Rules, go down to New Rule below Icon Sets.

excel duplicates 5 1200x443 1Adam Birney / Android Authority

This will bring up a menu for you to build your rule. Now select Use a formula to determine which cells to format.

excel duplicates 6 1200x485 2Adam Birney / Android Authority

Enter your =COUNTIF formula. In this case, it’s =COUNTIF($A$1:$C$10, A1)=3 because we’re only looking for sets of exactly three. Click OK to get your results.

excel duplicates 7 1200x340 1Adam Birney / Android Authority

Excel formulas are cell-based, so A1 in the formula above indicates which cell is being checked. However, the formula is also applied to all other cells with the $A$1:$C$10 conditional formatting. This saves you the time of retyping the formula individually.

Read more: Ten best Excel apps and spreadsheet apps for Android


How do I find duplicates in Excel without deleting them?

To find duplicates in Excel, select the range of cells you’d like to inspect, then navigate to Conditional Formatting –> Highlight cell rules and select Duplicates values. In the Duplicate Values dialog, select the highlighting option you need from the drop-down list.

How do I delete duplicates in Excel but keep one?

Select the data list you need, and click Data > Remove Duplicates. Then in Remove Duplicates dialog, check the column name that you want to remove duplicates from, and if your data has header, check the My data has headers option, too. Click OK. And a dialog pops out to tell you how many duplicates have been removed, just close it. And now, all duplicates have been removed while keeping only one in the list.

Original Article