Concept
1. Practice sheet
| A | B | C | |
|---|---|---|---|
| 1 | Name | Mobile | City |
| 2 | Rahul Sharma | 9876543210 | Delhi |
| 3 | Neha Gupta | 9812345678 | Jaipur |
| 4 | Rahul Sharma | 9876543210 | Delhi |
| 5 | Ravi Kumar | 9898989898 | Mumbai |
| 6 | Neha Gupta | 9812345678 | Jaipur |
| 7 | rahul sharma | 9876543210 | Delhi |
2. Rule 1: detect first, delete later
Deleting is permanent once you save. First see what's duplicated, then decide.
3. Highlight duplicates (quick look)
Select B2:B7 → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Every repeated mobile number turns red. Good for a quick look, but it colours all copies, including the first one.
4. Detect with COUNTIF
How many times does this value appear?
=COUNTIF($B$2:$B$7, B2)
Anything above 1 is a duplicate. Rahul's number shows 3.
Mark only the repeats, keep the first:
=IF(COUNTIF($B$2:B2, B2) > 1, "Duplicate", "Original")
The trick is the range $B$2:B2 — only the start is locked, so it grows as you drag down. Each row checks the rows from the first data row through the current row. Result: rows 2, 3, 5 Original; rows 4, 6, 7 Duplicate.
Duplicate on more than one column (same name and same city):
=COUNTIFS($A$2:A2, A2, $C$2:C2, C2)
Filter this column for values above 1 to review the repeats.
5. Remove Duplicates
- Click anywhere in the data.
- Data → Remove Duplicates.
- Tick the columns that together define a duplicate (e.g. only Mobile, or Name + Mobile).
- OK. Excel keeps the first occurrence and deletes the rest.
On this sheet with all three columns ticked, 3 rows are removed — including rahul sharma, because Remove Duplicates ignores upper/lower case.
Make a copy of the sheet before you do this. Ctrl + Z works only until you close the file.
6. Why Excel misses some duplicates
Rahul Sharma and Rahul Sharma (two spaces) are different to Excel. Mixed number/text identifiers can also cause inconsistent comparisons between tools. Normalize mobile numbers as text and preserve leading zeros. Clean first with TRIM (Module 5), then remove duplicates.
7. Excel 365 shortcut: UNIQUE
=UNIQUE(A2:C7)
Gives a duplicate-free copy in a new place, without touching the original. Useful when you want to keep the raw data.
Common mistakes
Removing duplicates without a backup. Ticking the wrong columns (ticking only Name would delete two different people with the same name). Not trimming spaces first.