Affiliate links on Android Authority may earn us a commission. Learn more.
How to find duplicates in Excel
Published onFebruary 9, 2023
As you grow your Excel skills, you’ll probably find yourself working with large amounts of data. Whether you’re using it as data points or searching for unique answers, it’s crucial to be able to find duplicates to ensure your calculations are correct. Here are a few helpful ways to get the job done.
Read more: How to find, highlight and remove duplicates in Google Sheets
QUICK ANSWER
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.
KEY SECTIONS
How to find duplicates in Excel
Select your range of cells. As with any Excel function, you must decide which data you want to work with.
Head up to the Home tab and locate the Styles section. Now click on Conditional Formatting to open a dropdown menu.
Go to Highlight Cell Rules and select the Duplicate values option.
Now you should see a menu pop up. 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.
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. Using the same steps, you can expand the same function to work with triplicates and above. 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.
This will bring up a menu for you to build your rule. Now select Use a formula to determine which cells to format.
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 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
FAQs
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. Select the highlighting option from the dropdown list in the Duplicate Values dialog.
Select the data list you need, and click Data > Remove Duplicates. Then in the Remove Duplicates dialog, check the column name that you want to remove duplicates from, and if your data has a header, check the My data has headers option, too. Click OK. A dialog will tell you how many duplicates have been removed; close it. And now, all duplicates have been removed while keeping only one in the list.