📊 Excel Basics #35 – Remove Duplicates
Duplicate records are common when working with data collected from multiple files, systems, or sources. Excel provides a quick way to identify and remove duplicate values without manually checking thousands of rows.
📌 What are Duplicates?
A duplicate occurs when the same record appears more than once.
Example:
Employee ID | Name | Department
101 | Rahul | IT
102 | Priya | HR
101 | Rahul | IT
103 | Amit | Finance
Here, the record for Employee ID 101 appears twice.
📌 1. Remove Duplicates
Select your dataset and go to: Data → Remove Duplicates
Excel will show a window where you can choose which columns should be checked. Click OK, and Excel removes duplicate rows based on the selected columns.
📌 2. Choosing Columns Matters
Suppose you have:
ID | Name | City
101 | Rahul | Pune
101 | Rahul | Mumbai
If you select all three columns, these are not considered duplicates because the City is different. But if you select only ID and Name, Excel considers them duplicates.
So always decide what makes a record "duplicate" before removing anything.
📌 3. Remove Duplicates from an Excel Table
If your data is already an Excel Table: Table Design → Remove Duplicates
You can select the columns you want Excel to use for identifying duplicates.
📌 4. Excel Keeps the First Record
When Excel removes duplicates, it generally keeps the first occurrence and removes subsequent matching records.
Example:
ID | Name
101 | Rahul
101 | Rahul
After removing duplicates:
ID | Name
101 | Rahul
📌 5. Remove Duplicates vs Find Duplicates
These are different tasks.
Remove Duplicates → Permanently removes duplicate records from the selected dataset.
Conditional Formatting → Duplicate Values → Highlights duplicates without deleting them.
💡 If you're unsure whether duplicates should be deleted, highlight them first and review the data.
📌 Real-World Example
Imagine you have 50,000 customer records collected from different sources. Some customers appear multiple times.
You can select: Customer ID → Remove Duplicates
Excel can quickly reduce the dataset to unique customer records.
📌 Common Mistakes
❌ Removing duplicates without checking which columns define uniqueness.
❌ Selecting only one column when the entire record should be compared.
❌ Not keeping a backup before deleting data.
❌ Assuming similar-looking records are always duplicates.
✅ Best Practices
• Always keep a backup of the original dataset.
• Decide which columns define a unique record.
• Review duplicates before deleting important data.
• Use Conditional Formatting first when you're unsure.
• For large datasets, use a unique ID whenever possible.
💡 Double Tap ❤️ For More
12August 26, 2026 1.9K 5
