Start with the question, not the cleanup tool
Data cleaning means identifying and addressing entries that could make a dataset misleading or difficult to use. It is tempting to open a spreadsheet and immediately delete anything that looks unusual. First, decide what each row represents and what question the data should answer. A row might represent a customer, an order or a survey response. That distinction determines whether two similar rows are duplicates or two legitimate events.
Keep an unchanged copy of the original data. Then inspect the fields, note what values are expected and record any rules you use to make changes. For example, an order quantity may need to be a whole number, while a free-text comment can contain almost anything. Cleaning is not about making every entry look alike; it is about making the data consistent enough to interpret while preserving differences that matter.
Check duplicates before deleting them
A duplicate is a record that repeats information already represented elsewhere, but matching rows do not always mean an error. Two identical purchases by the same person might be separate orders. Likewise, one person could submit a survey twice. Before removing a row, ask whether your dataset is meant to count purchases, people or responses.
Start by looking for exact matches across fields that should identify a record, such as an order ID. Then review possible duplicates that differ slightly: a name with extra spaces, an email address with a typo or a record entered on another day. Comparing several fields is usually more useful than matching on a name alone. If an identifier is reused or missing, set aside uncertain cases for review rather than treating a software flag as proof.
When a repeat is confirmed, decide whether to remove it or combine information from the records. If one copy has a missing contact field and the other has a value, combining them may be more useful than deleting one at random. Record how many rows were affected and why, so a later reader can understand any change in totals.
Bring inconsistent labels into a shared vocabulary
Labels often vary even when they mean the same thing. A survey might contain “Online,” “online” and “web” in a field about shopping channels. Left untouched, those entries can appear as separate categories in a chart. A simple mapping table can specify that all three become “Online,” making the rule visible and reusable.
Before merging labels, check their meaning in context. “Store pickup” and “In store,” for instance, may describe different experiences despite sharing a physical location. Standardize harmless differences such as capitalization and surrounding spaces first, then review abbreviations and alternative wording. Avoid forcing ambiguous entries into a category just to produce a cleaner-looking chart.
It helps to retain both the original entry and the standardized label. The original lets you revisit a decision; the new field supports consistent grouping. If new responses arrive later, the same mapping rules can be applied without guessing how earlier labels were handled.
Treat invalid values as clues, not just obstacles
An invalid value does not fit the field’s agreed rules. Examples include letters in a numeric quantity, an impossible calendar date or a response outside a survey’s stated scale. Define valid ranges and formats based on what the field is supposed to capture, not merely on what most entries look like. An unusually large purchase may be real; it deserves a check, not automatic removal.
Different problems call for different responses. An obvious formatting issue may be correctable, while an unclear value may need to be marked as missing. Use a distinct missing-value marker rather than entering zero unless zero truly means none. Otherwise, an unknown quantity can be mistaken for a measured value. If a value cannot be verified, leave it unresolved and note the limitation instead of inventing a replacement.
Finish with a reviewable cleaning record
After making changes, compare the cleaned dataset with the original. Check row counts, category totals and any summaries relevant to your question. A large shift may be justified, but it should be explainable. Spot-check changed records as well: a rule that fixes one spelling variant might accidentally group two distinct categories.
A short cleaning log can state which fields were checked, how duplicates were judged, which labels were mapped and how invalid or missing values were handled. Save uncertain decisions separately for follow-up. This record makes the dataset easier to reuse and helps readers distinguish what the data originally said from the choices made to prepare it. The aim is not a flawless-looking table; it is an analysis whose inputs and adjustments can be understood.
