Table of Contents

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

- Find and highlight the Data you intend to check for duplicates.
- Then go to the
tab, and search for the “*“Home”**Styles” section.* - Now select
that opens up the drop-down menu.*Conditional Formatting* - Then
and opt for “*Highlight Cells Rules**Duplicate Values…”.* - Here, duplicate values will let you open up the dialog box to format your cells containing duplicates. Under the head of “
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*values with,”* - 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.

- Find duplicated data with its first occurrence
- Find duplicated data without its first occurrence
- Locate case-sensitive duplicates in
*Excel* - Search for duplicate rows with the first occurrence
- Find duplicated rows without the first occurrence
- Count individual duplicate entries individually
- Count the
*Total*no of duplicates in a column

**Find ***Duplicates* with or without their first occurrence in Excel

*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
formula to identify the duplicates in “*COUNTIF*” then drag the fill handle to copy the formula to the rest of the datasheet. TRUE represent duplicates while FALSE stays unique entries.*B2,* - 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

*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

*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 8
^{th}row indicates the exact items with the same order number & date as the 1^{st}row. - The 4
^{th}& 5^{th}rows eventually depict no duplication w.r.t 9^{th}& 10^{th}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 1
^{st}, 2^{nd,}or 3^{rd}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.

- 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”, “”)** - Then select the range of your data set and go to
. Alternatively, you can opt for*Data > Filter**Sort & Filter > Filter via the Edit group.* *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.

- 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*

*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.

- To clean up your data, first, highlight the duplicate data set.
- Now, select
>*Data**Remove Duplicates* - Then under the
head, check for the*Columns*where you want to apply the “*column*” 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.