how to find duplicates in excel

How to Find duplicates in Excel or Remove Exact Data?

While working on Large data, “how to find duplicates in Excel” is a fast and efficient way to understand the data. Conditional formatting can help you highlight duplicate data with a comprehensive review of duplicated entries.

Let’s learn how to find and remove your duplicates if you want to clean up your data for its proper analysis, with a well-informed approach to identify duplicate data or locate duplicate rows irrespective of their data frequency. Moreover, you will be learning how to count unique data number of frequency duplicated entries with a total number of drupes in a column and how you can filter your duplicate data range to make it worth valuable.

How to Find Duplicates in Excel

  1. Find and highlight the Data you intend to check for duplicates.
  2. Then go to the “Home” tab, and search for the “Styles” section.
  3. Now select Conditional Formatting that opens up the drop-down menu.
  4. Then Highlight Cells Rules and opt for “Duplicate Values…”.
  5. Here, duplicate values will let you open up the dialog box to format your cells containing duplicates. Under the head of “values with,” choose the formatting style you want to apply to duplicated values. Here you can identify the color that differentiates duplicates from other data. It then spreads to a selected range
  6. Finally, click “OK.”

Use COUNTIF Formula to locate Duplicates in Excel

Alternately “COUNTIF” function can be handy to spot duplicate entries. Depending on the number of frequencies, you can even modify it with some slight amendments in the given formula.

  1. Find duplicated data with its first occurrence
  2. Find duplicated data without its first occurrence
  3. Locate case-sensitive duplicates in Excel
  4. Search for duplicate rows with the first occurrence
  5. Find duplicated rows without the first occurrence
  6. Count individual duplicate entries individually
  7. Count the Total no of duplicates in a column

Find Duplicates with or without their first occurrence in Excel

If you’ve got a list of company invoices where you want to find duplicates, including their first occurrence, then use the COUNTIF formula specifying the column A: =COUNTIF(A:A, A2)>1.

  • Type the above given COUNTIF formula to identify the duplicates in “B2,” then drag the fill handle to copy the formula to the rest of the datasheet. TRUE represent duplicates while FALSE stays unique entries.
  • You can even lock the range of specific cells using “$” that searches for copied data in cell range A2:A8 with =COUNTIF($A$2:$A$8, A2)>1

Use Boolean value to indicate duplicates

You can otherwise use Boolean values of TRUE and FALSE with “IF Function with label: Duplicates & Unique” like =IF(COUNTIF($A$2:$A$8, $A2)>1, “Duplicate“, “Unique“)

Replace empty strings with “Unique” Boolean value

Alternately, you can opt to replace “Unique” with empty string (“”) like: =IF(COUNTIF($A$2:$A$8, $A2)>1, “Duplicate”, “”)

Find Duplicates without their first occurrence in Excel

Navigating duplicate data to filter or remove using a formula can be sometimes unsafe since it spots all matching data as duplicates. To keep your unique data set intact, delete the second and all forthcoming entries when you can’t eliminate the copied data. Finally, modification are hereby mentioned using Relative and Absolute cell reference where it’s necessary.

=IF(COUNTIF($A$2:$A2, $A2)>1, “Duplicate”, “”)

Already visible in the screenshot above, it excludes the initial occurrence of “Pen” as “Duplicate.”

Locate Case–Sensitive duplicates in Excel

If you want to detect the exact duplicates using its text case, then type the generic array formula by pressing “Ctrl + Shift + Enter.”

  • “EXACT” formula at the center of this array helps you compare the specific “A2” cell with other cells specified in the range.
  • Under the binary array command of the unary operator (–), the TRUE represents {Duplicate} whereas FALSE indicates {Unique}
  • The SUM function will add up the number if it exceeds 1, then the IF function will indicate it “Duplicate.”
  • Text case does have an effect as apparent in the given screenshot represent it as unique value like (Paper or PAPER) are different considering its lower or uppercase in the date range.

How to Find duplicate rows with the first occurrence in Excel

If your data set consists of multiple columns with identical values to detect the absolute duplicate rows, then you can check for each column.

For instance, consider data set comprising order number, date, and items if you’ve duplicated data in all columns based on COUNTIFS Function that lets you evaluate multiple criteria simultaneously. If you want to locate duplicate rows with the first occurrence, then follow:

=IF(COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2,$C$2:$C$8,$C2)>1, “Duplicate row”, “”)

  • It depicts identical data in two rows with similarities in all the columns. The 8th row indicates the exact items with the same order number & date as the 1st row.
  • The 4th & 5th rows eventually depict no duplication w.r.t 9th & 10th rows because dates changes; therefore, consider it unique entries.

Search for duplicate rows without the first occurrence in Excel

Let’s consider similar instances for duplicate rows without first occurrence data entries.

Count individual Duplicate entries consecutively

It is somewhat helpful to search for duplicate data entries if you want to count on your Excel extensive datasheets. It usually allows calculating how many duplicated entries are for one of those values.

Let’s learn how to determine it using the given quick “COUNTIF” Formula: =COUNTIF($A$2:$A$8, $A2)

  • The occurrences based on the 1st, 2nd, or 3rd every time, use the following formula: =COUNTIF($A$2:$A2, $A2)

  • If you’re willing to count the number of frequencies for duplicated rows, then use COUNTIFS Function to apply it to multiple rows.

=COUNTIFS($A$2:$A$8, $A2, $B$2:$B$8, $B2)

  • As soon as you count on duplicate values, hiding uniques with identical values visibility can get easily accessed via Auto-Filter Feature.

Count the total no of duplicates in a column

To count on the total number of frequencies, you can use two different ways to apply COUNTIF Formula.

  • Count if formula without first occurrence :

=IF(COUNTIF($A$2:$A2, A2)>1, “Duplicate”, “”)

=COUNTIF(B2:B9, “Duplicate”)

  • Complex Array Formula to count identical values, press “Ctrl + Shift + Enter” to create the Array function. It includes all duplicate values with the first occurrences.

=ROWS($A$2:$A$8)-SUM(IF( COUNTIF($A$2:$A$8,$A$2:$A$8)=1,1,0))

  • To calculate the Total No Of Duplicate Rows, COUNTIFS Formula is used along with all the columns to verify the duplicates. Columns A & B are taken for the count on duplicate entries using Array Formula. Press “Ctrl + Shift-Enter” to apply the Array formula.

=ROWS($A$2:$A$9)-SUM(IF( COUNTIFS($A$2:$A$9,$A$2:$A$9, $B$2:$B$9,$B$2:$B$9)=1,1,0))

Use Filter to uncover the Duplicates Entries

Filtering your identical entries seems one of the easiest ways to display your duplicates without formula input. You can even display unique values instead for convenient data analysis. Let’s learn how to hide and show duplicate values.

  1. To uncover the duplicate values, use the multiple columns COUNTIF formula: =IF(COUNTIFS($A$15:$A22,A15,$B$15:$B22,B15,$C$15:$C22,C15)>1,”Duplicate Row”, “”)
  2. Then select the range of your data set and go to Data > Filter. Alternatively, you can opt for Sort & Filter > Filter via the Edit group.
  3. Automatic Table via “Ctrl+T” shortcut can also be a helpful option to enable the automatic Filter button. Select your Table, then click on the header Filter arrow to reveal the Duplicate or unique value by clicking on the check box. Just click on the “Duplicate Row,” and it will uncover all the data entries using their order and date.

  1. You can sort by order number or date-wise as well. The screenshot for the sorted Data is for an explanation.

Filter the Duplicate Data Set by their Occurrence

Apply the formula first if you intend to display your Duplicate with the 2nd, 3rd, or the nth number of occurrences. Then filter out your duplicate record “greater than 1″ event.

  • Click on the Filter arrow at the table header. Then click Number Filters> Greater Than.
  • Custom Auto Filter dialog box pops up immediately where you have to select “is greater than” and then type the number of occurrences in the typing box. Then click “OK.”

How To Remove the Duplicate Values from Your Datasheet

“Remove Duplicates” helps you delete your duplicated Data permanently. It is better to back up your data to another worksheet before you intend to remove your duplicates. That’s how you can prevent accidental loss of your valuable information.

  1. To clean up your data, first, highlight the duplicate data set.
  2. Now, select Data > Remove Duplicates
  3. Then under the Columns head, check for the column where you want to apply the “remove duplicates” option.

Remove Duplicates

As a result, the confirmation dialog box tells the Data displays three duplicate values removed, keeping five uniques items in the datasheet.

Conclusion

Finally, I’m wrapping up our thorough Excel tutorial, where you should have learned how to find and eliminate duplicates in Excel. Keep us updated in the comments section if it is of assistance, and please share it with your friends.

Leave a Comment