How To Find Duplicates In Excel Using Formula

Finding duplicates in Excel using a formula requires the COUNTIF function, which counts occurrences of a value within a specified range. This function serves as the foundation for identifying repeated entries across cells, columns, or rows. Excel 365 users have access to additional dynamic array functions like UNIQUE and FILTER for more advanced duplicate detection.

What is the primary formula to find duplicates in Excel?

The primary formula to find duplicates in Excel is =COUNTIF(range, criteria)>1. This formula counts how many times a specific value appears in a range and returns TRUE when the count exceeds one, indicating a duplicate.

Enter the formula in an adjacent cell to your data. For data in column A starting at row 2, type =COUNTIF(A:A, A2)>1 in cell B2. The formula checks how many times the value in A2 appears throughout column A. A result of TRUE indicates the value appears more than once. A result of FALSE indicates the value is unique.

Formula variations

1. Label duplicates explicitly: =IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "Unique")
2. Find duplicates across multiple columns: =COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1
3. Count exact occurrences: =COUNTIF($A$2:$A$100, A2)

How does the COUNTIF formula work for finding duplicates?

COUNTIF scans the specified range and tallies all cells that match the criteria cell. The formula =COUNTIF($A$2:$A$100, A2)>1 uses absolute references (A$2: A$100) for the range and a relative reference (A2) for the criteria.

The ">1" condition identifies duplicates because the first occurrence of any value returns a count of 1. Values appearing two or more times return counts greater than 1, triggering a TRUE result. Drag the formula down to apply it across all rows in your dataset.

How to find duplicates using conditional formatting with a formula?

Conditional formatting with a COUNTIF formula highlights duplicate cells automatically with a specified fill color.

Steps to apply

1. Select the data range (e.g., A2:A100)
2. Navigate to Home > Conditional Formatting > New Rule
3. Select "Use a formula to determine which cells to format"
4. Enter =COUNTIF($A$2:$A$100, A2)>1
5. Click Format, choose a fill color, and click OK

The formula uses absolute references (A$2: A$100) for the range and a relative reference (A2) for the active cell. This configuration ensures correct evaluation when applied across the entire selection. All instances of duplicated values receive highlighting, including the first occurrence.

Advanced conditional formatting options

1. Multi-column duplicates: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
2. Exclude first instance: =COUNTIF($A$2:$A$100,A2)-1>0

Excel's built-in "Duplicate Values" preset under Conditional Formatting > Highlight Cells Rules provides a faster alternative with less customization.

What are other formulas to find duplicates in Excel?

Excel provides several alternatives to COUNTIF for identifying duplicates, with dynamic array functions available in Excel 365 and later versions.

UNIQUE function

The UNIQUE function extracts distinct values from a range. Enter =UNIQUE(A2:A100) in a cell to generate a spilled list of unique items. Compare this list against the original data to identify repeated values. This function requires Excel 365 or Excel 2021.

SUMPRODUCT function

SUMPRODUCT works as a COUNTIF substitute for duplicate detection. The formula =IF(SUMPRODUCT(($A$2:$A$100=A2)*1)>1,"Duplicate","") counts matches by multiplying the logical array by 1, forcing a numerical count. This approach provides compatibility with older Excel versions that lack dynamic arrays.

FILTER function

The FILTER function isolates unique values by excluding duplicates. Enter =FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)=1) to return only values that appear exactly once in the range. This function requires Excel 365.

INDEX-MATCH approach

INDEX-MATCH can verify value existence: =IF(ISNUMBER(MATCH(A2,$A$2:$A$100,0)),"Found",""). This method is less efficient than COUNTIF for large datasets due to non-vectorized lookup operations.

Power Query

Power Query provides non-formula duplicate detection through the Data > Get Data menu. This tool groups and removes duplicates through a visual interface, handling large datasets more efficiently than formula-based methods.