Check duplicates before deleting rows
COUNTIF flags repeated IDs, but flagged rows are not always rows to remove.
Try this synthetic example
Enter R001, R002, R002, R003, R003 in A2:A6, one per row. In B2, enter =COUNTIF($A$2:$A$6,A2) and fill down. Results: 1, 2, 2, 2, 2.
Four rows belong to duplicate groups. If each repeated record is identical and you keep one per ID, only two surplus rows should be removed.
Check conflicting records
Compare the other fields before deleting anything. Rows with the same ID but different dates, amounts or contact details need review. Never guess missing information.
Clean whitespace safely
Use =TRIM(A2) in a helper column to remove ordinary leading, trailing and repeated spaces. Keep raw data until you check the result. TRIM does not remove every invisible character.
Final quality checks
Save the original. Confirm matching fields. Separate identical duplicates from conflicting records. Record removals. Check blanks and reconcile totals.
Practise the full workflow
Our Excel Data Cleaning Practice Workbook includes 28 exercises, 90 synthetic records, solutions, PDF guidance and a quality checklist. Current price: USD $7.Explore the Excel Data Cleaning Practice Workbook
Remote Skills Lab provides independent practice resources. No certification, employment or income is guaranteed.