Remove Duplicate Data in Excel: 3 Practical Methods
Clean student lists, fee records, contact lists and attendance data by removing duplicate entries.
Before you delete anything
- Make a copy of the sheet (right-click the tab → Move or Copy → Create a copy). Removing duplicates cannot always be undone after saving.
- Decide what “duplicate” means: same mobile number? same name + class? exactly the same row? Two students can share a name; a mobile number or admission number is usually a better key.
- Clean spaces and capital letters first: “Asha Sharma ” and “asha sharma” look different to Excel. Use
=TRIM(PROPER(B2))in a helper column.
Method 1: Highlight duplicates first (safe check)
- Select the column, e.g. the Mobile column C2:C500.
- Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
- Choose a colour → OK. Every repeated value is coloured.
- Use Data → Filter → filter by colour to review them before deleting.
To count how many times each value appears, add a helper column: =COUNTIF($C$2:$C$500, C2). Any number above 1 is a duplicate. =COUNTIF($C$2:C2, C2)>1 marks only the 2nd, 3rd… occurrence – the first stays unmarked.
Method 2: Remove Duplicates button
- Click inside your table (with headings).
- Data → Data Tools → Remove Duplicates.
- Tick My data has headers.
- Tick only the columns that define a duplicate – for example only Mobile, or Name + Class.
- OK. Excel reports e.g. “12 duplicate values found and removed; 488 unique values remain.”
Method 3: UNIQUE formula (keeps the original data)
In Excel 2021/Microsoft 365 and Google Sheets, =UNIQUE(A2:D500) in an empty cell returns the list without duplicate rows. =UNIQUE(C2:C500) gives each mobile number once, and =COUNTA(UNIQUE(C2:C500)) counts how many different numbers there are.
| Method | Changes original data? | Best for |
|---|---|---|
| Highlight + COUNTIF | No | Checking and deciding row by row |
| Remove Duplicates | Yes (deletes rows) | Final clean-up of a copy |
| UNIQUE | No (new list) | Reports, dropdown lists, counting |
Real example: admission enquiry list
A school collects enquiries from a Google Form, WhatsApp and the front desk. Many parents enquire twice. Clean it like this:
- Combine all lists in one sheet with the same columns: Date, Parent name, Mobile, Class.
- Clean the mobile numbers: keep only 10 digits (
=RIGHT(SUBSTITUTE(SUBSTITUTE(C2," ",""),"-",""),10)). - Sort by Date, newest first.
- Remove Duplicates on Mobile only → the newest enquiry of each parent remains.
- Count enquiries per class with a Pivot Table.
Google Sheets and LibreOffice
- Google Sheets: Data → Data cleanup → Remove duplicates; also Data cleanup → Trim whitespace.
=UNIQUE()works. - LibreOffice Calc: Data → More Filters → Standard Filter → Options → tick No duplications and “Copy results to” another place. Newer versions also have Data → Duplicates.
कुछ भी delete करने से पहले
- Sheet की copy बनाएँ (tab पर right-click → Move or Copy → Create a copy)। Save करने के बाद duplicates हटाना हमेशा undo नहीं होता।
- तय करें “duplicate” का मतलब क्या है: एक ही mobile number? एक ही नाम + class? बिल्कुल एक जैसी row? दो students का नाम एक हो सकता है; mobile number या admission number आम तौर पर बेहतर key है।
- पहले spaces और capital letters साफ़ करें: Excel के लिए “Asha Sharma ” और “asha sharma” अलग हैं। Helper column में
=TRIM(PROPER(B2))लगाएँ।
तरीका 1: पहले duplicates highlight करें (सुरक्षित जाँच)
- Column चुनें, जैसे Mobile column C2:C500।
- Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values।
- रंग चुनें → OK। हर दोहराई value रंगीन हो जाती है।
- Delete करने से पहले जाँचने के लिए Data → Filter → रंग से filter करें।
हर value कितनी बार आती है गिनने के लिए helper column जोड़ें: =COUNTIF($C$2:$C$500, C2)। 1 से बड़ा कोई भी number duplicate है। =COUNTIF($C$2:C2, C2)>1 सिर्फ़ दूसरी, तीसरी… बार वाली को mark करता है – पहली बिना mark रहती है।
तरीका 2: Remove Duplicates button
- अपनी table (headings के साथ) के अंदर click करें।
- Data → Data Tools → Remove Duplicates।
- My data has headers पर tick करें।
- सिर्फ़ वही columns tick करें जो duplicate तय करते हैं – जैसे सिर्फ़ Mobile, या Name + Class।
- OK। Excel बताता है जैसे “12 duplicate values found and removed; 488 unique values remain.”
तरीका 3: UNIQUE formula (मूल data बचा रहता है)
Excel 2021/Microsoft 365 और Google Sheets में ख़ाली cell में =UNIQUE(A2:D500) duplicate rows के बिना list देता है। =UNIQUE(C2:C500) हर mobile number एक बार देता है, और =COUNTA(UNIQUE(C2:C500)) गिनता है कि कितने अलग numbers हैं।
| तरीका | मूल data बदलता है? | किसके लिए |
|---|---|---|
| Highlight + COUNTIF | नहीं | Row-by-row जाँचना और तय करना |
| Remove Duplicates | हाँ (rows delete) | Copy की अंतिम सफ़ाई |
| UNIQUE | नहीं (नई list) | Reports, dropdown lists, गिनती |
असली उदाहरण: admission enquiry list
एक school Google Form, WhatsApp और front desk से enquiries जमा करता है। कई parents दो बार पूछते हैं। ऐसे साफ़ करें:
- सभी lists को एक sheet में एक जैसे columns के साथ जोड़ें: Date, Parent name, Mobile, Class।
- Mobile numbers साफ़ करें: सिर्फ़ 10 digits रखें (
=RIGHT(SUBSTITUTE(SUBSTITUTE(C2," ",""),"-",""),10))। - Date से sort करें, नई पहले।
- सिर्फ़ Mobile पर Remove Duplicates → हर parent की नई enquiry बचती है।
- Pivot Table से हर class की enquiries गिनें।
Google Sheets और LibreOffice
- Google Sheets: Data → Data cleanup → Remove duplicates; साथ में Data cleanup → Trim whitespace।
=UNIQUE()चलता है। - LibreOffice Calc: Data → More Filters → Standard Filter → Options → No duplications tick करें और “Copy results to” दूसरी जगह। नए versions में Data → Duplicates भी है।
Frequently Asked Questions
How do I remove duplicates in Excel but keep one copy?
Use Data → Remove Duplicates and tick only the key columns. Excel keeps the first occurrence and deletes the rest. Sort first if you want to keep the newest row.
Why does Excel not detect duplicates that look the same?
Hidden differences: extra spaces, different capital letters, or numbers stored as text. Clean with TRIM, PROPER/LOWER and VALUE in a helper column, then check again.
Can I undo Remove Duplicates?
Yes with Ctrl + Z immediately, before saving and closing. That is why you should always work on a copy of the sheet.
