Practical technology learning
An Excel Data-Cleaning Checklist for a Small Practice Dataset
Cleaning a spreadsheet is a series of decisions, not a single formatting command. A good practice exercise preserves the source, defines what each column means and records changes that affect the result. Use invented enquiries or public practice data rather than real student contact details.
Build a dataset with deliberate problems
Create about twenty synthetic records with an enquiry ID, date, chosen course and status. Add surrounding spaces, inconsistent capitalisation, a repeated ID, a missing status and one ambiguous date. Keep a separate list of the problems you intentionally inserted.
Save an untouched original and work on a copy. A clear before-and-after comparison is more useful for learning than repeatedly editing the only copy until the sheet looks tidy.
Define a column dictionary
For each column, record its meaning, expected type and permitted missing-value behaviour. An enquiry ID can require text storage even when all its characters are digits. Converting it to a number may remove leading zeros.
Decide the allowed course and status values. Do not merge two categories solely because the wording is similar. Keep an unresolved category for review when the intended meaning is unknown.
Clean spaces and dates without guessing
Remove unintended surrounding spaces using a documented operation. Ordinary whitespace and imported special characters do not always behave identically, so inspect the result rather than assuming one formula fixes every file.
Interpret dates using the source's intended locale. A value such as 04/05 can represent different dates in different conventions. If you cannot establish which convention applies, flag the record instead of inventing a date.
Review duplicates against a reliable key
Two people can share a name, and one person can submit multiple legitimate enquiries. Decide what makes a record unique for this exercise. Review duplicate enquiry IDs separately from repeated names or repeated course choices.
Before removing anything, keep a record of which rows were affected and why. Compare row counts before and after the operation. A lower row count is not evidence of better quality unless every removal has a justified rule.
Use repeatable transformations when appropriate
For recurring imports, Power Query can make transformation steps visible and repeatable. Profiling tools can help inspect valid, error and empty values. Check whether profiling is based on a sample or the whole dataset before interpreting the result.
Keep each step understandable: type conversion, trimming, category mapping and duplicate review should have clear purposes. Re-run the process on a second synthetic file and check whether the assumptions still hold.
Finish with a quality note
- State the number of original and retained records.
- List the accepted date convention and category mappings.
- Describe how duplicate records were identified.
- Count missing values that remain unresolved.
- Compare one summary total with a manual check.
- Keep the original data, cleaned output and change log together.
Connect the exercise to your next skill
A cleaned practice sheet can become the input for a PivotTable, SQL import or reporting project. Preserve the column dictionary so the next tool does not reinterpret your data silently.
For structured spreadsheet learning, discuss Mango Engineers' current Excel and Advanced Excel course scope. Ask which exercises fit your starting level and confirm batch availability before enrolling.