It often happens that when you are working with a large Excel sheet (spreadsheet), many duplicate rows and columns appear. When trying to group these rows and columns you need to work hard because duplicate data is such a real pain that nobody wants to bear.
With Excel, you can collect, manipulate, analyze, filter, and transform data in whatever way you need. Finding duplicate data is not an easy thing and meanwhile, it is not rocket science. We have come up with multiple approaches that let you use a formula to find duplicates in Excel and once you identify duplicates, you can do whatever you want.
Using Formula to Find Duplicates in Excel
With Excel formulas, you can identify duplicate values easily without being in any trouble. Once you learn how to use formulas, you will be able to understand the usage of other formulas as well, such as =CONCATENATE.
COUNTIF Formula to Find Duplicate Values
With the =COUNTIF formula you can check duplicate values as it lets you count cells that contain repeated data on the sheet. You will find the resulting values as “TRUE” or “FALSE”.
TRUE values indicate duplicate data and FALSE values show non-duplicate data. You can add a heading “Count” or anything similar into an empty column. Well, it is not a mandatory thing to do, you can skip it if you want. Below are some easy-to-follow steps that you need to follow:
In the first cell of an empty column, copy and paste the formula: =COUNTIF(A: A, A1)>1
In the formula, you may add A to coincide with the letter of the cell column that needs to find duplicates.
Now, drag the cell data down to the cells below.
All done!
In the new column, you will find all the duplicate values as “TRUE”, whereas “FALSE” identifies non-duplicate values. In the next step, you can organize data by your count column to see all the duplicates grouped together.
In case, when you need to make changes in the value results, this generic formula is available. Suppose, you need to find the duplicate data as “Duplicate” rather than by default terms “TRUE” “UNIQUE” or “FALSE”.
Or else you can delete >1 to check the number of copies that could be more than two.
Using VLOOKUP Formula to Find Duplicates in Excel
When your dataset contains two data columns, you can use this approach. VLOOKUP is another great option to utilize for comparing two separate columns when you have combined data and showing the common things in a third column.
In a third empty column, you may add a heading such as VLOOKUP. So, let’s get started:
- Copy and paste =VLOOKUP(B2, $A$2:$A$14, 1, FALSE) in the second cell of the third empty column.
- You can add cell letters and numbers to fit data.
- Now, drag the original cell down to all the other rows.
- Non-duplicate data in columns A and B are displayed as “#N/A” with the VLOOKUP formula. In the next step, you can delete duplicate data or keep it.
How to Identify Duplicates in Excel?
Let’s understand this phenomenon with a few simple steps:
- You can make a named range for the columns. It is optional.
- Click on the Data and choose the Conditional Formatting button given under the Home Ribbon.
- Choose the Duplicate Values option given under the Highlight Cell Rules.
- Click on the Duplicate Values placed under Highlight Cell Rules in the Conditional Formatting option.
- Press OK.
- Now, you will see all the duplicates are highlighted in Excel.
Summary
In this post, you have learned how formulas can be used to find duplicate values in Excel. Only practice can help you learn things faster. Therefore, you need to make sure you have used formulas correctly.
