How to Highlight Duplicate Rows in Excel?
Identifying duplicate entries in your data can streamline your analysis. Use this formula to easily flag duplicates in your list.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Identify duplicate rows in a list using a formula, returning TRUE for any row that has already appeared above it"
=COUNTIF(A$1:A1, A1) > 1This formula checks if the current row's value has appeared in the preceding rows and returns TRUE if it has.
Step-by-step breakdown
- COUNTIF counts how many times the current cell's value appears in the specified range.
- The range A$1:A1 expands as you drag the formula down, including all previous rows.
- If the count is greater than 1, it indicates a duplicate.
- The formula returns TRUE for duplicates and FALSE for unique entries.
Edge cases & warnings
- Empty cells may return FALSE even if they are duplicates.
- Different data types (text vs. numbers) may not be recognized as duplicates.
- Leading or trailing spaces can cause values to be treated as unique.
- Case sensitivity may affect duplicate detection if not handled properly.
Examples
"A1: Apple, A2: Banana, A3: Apple"
A1: FALSE, A2: FALSE, A3: TRUE"A1: 123, A2: 456, A3: 123"
A1: FALSE, A2: FALSE, A3: TRUEFrequently asked questions
How do I apply this formula to an entire column?
Drag the fill handle down from the cell with the formula to apply it to other rows.
Can I use this formula for non-adjacent columns?
No, this formula only works for a single column at a time.
What if I want to highlight duplicates instead of just marking them?
You can use Conditional Formatting with this formula to visually highlight duplicates.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.