How Do You Find Duplicates In Excel
The tutorial explains how to search for duplicates in Excel. You volition acquire a few formulas to place duplicate values or find indistinguishable rows with or without offset occurrences. You will too learn how to count instances of each duplicate record individually and find the total number of dupes in a cavalcade, how to filter out duplicates, and more.
While working with a large Excel worksheet or consolidating several modest spreadsheets into a bigger one, you may find lots of duplicate rows in it. In one of our previous tutorials, we discussed diverse ways to compare two tables or columns for duplicates.
And today, I'd similar to share a few quick and effective methods to place duplicates in a single list. These solutions work in all versions of Excel 365, Excel 2019, Excel 2016, Excel 2013 and lower.
How to identify duplicates in Excel
The easiest way to observe duplicates in Excel is using the COUNTIF function. Depending on whether you desire to find indistinguishable values with or without kickoff occurrences, in that location's going to be a slight variation in the formula as shown in the following examples.
How to find duplicate records including ist occurrences
Supposing yous have a list of items in cavalcade A that you want to check for duplicates. These can be invoices, product Id's, names or any other data.
Here'due south a formula to discover duplicates in Excel including kickoff occurrences (where A2 is the topmost cell):
=COUNTIF(A:A, A2)>1
Input the above formula in B2, then select B2 and drag the fill up handle to copy the formula downward to other cells:
Equally you tin can meet in the screenshot in a higher place, the formula returns Truthful for duplicate values and FALSE for unique values.
Annotation. If you need to observe duplicates in a range of cells rather than in an entire cavalcade, call up to lock that range with the $ sign. For example, to search for duplicates in cells A2:A8, use this formula:
=COUNTIF($A$two:$A$8, A2)>1
For a duplicate formula to return something more meaningful than the Boolean values of TRUE and Simulated, enclose it in the IF role and type any labels you lot want for duplicate and unique values:
=IF(COUNTIF($A$two:$A$viii, $A2)>ane, "Indistinguishable", "Unique")
In case, you want an Excel formula to find duplicates just, supplant "Unique" with an empty cord ("") like this:
=IF(COUNTIF($A$two:$A$eight, $A2)>1, "Duplicate", "")
The formula will return "Duplicates" for duplicate records, and a blank cell for unique records:
How to search for duplicates in Excel without 1st occurrences
In case you plan to filter or remove duplicates after finding them, using the above formula is not safe because it marks all identical records as duplicates. And if you want to go along the unique values in your listing, so yous cannot delete all duplicate records, you demand to simply delete the 2nd and all subsequent instances.
Then, let'due south modify our Excel duplicate formula by using accented and relative cell references where advisable:
=IF(COUNTIF($A$ii:$A2, $A2)>one, "Duplicate", "")
Equally you can see in the following screenshot, this formula does non identity the first occurrence of "Apples" equally duplicate:
How to observe case-sensitive duplicates in Excel
In situations when you demand to identify exact duplicates including the text case, employ this generic array formula (entered by pressing Ctrl + Shift + Enter):
IF( SUM(( --EXACT(range, uppermost _cell)))<=ane, "", "Indistinguishable")
At the middle of the formula, you use the Verbal function to compare the target cell with each cell in the specified range exactly. The issue of this operation is an array of TRUE (match) and Faux (not match), which is coerced to an assortment of one's and 0'due south by the unary operator (--). After that, the SUM function adds up the numbers, and if the sum is greater than 1, the IF role reports a "Duplicate".
For our sample dataset, the formula goes as follows:
=IF(SUM((--Exact($A$2:$A$8,A2)))<=1,"","Indistinguishable")
Equally shown in the screenshot below, it treats lowercase and uppercase as different characters (APPLES is not identified every bit a indistinguishable):
How to find duplicate rows in Excel
If your aim is to dedupe a table consisting of several columns, and so you lot need a formula that tin check each column and identify simply accented duplicate rows, i.eastward. rows that take completely equal values in all columns.
Let's consider the post-obit example. Supposing, you have order numbers in column A, dates in column B, and ordered items in column C, and you want to notice duplicate rows with the same order number, date and item. For this, we are going to create a duplicate formula based on the COUNTIFS function that allows checking multiple criteria at a fourth dimension:
To search for duplicate rows with 1st occurrences, apply this formula:
=IF(COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2,$C$two:$C$8,$C2)>1, "Duplicate row", "")
The post-obit screenshot demonstrates that the formula really locates merely the rows that have identical values in all 3 columns. For case, row eight has the same social club number and date as rows ii and 5, only a dissimilar item in column C, and therefore information technology is not marked equally duplicate row:
To testify duplicate rows without 1st occurrences, make a fiddling adjustment to the above formula:
=IF(COUNTIFS($A$ii:$A2,$A2,$B$2:$B2,$B2,$B$2:$B2,$B2,$C$2:$C2,$C2,) >i, "Duplicate row", "")
How to count duplicates in Excel
If you want to know the verbal number of identical records in your Excel sheet, use one of the post-obit formulas to count duplicates.
Count instances of each duplicate record individually
When you take a cavalcade with duplicated values, you may often need to know how many duplicates are in that location for each of those values.
To find out how many times this or that entry occurs in your Excel worksheet, use a simple COUNTIF formula, where A2 is the start and A8 is the concluding detail of the list:
=COUNTIF($A$2:$A$8, $A2)
Equally demonstrated in the following screenshot, the formula counts the occurrences of each particular: "Apples" occurs three times, "Dark-green bananas" - 2 times, "Bananas" and "Oranges" just once.
If you want to place 1st, iind, 3rd, etc. occurrences of each particular, utilize the following formula:
=COUNTIF($A$ii:$A2, $A2)
In a similar style, yous can count the occurrences of duplicated rows. The merely difference is that y'all will need to use the COUNTIFS part instead of COUNTIF. For example:
=COUNTIFS($A$2:$A$8, $A2, $B$2:$B$8, $B2)
Once the indistinguishable values are counted, you can hide unique values and only view duplicates, or vice versa. To practice this, utilize Excel's motorcar-filter as demonstrated in the following instance: How to filter out duplicates in Excel.
Count the total number of duplicates in a column(s)
The easiest fashion to count duplicates in a column is to apply any of the formulas we used to identify duplicates in Excel (with or without first occurrences). And then y'all can count duplicate values by using the following COUNTIF formula:
=COUNTIF(range, "duplicate")
Where "indistinguishable" is the label you used in the formula that locates duplicates.
In this case, our duplicate formula takes the following shape:
=COUNTIF(B2:B8, "indistinguishable")
Another mode to count duplicate values in Excel past using a more than complex array formula. An advantage of this approach is that it does not require a helper column:
=ROWS($A$2:$A$8)-SUM(IF( COUNTIF($A$2:$A$eight,$A$2:$A$8)=1,1,0))
Because it's an array formula, remember to press Ctrl + Shift + Enter to complete it. Also, please keep in mind that this formula counts all duplicate records, including outset occurrences:
To find the total number of indistinguishable rows, embed the COUNTIFS function instead of COUNTIF in the above formula, and specify all of the columns you want to check for duplicates. For example, to count duplicate rows based on columns A and B, enter the following formula in your Excel sheet:
=ROWS($A$2:$A$8)-SUM(IF( COUNTIFS($A$2:$A$eight,$A$two:$A$viii, $B$2:$B$8,$B$ii:$B$viii)=1,ane,0))
How to filter duplicates in Excel
For easier data analysis, yous may desire to filter your data to only display duplicates. In other situations, you may demand the opposite - hibernate duplicates and view unique records. Below you will discover solutions for both scenarios.
How to prove and hide duplicates in Excel
If you want to see all duplicates at a glance, use one of the formulas to notice duplicates in Excel that ameliorate suits your needs. And then select your tabular array, switch to the Data tab, and click the Filter push button. Alternatively, you can click Sort & Filter > Filter on the Home tab in the Editing group.
Tip. To have filtering enabled automatically, convert your data to a fully-functional Excel tabular array. Just select all information and printing the Ctrl + T shortcut.
After that, click the pointer 
in the header of the Indistinguishable cavalcade and bank check the "Duplicate row" box to evidence duplicates. If you want to filter out, i.eastward. hibernate duplicates, select "Unique" to view but unique records:
And at present, you can sort duplicates past the primal column to group them for easier analysis. In this case, we tin can sort duplicate rows by the Order number cavalcade:
How to filter duplicates past their occurrences
If y'all want to show 2nd, 3rd, or Nth occurrences of duplicate values, use the formula to count duplicate instances we discussed earlier:
=COUNTIF($A$2:$A2, $A2)
And then utilise filtering to your table and select only the occurrence(southward) you desire to view. For instance, you tin filter the 2nd occurrences similar in the following screenshot:
To brandish all duplicate records, i.e. occurrences greater than 1, click the filter arrow in the header of the Occurrences column (the column with the formula), so click Number Filters > Greater Than.
Select "is greater than" in the first box, type ane in the box next to it, and click the OK button:
In a similar manner, you tin evidence twond, 3rd and all subsequent duplicate occurrences. Just blazon the required number in the box next to "is greater than".
Highlight, select, clear, delete, copy or move duplicates
After you've filtered duplicates like demonstrated above, yous have a variety of choices to deal with them.
How to select duplicates in Excel
To select duplicates, including cavalcade headers, filter them, click on whatsoever filtered prison cell to select it, and then press Ctrl + A.
To select duplicate records without column headers, select the first (upper-left) cell, and press Ctrl + Shift + End to extend the option to the final cell.
Tip. In most cases, the higher up shortcuts work fine and select filtered (visible) rows only. In some rare cases, mostly on very large workbooks, both visible and invisible cells may become selected. To set this, use i of the above shortcuts beginning, so press Alt + ; to select only visible cells, ignoring hidden rows.
How to clear or remove duplicates in Excel
To articulate duplicates in Excel, select them, right click, and and then click Clear Contents (or click the Clear push > Clear Contents on the Domicile tab, in the Editing group). This will delete the cell contents only, and you will have empty cells as the upshot. Selecting the filtered duplicate cells and pressing the Delete key will have the aforementioned effect.
To remove entire duplicate rows, filter duplicates, select the rows past dragging the mouse across the row headings, right click the selection, and then choose Delete Row from the context card.
How to highlight duplicates in Excel
To highlight duplicate values, select the filtered dupes, click the Fill up color push 
on the Dwelling house tab, in the Font group, and then select the colour of your choosing.
Some other way to highlight duplicates in Excel is using a built-in conditional formatting rule for duplicates, or creating a custom dominion specially tailored for your sheet. Experienced Excel users won't accept any trouble with creating such a rule based on the formulas we used to cheque duplicates in Excel. If y'all are not very comfy with Excel formulas or rules nonetheless, y'all will discover the detailed steps in this tutorial: How to highlight duplicates in Excel.
How to re-create or movement duplicates to some other sheet
To re-create duplicates, select them, press Ctrl + C, then open up another sheet (a new or existing one), select the upper-left cell of the range where you want to re-create the duplicates, and press Ctrl + V to paste them.
To move duplicates to another sail, perform the aforementioned steps with the only deviation that you press Ctrl + X (cut) instead of Ctrl + C (copy).
This is how you can identify duplicates in Excel using functions and built-in features. To better understand the formulas discussed in this tutorial, feel free to download Place Duplicates Sample Workbook.
Indistinguishable Remover - fast and efficient way to locate duplicates in Excel
At present that y'all know how to utilize duplicate formulas in Excel, let me demonstrate you another quick, efficient and formula-free way - Duplicate Remover for Excel.
This all-in-one tool can search for duplicate or unique values in a single cavalcade or compare two columns. It tin can find, select and highlight duplicate records or entire duplicate rows, remove found dupes, copy or motility them to another sheet. I think an example of applied use is worth many words, and then let'southward become to it.
How to discover duplicate rows in Excel in 2 quick steps
To test the capabilities of our Indistinguishable Remover add-in, I've created a tabular array with a few hundred rows that looks like follows:
Every bit you see, the table has a few columns. The first three columns comprise the most relevant information, then we are going to search for duplicate rows based solely on the information in columns A - C. To find duplicate records in these columns, but do the following:
- Select any cell within your table and click the Dedupe Table button on the Excel ribbon. Later installing our Ultimate Suite for Excel, you volition find it on the Ablebits Data tab, in the Dedupe group.
- The smart add-in will pick up the unabridged table and inquire you lot to specify the following two things:
- Select the columns to check for duplicates (in this example, these are the Society no., Lodge appointment and Item columns).
- Choose an action to perform on duplicates. Because our purpose is to identify duplicate rows, I've selected the Add a status column
Apart from adding a status cavalcade, an assortment of other options are available to you:
- Delete duplicates
- Color (highlight) duplicates
- Select duplicates
- Copy duplicates to a new worksheet
- Movement duplicates to a new worksheet
Click the OK button and await for a few seconds. Done!
As you can see in the below screenshot, all of the rows that have identical values in the beginning 3 columns have been located (starting time occurrences are not identified every bit duplicates).
If y'all want more than options to dedupe your worksheets, employ the Indistinguishable Remover wizard that can find duplicates with or without first occurrences as well as unique values. The detailed steps follow beneath.
Duplicate Remover magician - more options to search for duplicates in Excel
Depending on a detail sheet you are working with, you may or may not want to treat the first instances of identical records every bit duplicates. 1 possible solution is using a different formula for each scenario, as we discussed in How to place duplicates in Excel. If you are looking for a fast, accurate and formula-free method, effort the Indistinguishable Remover sorcerer:
- Select any cell within your table and click the Indistinguishable Remover button on the Ablebits Data tab. The magician will run and the entire table will become selected.
- On the next step, you lot are presented with the 4 options to cheque duplicates in your Excel sheet:
- Duplicates without 1st occurrences
- Duplicates with 1st occurrences
- Unique values
- Unique values and 1st indistinguishable occurrences
For this instance, permit's go with the second selection, i.eastward. Duplicates + 1st occurrences:
- Now, select the columns where you want to bank check duplicates. Like in the previous case, we are selecting the first iii columns:
- Finally, choose an action you want to perform on duplicates. As is the example with the Dedupe Table tool, the Indistinguishable Remover magician tin can identify, select, highlight, delete, copy or move duplicates.
Because the purpose of this tutorial is to demonstrate dissimilar ways to identify duplicates in Excel, let'due south bank check the respective selection and click Finish:
It simply takes a fraction of a second for the Duplicate Remover wizard to cheque hundreds of rows, and evangelize the following effect:
No formulas, no stress, no errors - always swift and impeccable results :)
If yous are interested to attempt these tools to detect duplicates in your Excel sheets, you are most welcome to download an evaluation version below. Your feedback in comments will exist greatly appreciated!
Bachelor downloads
Place Duplicates - formula examples (.xlsx file)
Ultimate Suite - trial version (.naught file)
You may besides be interested in
Source: https://www.ablebits.com/office-addins-blog/2016/03/02/identify-duplicates-excel/
Posted by: thomasprel1989.blogspot.com
0 Response to "How Do You Find Duplicates In Excel"
Post a Comment