Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.1 Duplicates

By Last updated:

Before

Order ID City Product Amount
BLK-3001 Pune Gokul Cow Milk 500 ml 32
BLK-3002 Nashik Nashik Grapes 500 g 90
BLK-3001 Pune Gokul Cow Milk 500 ml 32
BLK-3003 Nagpur Nagpur Oranges 1 kg 120
BLK-3002 Nashik Nashik Grapes 500 g 90

After

Order ID City Product Amount
BLK-3001 Pune Gokul Cow Milk 500 ml 32
BLK-3002 Nashik Nashik Grapes 500 g 90
BLK-3003 Nagpur Nagpur Oranges 1 kg 120

Steps in Excel

  1. See them first: select Order IDs › Home › Styles › Conditional Formatting › Highlight Cells Rules › Duplicate Values.
  2. Flag with a formula: in E2 =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First") – the expanding range $A$2:A2 marks the 2nd, 3rd… copies only. Filter on "Duplicate" to review.
  3. Remove: click inside the data › Data › Data Tools › Remove Duplicates › tick only the columns that define a duplicate (here Order ID; or all columns for fully identical rows) › OK. Excel reports how many were removed – here 2 duplicate values found and removed; 3 unique values remain.
  4. Formula way (Microsoft 365 / Excel 2021+): =UNIQUE(A1:D6) returns the distinct rows in a new place; =UNIQUE(B2:B6) gives the distinct city list.

Ravindra Bagale's Tip

Remove Duplicates madhe khup students sagle columns tick thevtat kiwa chukiche columns tick kartat. Ekach customer ne don vegle orders kele tar te duplicate nahit! Duplicate kashala mhanaycha (Order ID? Order ID + Product?) he aadhi tharva, aani remove karaychya aadhi COUNTIF flag ne review kara – Remove Duplicates undo nantar parat milat nahi.

Practice task

In a list of 20 orders with 4 repeated Order IDs, flag duplicates with COUNTIF, remove them with Remove Duplicates, and confirm the new count with =ROWS(UNIQUE(A2:A21)) (Microsoft 365).