Ravindra BagaleCourses & study guides Track your progress

Guides

Excel मध्ये Data → Remove Duplicates कसे वापरायचे

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Read this guide in: English · मराठी · हिंदी

Data → Remove Duplicates तुम्ही tick केलेल्या columns वरून repeated rows काढून टाकतं — प्रत्येक key ची पहिली occurrence ठेवून.

मित्रांनो, customer list मध्ये same phone दोनदा — cleanup time. Totals आणि mail merges duplicates वर तुटतात. Backup, मग Data → Remove Duplicates.

थोडक्यात उत्तर

Vertical steps:

  1. Sheet copy करा (backup).
  2. Headers सहित data select.
  3. Data → Remove Duplicates.
  4. Duplicate ठरवणारे columnsच tick (उदा. Order ID).
  5. OK — किती removed / remaining वाचा.
  6. Key column spot-check.
Data tab → Remove Duplicates → tick key columns → OK

Language: English · मराठी · हिंदी

Before आणि after

Before

Order ID City Amount
O1 Mumbai 100
O2 Pune 40
O1 Mumbai 100

After (tick Order ID)

Order ID City Amount
O1 Mumbai 100
O2 Pune 40
Before remove duplicates Three rows with duplicate O1.

Before

After remove duplicates Unique order list with two rows.

After

Duplicates का काढायचे?

  1. Double-counted sales PivotTables फुगावतात.
  2. Customer SMS lists same phone spam करू नयेत.
  3. Serious analysis आधी cleaning chapter skill.

Excel काय करतं

  1. Tick केलेल्या columns बघतं.
  2. प्रत्येक unique key combo ची पहिली row ठेवतं.
  3. नंतरच्या matching rows delete करतं.
  4. Summary count दाखवतं.

Vertical practice steps

  1. तीन-row before table टाइप करा.
  2. Sheet duplicate = backup.
  3. Data → Remove Duplicates → फक्त Order ID tick.
  4. दोन rows राहिल्या confirm.
  5. Undo (Ctrl+Z); सर्व columns tick — इथे O1 rows identical असल्याने same result.
  6. दुसऱ्या O1 चा Amount 99 करा; फक्त Order ID tick — तरी पहिला O1 राहतो.

Safer habits

  1. नेहमी backup किंवा copy sheet.
  2. फक्त पाहण्यासाठी Conditional Formatting → Duplicate Values.
  3. Microsoft 365: UNIQUE() source न काढता distinct list spill करतो.

Ravindra Bagale's Tip

सर्व columns tick फक्त जेव्हा “पूर्ण identical rows” म्हणायचे असतील. “एक row per Order ID” साठी फक्त Order ID tick — इतर fields वेगळे असले तरी. Wrong ticks = wrong cleanup.

Practice task

  1. दहा customer rows, दोन repeated phones.
  2. Phone वर Remove Duplicates.
  3. Conditional Formatting → Duplicate Values on Phoneही try.
  4. फरक नोंदवा: delete vs highlight.

Learn it properly

Related guides: PivotTable · CONCAT/TEXTJOIN · FILTER

समजलं का? Remove Duplicates tick केलेल्या columns नुसार पहिली row ठेवतं. आधी backup. आता text join.

Frequently asked questions

Does Remove Duplicates keep the first row?

Yes. Excel keeps the first occurrence and deletes later duplicates in the selection.

Which columns should I tick?

Only columns that define sameness. Ticking every column removes only fully identical rows.

Can I highlight duplicates instead?

Yes — Conditional Formatting → Highlight Cells Rules → Duplicate Values — without deleting.

UNIQUE function?

In Microsoft 365, UNIQUE spills a distinct list without deleting source rows.

Undoing?

Ctrl+Z immediately. That is why a backup sheet is wise before cleanup.

Course lesson?

Chapter 5 Data Cleaning — Duplicates.