How to find duplicate rows

Answer

To find duplicate rows in Excel, use Conditional Formatting with COUNTIFS formulas, the Remove Duplicates feature under the Data tab, or helper columns with COUNTIF/COUNTIFS functions. Each method serves different purposes: Conditional Formatting highlights duplicates visually, Remove Duplicates deletes them permanently, and helper columns flag duplicates for filtering without altering original data.

How to find duplicate rows using conditional formatting

To find duplicate rows using Conditional Formatting, apply a COUNTIFS formula to your data range that counts matches across all columns and highlights rows appearing more than once. This method requires no helper column and provides instant visual identification.

Step-by-step process:

1. Select your full data range (e.g., A2:D100).
2. Navigate to Home > Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format."
4. Enter the formula: =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2,$D$2:$D$100,$D2)>1
5. Click Format and set a fill color (e.g., red).
6. Click OK to apply the rule.

Duplicate rows will highlight immediately across your dataset.

Why COUNTIFS works for multi-column duplicates

COUNTIFS checks exact matches across multiple fields simultaneously, unlike the basic "Duplicate Values" option that only scans single cells or columns. The formula compares each column's value against all rows in that column, returning a count greater than 1 for rows with identical matches across all specified fields.

Alternative formulas for Conditional Formatting:

The SUMPRODUCT function provides another option for targeting specific columns: =SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100=B2)*($C$2:$C$100=C2))>1. This approach works well for datasets with unique identifiers where you need to match only key columns like ID and name.

For Excel 365 users, TEXTJOIN offers cleaner syntax with many columns by concatenating row values first in a helper column using =COUNTIF($E$2:$E$100,TEXTJOIN(",",TRUE,$A2:$D2))>1.

Source: Microsoft Support - Use conditional formatting to find duplicate data

How to find duplicate rows using remove duplicates

To find and remove duplicate rows using the Remove Duplicates feature, select your data range, navigate to Data > Data Tools > Remove Duplicates, and choose which columns to evaluate. This tool permanently deletes duplicates while keeping the first occurrence of each unique row.

Step-by-step process:

1. Select your data range or table.
2. Navigate to Data > Data Tools > Remove Duplicates.
3. Check the columns to evaluate for duplicates in the dialog box (e.g., Subject ID and Visit Date).
4. Click OK to execute the removal.
5. Review the summary showing duplicates removed and unique values retained.

Key considerations before using Remove Duplicates

This feature permanently alters your data, and the only way to reverse it is Ctrl+Z immediately after execution. Copy your original dataset before using this tool, especially for regulatory-compliant work or datasets requiring audit trails.

Excel treats the first occurrence of a duplicate as the unique value and deletes subsequent matches. The feature ignores cell formatting and handles case sensitivity and leading/trailing spaces inconsistently across different Excel versions.

Limitations

The Remove Duplicates feature works on tables but requires adjustment for dynamic arrays. Large datasets should be tested on a subset first to verify expected behavior. Sorting your data before removal ensures consistent first-occurrence retention.

For non-destructive previews or case-sensitive matching requirements, Advanced Filter or Power Query provide better alternatives.

Source: Microsoft Support - Find and remove duplicates

How to find duplicate rows using COUNTIF and COUNTIFS formulas

To find duplicate rows using formulas, add a helper column with COUNTIF for single-column checks or COUNTIFS for multi-column comparisons, flagging rows where the count exceeds 1. This non-destructive method allows filtering, sorting, or deletion without altering the original data.

Single-column duplicate detection

1. Insert a helper column (e.g., column F) next to your data.
2. Enter =COUNTIF($A$2:$A$100,A2) in cell F2.
3. Drag the formula down to cover all rows.
4. Values greater than 1 indicate duplicate entries.
5. Filter column F for values >1 to display duplicates only.

Multi-column duplicate detection

For rows that must match across multiple columns (A through D), use COUNTIFS in your helper column:

=COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2,$D$2:$D$100,$D2)>1

This formula returns TRUE for duplicate rows and FALSE for unique rows.

Adding descriptive labels

Combine COUNTIFS with the IF function for clear labeling:

=IF(COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2,$D$2:$D$100,$D2)>1,"Duplicate","Unique")

Identifying first occurrences only

To flag only the second and subsequent duplicates while marking first occurrences as unique, modify the range to expand as the formula moves down:

=COUNTIF($A$2:A2,A2)>1

This formula counts occurrences from the start of the range up to the current row. The first occurrence returns FALSE (count equals 1), while subsequent matches return TRUE.

Formula reference guidelines

Use absolute references ($ signs) on the full range but relative references on the row being evaluated. This ensures the formula calculates correctly when dragged down. The helper column can be hidden after analysis or used to filter and delete flagged rows.

Source: Microsoft Support - COUNTIF function

Comparison of methods for finding duplicate rows

Conditional Formatting provides the fastest visual scan without modifying data. Remove Duplicates offers one-click cleanup but requires a backup copy first. Helper columns with COUNTIF or COUNTIFS formulas deliver the most control, allowing users to filter, review, and selectively delete duplicates while maintaining data integrity.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report