Seeing Double? No Problem: Four Ways to Tackle Excel Duplicates
We will cover four ways to identify duplicate data in Excel without making a trip to the eye doctor.
Want to get the most out of this newsletter? Download the sample data so that you can follow along!
Ever feel like you’re seeing double? In Excel this happens all the time. Don’t worry - it’s a completely solvable problem and I promise you that you won’t need to visit the eye doctor. I am going to share with you four different ways to pluck out those doubles, triples, quadruples and more. This will help you identify out the data quickly so that you can focus on what is most important - getting the job done and spending time doing other things.
Find Duplicates in Columns
The easiest method to finding duplicates is to use Excel’s built in conditional formatting function. To compare two columns and highlight duplicates, follow these steps:
Select the two separate ranges using Ctrl + Shift + Down.
Go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
Choose your preferred formatting option and click OK.
Find Duplicate Rows
Easy enough, right? The problem is that we often get data in multiple columns. So what can we do? In the case below we have a list of First names & Last names. Since we often see people have the same of both we will want to join them together first and then find the duplicate rows based on the entire row's content. To do this just follow these steps:
Create a unique key for each row using the CONCAT function.
Apply conditional formatting to highlight duplicate values.
Note: You can also hide the unique key column by applying a custom number format (three semicolons) or by grouping the column.
Excel Formulas to Find Duplicates
Use formulas to find duplicates based on a single column or entire row:
Use the COUNTIF function to find the number of occurrences of a value.
Build on the COUNTIF formula to return TRUE or FALSE values for duplicates. Use “>1” to show True for every duplicate answer.
Lets say in column “B” you want to find duplicates in rows 2-45.
The syntax would look like this: =COUNTIF($B$2:$B$45, B2)>1
NOTE: You can also Combine CONCAT and COUNTIF to find duplicate rows based on the entire row's content.
Use Formulas to Create a Duplicate List
Let’s say we want to return duplicate values in a separate list, we can use the FILTER function. For this example we will use the same list as above.
Start with the FILTER function to return your data set.
Then we want to nest the COUNTIF function to define the criteria for inclusion based on the values within the column.
Finally we want to use the UNIQUE function to remove the duplicates when the data returns.
The Syntax will look like this:
=UNIQUE(FILTER(B2:B45, COUNTIF(B2:B42, B2:B45)>1))
By mastering these methods, you can efficiently identify duplicates in Excel, making your data analysis more accurate and reliable. Not to mention save your eyes!
I hope you enjoyed this newsletter. There are a few other ways I can help you master Excel:
Want to learn more Excel Shortcuts? I have created something special (and free) for you: The BEST DAMN Excel Shortcut Sheet. PERIOD.
Do you have any burning questions about Excel that you want answered? Send me your question here. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you like these tips & tricks please share this newsletter with a friend or two...