A spreadsheet data quality exercise for beginners
A spreadsheet data quality exercise starts with a small table and a clear definition of what every column means. Check missing values, duplicates and inconsistent formats before calculating a summary. This guide helps beginners see why an attractive dashboard can be misleading when the underlying rows have not been reviewed.
A spreadsheet data quality exercise starts with a small table and a clear definition of what every column means. Check missing values, duplicates and inconsistent formats before calculating a summary. This guide helps beginners see why an attractive dashboard can be misleading when the underlying rows have not been reviewed.
Key ideas
- The unit represented by one row is defined.
- An unchanged raw copy is kept.
- Duplicate rules refer to identifiers and row meaning.
- Missing and invalid values are distinguished.
Define the columns and keep the original
Create an invented table of course enquiries with an ID, date, source and status. Keep a raw copy before making changes. State whether one row means one enquiry, one person or one action. Those are different units. The Google Analytics event guide is useful context for thinking about recorded actions, but this worksheet is not a direct Analytics export and makes no claim about actual VITON13 traffic.
Find inconsistencies before fixing them
Look for repeated IDs, empty statuses and dates written in different formats. Decide what counts as a duplicate using the row definition. Two enquiries from one person are not necessarily duplicate records. Flag doubtful rows rather than deleting them immediately. If you cannot explain why a change is valid, keep the row in a review list and preserve the original value beside your proposed correction.
Make the correction rule explicit
Write one rule for each change: trim outer spaces in a status label, map an approved spelling variant or separate a missing date from an invalid date. Preserve identifiers as text when their formatting matters. Python’s CSV documentation provides context for exchanging tabular text; a CSV file itself does not establish the correct business meaning of a column. That meaning belongs in your data note.
Check totals against the rows
Count the original rows, retained rows and quarantined rows. Explain every difference. Build a small summary only after the review, then inspect an example from each category. If you report a rate, include both numerator and denominator. An invented table can teach the method; its result is not a market benchmark, a conversion forecast or evidence about a real organisation.
Your evidence checklist
Mark only what you have checked. This records your own progress, not an independent audit or a predicted result. There is no automatic saving; download the note if you want to keep it.
0 / 6 checked
In everyday language
A useful spreadsheet summary can explain every original row and every correction that changed the answer.
Try it yourself
Make ten fictional enquiry rows with one repeated ID, one empty status and one inconsistent date. Flag each issue, record the correction rule and create a summary that explains all ten original rows.
Expected result
A raw sheet, a reviewed sheet and an issue log whose counts reconcile. All examples are synthetic and should not be treated as business performance data.
Check your answer: Can I delete every repeated name?
No. Names are not necessarily unique, and one person may create several legitimate enquiries. Define the row unit and the identifier first. When the intended meaning is unclear, flag the row for review instead of removing information that may be valid.
Questions
Can I delete every repeated name?
No. Names are not necessarily unique, and one person may create several legitimate enquiries. Define the row unit and the identifier first. When the intended meaning is unclear, flag the row for review instead of removing information that may be valid.
Why keep the raw sheet after cleaning?
It lets you inspect how a correction changed the result and recover information if a rule was wrong. A tidy final table without a change record is difficult to review. Keep the original, the rule and the corrected value connected.
A useful spreadsheet summary can explain every original row and every correction that changed the answer.
Sources and further reading
- Python — CSV reading and writing ↗Sources checked:
- Google Analytics — Set up events ↗Sources checked: