Excel formulas can flag repeated values, duplicate rows, or entries that appear more than once in a list.
The right formula depends on whether you want to mark every match, keep the first occurrence unmarked, or compare several columns together.
Find duplicates in one column with COUNTIF
For a list in cells A2:A100, enter this formula in B2 and fill it down:
=COUNTIF($A$2:$A$100,A2)>1The result is TRUE when the value in A2 appears more than once in the range and FALSE when it appears once.
For example, if A2:A6 contains Apple, Pear, Apple, Plum, Pear, the formula returns TRUE for both Apple cells and both Pear cells, and FALSE for Plum.
The dollar signs keep the lookup range fixed while the formula is copied down.
The reference A2 remains relative, so it changes to A3, A4, and so on.
If you prefer a readable label, use:
=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")COUNTIF is available in current desktop versions of Excel, Excel for the web, and Google Sheets.
It counts values matching the criterion; it does not distinguish uppercase from lowercase.
Flag only the second and later occurrences
Sometimes you want to preserve the first instance as the original and mark only subsequent repeats.
In B2, enter:
=COUNTIF($A$2:A2,A2)>1Fill the formula down alongside the data.
The first range endpoint expands as the formula moves, so each row counts matching values from the top of the list through the current row.
In the Apple, Pear, Apple example, the first Apple returns FALSE and the second Apple returns TRUE.
For a text label instead of TRUE or FALSE, use:
=IF(COUNTIF($A$2:A2,A2)>1,"Repeat","First or unique")This is useful for review queues, imports, and cleanup tasks where the first occurrence should remain.
Sort the source list only if changing its order is acceptable, because the formula treats the first row in the current order as the original.
Ignore blank cells when checking for duplicates
COUNTIF can count blank cells, so a basic duplicate formula may label empty rows as repeated when there are multiple blanks.
Add a nonblank check to avoid that:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)This returns FALSE for an empty A2 and TRUE only when a nonblank value occurs more than once.
To display a blank result for empty rows and a label for filled rows, use:
=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"))If cells contain formulas that return an empty string, the check A2="" treats them as blank too.
If whitespace-only cells should count as blank, clean or normalize those values first; a cell containing spaces is not the same as an empty cell.
Check whether a whole row is duplicated
To identify repeated combinations across columns A and B, enter this in C2 and fill down:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1COUNTIFS counts rows where both conditions are true: the value in column A matches A2 and the value in column B matches B2.
For example, if the columns contain “Mira, North” twice, that pair is flagged.
A row with “Mira, South” is treated as different because its second field differs.
To check three fields, add another range-and-criterion pair:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1Keep every criteria range the same size.
COUNTIFS is supported in Excel 2007 and later, Microsoft 365, Excel for the web, and Google Sheets.
In older Excel versions without COUNTIFS, a helper column can join the fields, then COUNTIF can check that combined key.
Find duplicates using a concatenated key
For a multi-column comparison, you can make a helper key in D2:
=A2&"|"&B2Then test that key in E2:
=COUNTIF($D$2:$D$100,D2)>1A delimiter such as a vertical bar helps separate fields, but it can still appear in source data.
For more reliable comparisons, especially when values may contain the delimiter or have different data types, prefer COUNTIFS.
Another option is to add a delimiter that cannot occur in the source values after validating the data.
Make duplicate results easier to scan
For a visual flag without a helper column, use Conditional Formatting.
Select A2:A100, choose the duplicate-values rule, and set the formatting you want.
The menu wording varies slightly across Excel versions, but it is typically under Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
A formula-based conditional formatting rule gives more control.
Select A2:A100, create a rule using a formula, and enter:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)Make sure A2 is the active top-left cell in the selected range.
The relative reference adjusts for each cell, while the absolute range stays fixed.
This rule highlights every nonblank duplicate, including the first occurrence.
Common formula issues and edge cases
- Leading or trailing spaces: “Pear” and “Pear ” may look identical but differ as text.
Use TRIM in a cleanup column, then check the cleaned values. - Numbers stored as text: A numeric 25 and text “25” can behave differently depending on the data and formula.
Standardize the column before comparing. - Uppercase and lowercase: COUNTIF is case-insensitive, so “abc” and “ABC” count as matches.
Case-sensitive checks require a different approach, such as SUMPRODUCT with EXACT. - Changing list size: A fixed range like $A$2:$A$100 will not include new rows below 100.
Use an Excel Table with structured references or expand the range as the list grows. - Formula separators: Some regional settings use semicolons instead of commas.
If Excel rejects a formula, replace argument-separating commas with semicolons.
Choose COUNTIF when one field determines whether a value repeats, COUNTIFS when several fields define a duplicate row, and the expanding COUNTIF range when only later occurrences should be marked.
Check the data for blanks, spaces, and inconsistent types before relying on the flags for a cleanup decision.