Pinterest Stumbleupon Whatsapp
Ads by Google

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 7 Useful Excel Sheets to Instantly Improve Your Family's Budget 7 Useful Excel Sheets to Instantly Improve Your Family's Budget It's not always easy to set up a family budget, but these seven templates will give you the structure you need to get started. Read More or the people who subscribe to your newsletter Learn Something New with 10 Worth-It Email Newsletters Learn Something New with 10 Worth-It Email Newsletters You will be surprised at the quality of newsletters today. They are making a comeback. Subscribe to these ten fantastic newsletters and find out why. Read More , 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 This Access Database Tutorial Will Help You Get Started This Access Database Tutorial Will Help You Get Started Getting to grips with Microsoft Access? This guide will get your database up and running in no time at all. Once you understand the fundamentals, you'll be well on your way to Access mastery. Read More , 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.

Ads by Google

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 The Best Mac Apps of 2015 (And Our Favorite Updates) The Best Mac Apps of 2015 (And Our Favorite Updates) Brilliant calendar apps, feature-rich Twitter apps, a whole new Office experience, and a sassy way to find out the weather, were just some of the best OS X apps released or updated this year. Read More , 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 How to Whitelist Email Addresses in Outlook How to Whitelist Email Addresses in Outlook If you're using Outlook and are fed up with certain emails being automatically marked as junk, then whitelisting is the answer. Here's how to set that up with ease! Read More .

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 4 Mistakes You Can Avoid when Programming Excel Macros with VBA 4 Mistakes You Can Avoid when Programming Excel Macros with VBA Simple code and macros are the keys to Microsoft Excel superpowers. Even non-programmers can easily add impressive functionality to their spreadsheets with Virtual Basics for Applications (VBA). Just avoid these programming beginner mistakes! Read More 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.

Do you need help finding duplicates using this technique? Or do you have another tip for users that will supercharge their Excel experience? Head to the comments section below to join the conversat

Leave a Reply

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