A repeatable workflow for trimming spaces, standardizing text, finding duplicates, and flagging missing information without inventing data.
Editorial note: Created by the First Role Skills Editorial Team. All records are fictional, and the before-and-after files are original. The method is deliberately application-neutral so it can be checked in Excel or Google Sheets.
Messy contact list exercise
All names, addresses, and US 555 numbers are fictional. Work on a copy, then compare with the documented answer.
Download practice CSV · Download answer key
Data cleaning is not the same as making every cell look complete. A safe cleanup keeps uncertain values visible, applies documented standards, and never guesses personal information.
Use seven steps in this order
Protect the source
Duplicate the file and keep an untouched original. Record the date and purpose of your working copy.
Profile before changing
Scan each column for blanks, inconsistent case, unusual formats, and possible duplicates.
Write the standards
Choose one display rule for names, phone numbers, and status values before applying edits.
Remove accidental spaces
Trim leading and trailing whitespace. Do not delete meaningful spaces inside names.
Normalize predictable text
Apply consistent capitalization and approved status values. Keep the raw copy for comparison.
Validate and deduplicate
Use more than a matching name: compare email, phone, and context. Flag uncertain records for review.
Document every exception
Note missing values and unresolved errors instead of silently inventing replacements.
Before and after
Before
| First | Last | Phone | Status | |
|---|---|---|---|---|
| Maya | CHEN | maya.chen@example.com | (555) 010-1201 | Active |
| AMIRA | stone | amira.stone@example | 555-010-1203 | Active |
| Maya | Chen | maya.chen@example.com | 555-010-1201 | Active |
After
| First | Last | Phone | Status | |
|---|---|---|---|---|
| Maya | Chen | maya.chen@example.com | 555-010-1201 | Active |
| Amira | Stone | amira.stone@example | 555-010-1203 | Needs review |
Why the incomplete email remains
We changed Amira’s status to “Needs review” because the correct domain is unknown. Guessing would create false data.
Can you find the six issue types?
Download the practice file and inspect it before revealing the review guide.
Make the cleanup auditable
Keep the untouched source, your cleaned copy, and a short change log together. The change log should name the columns reviewed, the standards you applied, the number of values changed, and every unresolved exception. For a portfolio sample, show three representative transformations rather than publishing a large contact table. Explain why trimming an accidental space is safe, why changing a known status to an approved label is controlled, and why an incomplete email must remain flagged instead of receiving a guessed domain.
A useful final check is to sort and filter the cleaned data, compare the row count with the source, and confirm that no record disappeared unintentionally. Record the application used, the date of the test, and whether formulas were converted to values. This evidence demonstrates data judgment and traceability. It does not imply that you handled real customer or employee information.
Why this matters in your first job
Almost every organization has messy data: duplicated contacts, inconsistent capitalization, missing details. Cleaning it carefully is a common first assignment in admin, sales support, marketing and operations roles. The real skill is judgment: knowing what is safe to standardize and what must be flagged rather than guessed. That judgment protects the organization from errors and privacy problems.
Common questions
Can I delete duplicates automatically? Be careful. Two records with the same name may be different people. Compare several fields, such as email and phone, and flag uncertain matches for review.
Should I fill in missing information? Never invent data. Mark missing values clearly and, where appropriate, ask the owner of the data how to obtain the correct information.
How do I handle personal data safely? Work only in approved systems, avoid copying personal data to personal devices and follow your employer’s data policies. See Excel skills employers expect and our XLOOKUP data audit guide.
Sources and review notes
This guide demonstrates data-quality decisions rather than a changing software interface. Formula names and menu locations should be checked against the application version used by the learner.
- Practice and answer files: original First Role Skills examples
- Byline: First Role Skills Editorial Team
- Reviewed: 3 August 2026