How To Find Duplicate Values In Excel
The tutorial explains how to search for duplicates in Excel. You volition larn a few formulas to place indistinguishable values or find indistinguishable rows with or without first occurrences. You will besides larn how to count instances of each duplicate tape individually and find the total number of dupes in a column, how to filter out duplicates, and more.
While working with a big Excel worksheet or consolidating several small spreadsheets into a bigger one, you may find lots of indistinguishable rows in it. In one of our previous tutorials, nosotros discussed various means to compare two tables or columns for duplicates.
And today, I'd like to share a few quick and effective methods to identify duplicates in a unmarried listing. 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 detect duplicates in Excel is using the COUNTIF function. Depending on whether you want to discover duplicate values with or without start occurrences, in that location'south going to exist a slight variation in the formula equally shown in the following examples.
How to find duplicate records including 1st occurrences
Supposing you have a listing of items in column A that you want to cheque for duplicates. These tin can be invoices, product Id's, names or whatsoever other data.
Here's a formula to find duplicates in Excel including kickoff occurrences (where A2 is the topmost cell):
=COUNTIF(A:A, A2)>ane
Input the above formula in B2, then select B2 and drag the fill handle to re-create the formula downwardly to other cells:
As you lot tin see in the screenshot above, the formula returns TRUE for duplicate values and Faux for unique values.
Note. If you demand to detect duplicates in a range of cells rather than in an entire column, retrieve to lock that range with the $ sign. For example, to search for duplicates in cells A2:A8, utilize this formula:
=COUNTIF($A$2:$A$8, A2)>1
For a duplicate formula to return something more meaningful than the Boolean values of TRUE and FALSE, enclose it in the IF office and type whatsoever labels you want for duplicate and unique values:
=IF(COUNTIF($A$2:$A$8, $A2)>ane, "Duplicate", "Unique")
In case, you want an Excel formula to detect duplicates only, supercede "Unique" with an empty string ("") like this:
=IF(COUNTIF($A$two:$A$viii, $A2)>i, "Indistinguishable", "")
The formula will return "Duplicates" for indistinguishable records, and a blank cell for unique records:
How to search for duplicates in Excel without anest occurrences
In case you programme to filter or remove duplicates after finding them, using the to a higher place formula is non safe because it marks all identical records as duplicates. And if you lot desire to go along the unique values in your listing, then you cannot delete all duplicate records, you need to merely delete the 2nd and all subsequent instances.
So, let's modify our Excel duplicate formula by using absolute and relative cell references where appropriate:
=IF(COUNTIF($A$2:$A2, $A2)>ane, "Indistinguishable", "")
Equally you lot can see in the following screenshot, this formula does not identity the first occurrence of "Apples" every bit duplicate:
How to find example-sensitive duplicates in Excel
In situations when you need to place exact duplicates including the text case, use this generic array formula (entered by pressing Ctrl + Shift + Enter):
IF( SUM(( --Verbal(range, uppermost _cell)))<=ane, "", "Duplicate")
At the centre of the formula, you use the Exact function to compare the target jail cell with each cell in the specified range exactly. The result of this performance is an array of TRUE (match) and Simulated (non match), which is coerced to an array of ane'due south and 0's by the unary operator (--). Later on 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((--Verbal($A$ii:$A$viii,A2)))<=ane,"","Indistinguishable")
As shown in the screenshot below, it treats lowercase and uppercase equally different characters (APPLES is not identified as a duplicate):
How to discover indistinguishable rows in Excel
If your aim is to dedupe a table consisting of several columns, then you need a formula that tin cheque each cavalcade and identify only absolute duplicate rows, i.e. rows that have completely equal values in all columns.
Allow'southward consider the post-obit example. Supposing, you have club numbers in cavalcade A, dates in column B, and ordered items in cavalcade C, and you desire to observe duplicate rows with the same order number, date and item. For this, we are going to create a indistinguishable formula based on the COUNTIFS function that allows checking multiple criteria at a time:
To search for duplicate rows with 1st occurrences, use this formula:
=IF(COUNTIFS($A$two:$A$8,$A2,$B$two:$B$viii,$B2,$C$2:$C$8,$C2)>one, "Duplicate row", "")
The following screenshot demonstrates that the formula really locates only the rows that take identical values in all three columns. For instance, row 8 has the same club number and date equally rows two and 5, but a different item in cavalcade C, and therefore it is not marked as duplicate row:
To evidence indistinguishable rows without 1st occurrences, brand a piddling aligning to the in a higher place formula:
=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$B$ii:$B2,$B2,$C$2:$C2,$C2,) >i, "Duplicate row", "")
How to count duplicates in Excel
If you want to know the exact number of identical records in your Excel sheet, apply 1 of the post-obit formulas to count duplicates.
Count instances of each duplicate record individually
When you have a column with duplicated values, you may often need to know how many duplicates are there 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 first and A8 is the last item of the list:
=COUNTIF($A$ii:$A$8, $A2)
As demonstrated in the post-obit screenshot, the formula counts the occurrences of each item: "Apples" occurs iii times, "Green bananas" - 2 times, "Bananas" and "Oranges" simply once.
If you want to identify 1st, twond, 3rd, etc. occurrences of each item, use the following formula:
=COUNTIF($A$ii:$A2, $A2)
In a similar mode, you can count the occurrences of duplicated rows. The only difference is that yous will need to use the COUNTIFS office instead of COUNTIF. For example:
=COUNTIFS($A$2:$A$8, $A2, $B$two:$B$8, $B2)
Once the duplicate values are counted, you can hide unique values and only view duplicates, or vice versa. To do this, utilise Excel'due south motorcar-filter as demonstrated in the following example: How to filter out duplicates in Excel.
Count the full number of duplicates in a column(s)
The easiest fashion to count duplicates in a cavalcade is to employ whatsoever of the formulas nosotros used to place duplicates in Excel (with or without first occurrences). And then you tin count duplicate values by using the following COUNTIF formula:
=COUNTIF(range, "duplicate")
Where "duplicate" is the label you used in the formula that locates duplicates.
In this example, our duplicate formula takes the following shape:
=COUNTIF(B2:B8, "duplicate")
Another way to count duplicate values in Excel by using a more complex assortment formula. An reward of this arroyo is that information technology does non require a helper cavalcade:
=ROWS($A$two:$A$eight)-SUM(IF( COUNTIF($A$2:$A$viii,$A$2:$A$8)=ane,1,0))
Because it'due south an assortment formula, think to press Ctrl + Shift + Enter to consummate it. Also, please keep in mind that this formula counts all duplicate records, including commencement occurrences:
To find the total number of duplicate rows, embed the COUNTIFS role instead of COUNTIF in the above formula, and specify all of the columns you want to cheque for duplicates. For example, to count indistinguishable 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$viii,$A$2:$A$eight, $B$ii:$B$eight,$B$2:$B$viii)=1,one,0))
How to filter duplicates in Excel
For easier information analysis, you may want to filter your data to only display duplicates. In other situations, y'all may demand the contrary - hibernate duplicates and view unique records. Below you will find solutions for both scenarios.
How to show and hide duplicates in Excel
If y'all want to come across all duplicates at a glance, use 1 of the formulas to find duplicates in Excel that ameliorate suits your needs. Then select your table, 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 table. Just select all data and printing the Ctrl + T shortcut.
Afterward that, click the arrow 
in the header of the Duplicate column and check the "Indistinguishable row" box to show duplicates. If you lot want to filter out, i.due east. hide duplicates, select "Unique" to view simply unique records:
And now, you lot can sort duplicates by the cardinal cavalcade to grouping them for easier analysis. In this example, we can sort duplicate rows by the Order number column:
How to filter duplicates by their occurrences
If you want to prove 2nd, 3rd, or Nth occurrences of duplicate values, use the formula to count duplicate instances nosotros discussed earlier:
=COUNTIF($A$2:$A2, $A2)
And then utilise filtering to your table and select but the occurrence(s) you lot desire to view. For instance, you can filter the 2nd occurrences like in the following screenshot:
To display all duplicate records, i.e. occurrences greater than 1, click the filter arrow in the header of the Occurrences column (the cavalcade with the formula), and then click Number Filters > Greater Than.
Select "is greater than" in the kickoff box, type 1 in the box next to information technology, and click the OK push button:
In a like mode, you can show 2nd, 3rd and all subsequent duplicate occurrences. Only blazon the required number in the box next to "is greater than".
Highlight, select, articulate, delete, copy or move duplicates
After you've filtered duplicates like demonstrated to a higher place, you have a diversity of choices to bargain with them.
How to select duplicates in Excel
To select duplicates, including cavalcade headers, filter them, click on any filtered jail cell to select it, and and then printing Ctrl + A.
To select duplicate records without column headers, select the get-go (upper-left) cell, and printing Ctrl + Shift + End to extend the option to the last jail cell.
Tip. In nigh cases, the higher up shortcuts piece of work fine and select filtered (visible) rows but. In some rare cases, mostly on very large workbooks, both visible and invisible cells may become selected. To gear up this, use one of the higher up shortcuts first, and so press Alt + ; to select only visible cells, ignoring hidden rows.
How to clear or remove duplicates in Excel
To clear duplicates in Excel, select them, right click, so click Clear Contents (or click the Clear button > Clear Contents on the Dwelling tab, in the Editing grouping). This will delete the jail cell contents but, and you will have empty cells as the result. Selecting the filtered indistinguishable cells and pressing the Delete key will have the same effect.
To remove unabridged duplicate rows, filter duplicates, select the rows by dragging the mouse beyond the row headings, right click the option, and then choose Delete Row from the context menu.
How to highlight duplicates in Excel
To highlight duplicate values, select the filtered dupes, click the Fill colour push 
on the Home tab, in the Font group, and then select the color of your choosing.
Another way to highlight duplicates in Excel is using a born conditional formatting rule for duplicates, or creating a custom rule peculiarly tailored for your canvas. Experienced Excel users won't accept any problem with creating such a rule based on the formulas we used to check duplicates in Excel. If y'all are not very comfortable with Excel formulas or rules nonetheless, you will find the detailed steps in this tutorial: How to highlight duplicates in Excel.
How to copy or motility duplicates to some other sail
To copy duplicates, select them, press Ctrl + C, then open up some other canvass (a new or existing one), select the upper-left jail cell of the range where yous desire to re-create the duplicates, and press Ctrl + 5 to paste them.
To motion duplicates to another sail, perform the aforementioned steps with the only departure that y'all press Ctrl + X (cut) instead of Ctrl + C (re-create).
This is how you can place duplicates in Excel using functions and born features. To improve understand the formulas discussed in this tutorial, feel gratuitous to download Identify Duplicates Sample Workbook.
Duplicate Remover - fast and efficient way to locate duplicates in Excel
Now that y'all know how to employ 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 column or compare ii columns. It tin can notice, select and highlight duplicate records or entire duplicate rows, remove found dupes, re-create or move them to another sheet. I think an instance of applied apply is worth many words, and then let's get to information technology.
How to find indistinguishable rows in Excel in two quick steps
To examination the capabilities of our Duplicate Remover add together-in, I've created a table with a few hundred rows that looks like follows:
Equally you come across, the table has a few columns. The starting time 3 columns incorporate the most relevant information, then we are going to search for duplicate rows based solely on the data in columns A - C. To detect duplicate records in these columns, just practice the following:
- Select any prison cell within your table and click the Dedupe Tabular array button on the Excel ribbon. After installing our Ultimate Suite for Excel, you will find it on the Ablebits Data tab, in the Dedupe group.
- The smart add-in will pick up the unabridged table and ask you lot to specify the post-obit 2 things:
- Select the columns to check for duplicates (in this instance, these are the Lodge no., Guild date and Particular 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 array of other options are available to you:
- Delete duplicates
- Color (highlight) duplicates
- Select duplicates
- Re-create duplicates to a new worksheet
- Motion duplicates to a new worksheet
Click the OK push and await for a few seconds. Done!
As you tin run into in the below screenshot, all of the rows that have identical values in the first 3 columns have been located (offset occurrences are not identified as duplicates).
If you lot desire more than options to dedupe your worksheets, employ the Duplicate Remover sorcerer that can discover duplicates with or without commencement occurrences likewise as unique values. The detailed steps follow beneath.
Duplicate Remover wizard - more options to search for duplicates in Excel
Depending on a detail sheet yous are working with, you may or may non want to care for the showtime instances of identical records as duplicates. One possible solution is using a different formula for each scenario, equally we discussed in How to place duplicates in Excel. If you are looking for a fast, authentic and formula-free method, try the Indistinguishable Remover magician:
- Select whatsoever cell inside your table and click the Duplicate Remover button on the Ablebits Information tab. The wizard volition run and the entire tabular array will become selected.
- On the next footstep, you are presented with the 4 options to check duplicates in your Excel canvas:
- Duplicates without 1st occurrences
- Duplicates with 1st occurrences
- Unique values
- Unique values and 1st duplicate occurrences
For this example, permit's go with the second option, i.e. Duplicates + 1st occurrences:
- Now, select the columns where you desire to check duplicates. Like in the previous instance, we are selecting the first 3 columns:
- Finally, choose an action you want to perform on duplicates. Equally is the case with the Dedupe Table tool, the Duplicate Remover sorcerer tin can identify, select, highlight, delete, copy or motion duplicates.
Because the purpose of this tutorial is to demonstrate unlike ways to identify duplicates in Excel, let's check the corresponding option and click Finish:
It only takes a fraction of a 2nd for the Indistinguishable Remover wizard to check hundreds of rows, and deliver the following result:
No formulas, no stress, no errors - always swift and impeccable results :)
If you are interested to try these tools to detect duplicates in your Excel sheets, you lot are most welcome to download an evaluation version below. Your feedback in comments will be profoundly appreciated!
Bachelor downloads
Identify Duplicates - formula examples (.xlsx file)
Ultimate Suite - trial version (.zip file)
You may likewise be interested in
Source: https://www.ablebits.com/office-addins-blog/2016/03/02/identify-duplicates-excel/
Posted by: davisexter1987.blogspot.com
0 Response to "How To Find Duplicate Values In Excel"
Post a Comment