Excel's Remove Duplicates tool deletes repeated rows in one click. But before you delete anything, it's worth knowing which duplicates exist — and sometimes highlighting them is more useful than removing them. Here are three approaches.
Method 1: Remove Duplicates Tool (Data Tab)
The fastest method. Use it when you're confident you want to delete the extra rows.
- Click any cell inside your data range
- Go to Data tab → Remove Duplicates (Data Tools group)
- A dialog lists all columns in your data
- Select the columns that define what makes a row "duplicate":
- Click OK
Method 2: Find Duplicates First with COUNTIF
Before removing anything, use COUNTIF to see exactly which rows are duplicates.
In an empty column next to your data, enter this formula (assuming your key column is A, starting at row 2):
=COUNTIF($A$2:$A$1000,A2)This counts how many times the value in A2 appears in the entire column. Results:
- 1 = unique (appears once)
- 2 or more = duplicate
Copy the formula down the column. Then:
- Filter the helper column for values > 1 to see all duplicate rows
- Review them before deciding what to delete
- Sort by the helper column descending to group duplicates together
To flag only the second+ occurrence (not the first): =COUNTIF($A$2:A2,A2)>1
This formula expands its range as it copies down. The first occurrence always returns FALSE; subsequent ones return TRUE.
Method 3: Highlight Duplicates with Conditional Formatting
Visualize duplicates without deleting anything.
- Select the column to check (e.g., A2:A1000)
- Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
- Choose a highlight color → click OK
- Spotting duplicates during a data review
- Presenting the problem to someone else before cleaning
- Checking whether a supposed key column is actually unique
Apply this to the range A2:A1000. The expanding range reference means the first time a value appears, the count is exactly 1 (not highlighted). The second time it appears, the count is 2 (highlighted).
Which Method to Use?
| Situation | Method |
| You trust the data, just need it clean | Remove Duplicates tool |
| You want to review before deleting | COUNTIF helper column |
| Sharing a report and want to flag issues visually | Conditional formatting |
| Need to keep duplicates but mark them | COUNTIF formula → filter/sort |
Frequently Asked Questions
Does Remove Duplicates consider letter case?
No. Excel's Remove Duplicates tool treats "Apple", "apple", and "APPLE" as duplicates. If case matters, use a COUNTIF-based approach where you can use EXACT() for case-sensitive comparisons:
=SUMPRODUCT((EXACT($A$2:$A$1000,A2))*1)
How do I remove duplicates across multiple columns?
In the Remove Duplicates dialog, check only the columns that together define uniqueness. For example, if Name + Date together should be unique (a person can appear multiple times on different dates), check both Name and Date columns and uncheck the others.
Can I remove duplicates in a column without affecting other columns in the same row?
No — Remove Duplicates always operates on full rows. If you want to deduplicate just one column's values independently (like creating a unique list), use Data → Remove Duplicates on a copy of just that column, or use the UNIQUE function (Excel 365): =UNIQUE(A2:A1000).
Why does Remove Duplicates say "0 duplicates found" when I can clearly see repeated values?
Usually a formatting issue — the values look the same but aren't. Common causes: (1) trailing spaces ("Apple " vs "Apple") — fix with TRIM(); (2) numbers stored as text vs real numbers — check the cell alignment or use Data → Text to Columns to convert; (3) invisible characters — use CLEAN() to remove them.