How to Find Duplicates in Excel & Numbers
Whatsapp Pinterest

A spreadsheet is a great way of packing lots of information into a dense area — but, more often than not, these documents will become unmanageable and unwieldy when they’re in use for a long period of time.

Whether your spreadsheet is tracking your family’s budget or the people who subscribe to your newsletter, beyond a certain point it will be impossible for you to scan through manually with any degree of accuracy. This can be a real problem if you need to check for duplicates.

Fortunately, you can delegate the grunt work to your computer. By using conditional formatting or the COUNTIF function, you can find duplicates in columns or duplicates across rows of a spreadsheet in no time at all. Here’s how to do it.

Finding Duplicates Using Conditional Formatting

Conditional formatting is the quickest and easiest way to find duplicates on a single sheet. We’re going to use this technique to sort through this database of email addresses, weeding out any duplicates that have arisen thanks to users submitting the same information more than once, and registering the same address under more than one name.

source list

The first step is to select all the data we want to scour for duplicates. Then, head to the Styles section of the Home tab and navigate to Conditional Formatting > Highlight Cells Rules > Duplicate Values.

duplicate values

This will bring up a window that allows you to customize the way that duplicate values are styled. This is an optional step, so if you’re just looking to find duplicates and don’t care about the color scheme, click OK to move on.

duplicate values styling

You should find that any duplicate entries are now highlighted in red. This should make it easy for you to delete unnecessary cells, or even at least understand at-a-glance which information is extraneous.

Finding Duplicates Using COUNTIF

Sometimes, you might want to check for duplicates across more than one sheet, and that requires a combination of conditional formatting and the COUNTIF formula. This technique will also work with older versions of Excel, and its Mac equivalent, Numbers.

two sheets

This time, our database of email addresses is split across two different sheets in the same document. First, we need to set up a function Need to Learn Excel? 10 Experts Will Teach You for Free! Need to Learn Excel? 10 Experts Will Teach You for Free! Learning how to use Excel's more advanced features can be tough. To make it a little easier, we have tracked down the best Excel gurus who can help you master Microsoft Excel. Read More  that can find duplicates in the column next to our first set of data.

We’re going to use COUNTIF, which uses the syntax COUNTIF(range, criteria). For my example, the function will read:

=COUNTIF(Sith!$A:$A, A1)

There’s a couple of important details to unpack here, so that you can repurpose this function for your own needs. First, there’s the reference to a different sheet — mine reads Sith! because of the subject matter, but you might need to use Sheet2! or whatever name you’ve given that particular sheet.

Second, there’s the criteria, which unlike the range does not use an absolute reference. This is because we want the range of cells being checked to remain the same every time, but we want each individual function to look for an individual email address.

Once that function is in place, use the bottom right hand corner of the cell to fill as many rows as are required.

count duplicates

As you can see in cell B5, the function will count any duplicates that it finds on the other sheet. We can make this information a bit easier to digest by applying another piece of conditional formatting to the column.

greater than

Highlight the entire column by clicking on its header, then head to the Home tab and navigate to Conditional Formatting > Highlight Cells Rules >  Greater Than.

greater than popup

Enter 0 into the pop-up, and make any styling edits that you require using the drop/down. Press OK and you should see something like the results below.

duplicates found

The number of duplicates present on the other sheet will be counted and highlighted for your convenience. We can combine that with the first technique in this article to give a full picture of all the duplicates in the document.

full list of duplicates

With that information at your fingertips, removing any duplicates from your document should be a breeze.

Going Further with VBA

The techniques in this article will allow you to check your spreadsheet for duplicates, but this kind of task is even easier to accomplish once you’re a master of VBA How You Can Make Your Own Simple App With VBA How You Can Make Your Own Simple App With VBA For those of you that would really love to be able to write your own application, but have never typed a single line of code before, I'm going to walk you through making your very... Read More .

VBA is an offshoot of Microsoft’s Visual Basic programming language that’s used across the Office suite and beyond, but it’s particularly potent when used in conjunction with Excel. VBA allows users to create macros that automate tasks, and otherwise do more with Excel.

As explained above, it’s certainly possible to find duplicates in Excel without learning VBA. However, if you find yourself needing to carry out this kind of task on a regular basis, it might be something that’s worth learning about. It’s an accessible language that can be used to great effect as part of the Microsoft Office suite, so check it out if you’re looking to expand your knowledge.

And remember, finding duplicates is just one scenario where conditional formatting comes in handy. You can also use it to highlight other types of data in a spreadsheet. Learn how to use conditional formatting in Excel and conditional highlighting in Numbers on Mac.

You might also want to learn how to add checkboxes, ratings, and sliders in Numbers and how to use formulas and functions in Numbers How to Use Formulas and Functions in Numbers on Mac How to Use Formulas and Functions in Numbers on Mac Use spreadsheets on your macOS? Learn how to use formulas and functions in Numbers to make your data easier to work with. Read More .

Explore more about: Microsoft Excel.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

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