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.
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.
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.
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.
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 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:
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.
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.
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.
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.
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.
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.
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.
Explore more about: Microsoft Excel.