Define “clean” before changing the file.

Cleaning is a rule system, not a cosmetic pass.

A spreadsheet can look consistent while still being wrong for its intended use. Dates may have matching formats but represent different timezones. Names may use consistent capitalization but refer to different entities. Duplicate rows may share visible text while representing separate locations, or appear different while representing the same organization.

Begin with the downstream use. State what system, analysis, report, import, or operating decision the cleaned file must support. That determines which columns are required, which formats are valid, what uniqueness means, and when a questionable value should be corrected, flagged, preserved, or left blank.

Preserve the received data and define the release.

The project should distinguish the received source files from the customer-facing release. Cleaning should not silently overwrite the only copy of the input. Name the file or worksheet that controls the starting row count, columns, and values, and identify which customer-requested outputs belong in the final package.

Decide whether the release contains only the cleaned dataset or also an agreed validation summary, exception file, change indicator, or rejected-record file. Internal production artifacts do not belong in the customer package unless the confirmed scope explicitly calls for a customer-facing version.

File-level decisions

• Which file and worksheet is the controlling input?
• Are hidden rows, hidden columns, filters, formulas, merged cells, and comments part of the source?
• Should formulas be preserved, converted to values, or excluded?
• Must the original row order remain intact?
• Which file types, worksheet names, and customer-facing outputs are required?

Write the target schema column by column.

For each column, define its name, meaning, type, allowed format, required status, and permitted values. If columns are renamed, provide the exact target names. If multiple source columns are combined or one column is split, describe the transformation and the precedence when values disagree.

Schema rules should also describe invalid values. A numeric identifier may need to remain text so leading zeroes are preserved. A date might require ISO format, while an address may remain in separate fields rather than one display string. A field can be optional but still require validation when present.

Separate presentation cleanup from meaning changes.

Normalization can cover whitespace, capitalization, punctuation, phone numbers, URLs, dates, categories, units, state names, country codes, and other repeatable formats. Every transformation needs an agreed rule. Trimming surrounding spaces is different from changing internal spelling; mapping “NY,” “New York,” and “N.Y.” to one value is different from guessing a location from an incomplete address.

For controlled categories, provide the allowed values and the mapping table or logic. Define whether unknown source values remain unchanged, become a named exception value, or are left blank. If unit conversion is required, identify the source unit, target unit, precision, and rounding rule.

Normalization questions

• Which characters, spaces, or punctuation may be changed?
• Which values map to a controlled category?
• Are capitalization changes display-only or part of identity matching?
• Which dates, phones, addresses, URLs, identifiers, or units have required formats?
• How should an input that cannot be normalized without guessing be represented?

Define duplicates with keys and survivorship rules.

Duplicate removal is not one action. Exact duplicate rows, repeated identifiers, similar organization names, and multiple records sharing an address can require different treatment. Name the exact key or approved matching rule used to form a duplicate group.

Then define survivorship: which row is retained, which fields can be combined, what source controls conflicting values, and whether removed rows need to appear in an agreed customer-facing exception output. If fuzzy matching is permitted, define the fields, thresholds, exclusions, and treatment of uncertain matches. Ambiguous candidates can be flagged rather than merged automatically.

Choose how missing and invalid values behave.

Blank, null, zero, “N/A,” “unknown,” and an empty formula result can carry different meanings. Define which representations count as missing for each field and which output value should be used. Do not replace a missing fact with zero unless zero is a supported value in that field.

If enrichment or verification is part of the job, name the fields and approved sources. Specify whether an unverifiable value remains unchanged, is cleared, is flagged, or moves to an exception output. A cleanup project should not create facts that the input and approved sources cannot support.

Set verification boundaries and source precedence.

Verification is different from formatting. It compares a defined field against customer-provided evidence or an approved public source. Name the fields to verify, the permitted sources, the freshness requirement, and the source that controls when evidence conflicts.

Decide how the release shows verified, unverified, conflicting, and unavailable results. This may use a status column, an observation date, a source URL, an exception reason, or separate fields. Verification coverage should be measurable against a named population, not described only as a general quality expectation.

Make exception handling part of the contract.

Exceptions are expected whenever the source contains ambiguity, contradictions, unsupported values, or records outside the rules. Define the exception categories and what happens to the main dataset. A record can remain with a flag, move to a rejected-record output, preserve the source value, or leave the target field blank.

Also define escalation. If the number or type of exceptions materially changes the work, the job should pause for scope review rather than silently applying a new rule. This keeps the release aligned with the customer’s approved requirements.

Turn the release requirements into tests.

Acceptance tests should be executable against the delivered files. They can verify exact filenames, worksheet names, columns, types, formats, required-field completion, category validity, uniqueness, formula treatment, row counts, provenance fields, and exception outputs. Tests should refer to the agreed schema rather than subjective appearance.

When an acceptance rule uses a percentage, define its numerator and denominator. For example, “at least 98% of included rows contain a valid customer_id” is different from “98% of nonblank customer_id values have a valid format.” The correct metric depends on the purpose of the file.

Example of a confirmable cleaning request.

This example establishes measurable behavior but still requires the actual source file, field mappings, category table, privacy review, size, and exception volume before price and feasibility can be confirmed.

What happens after submission.

1. Start a private job and upload the source file only inside the approved customer workspace.
2. Describe the downstream use, known problems, target schema, duplicate rules, verification needs, and delivery format.
3. Morrow Creek reviews the file structure, data sensitivity, feasibility, exceptions, and acceptance requirements.
4. You receive the exact paid scope and price before committing to production.
5. The customer release is checked against the confirmed rules before payment and download.

For related planning, review data cleaning and verification, Excel and CSV cleaning, and the complete custom data project guide.