Five spreadsheet data-quality problems that create duplicate work

Duplicate entities

Two rows can refer to the same client, candidate, property, supplier or job without looking identical. Trading names, abbreviations, old addresses and spelling differences make a simple exact-match search incomplete. Start by defining what an entity means in this particular sheet and which fields provide evidence of a match. A shared email address may be meaningful for one dataset and completely normal for another.

Create a possible-duplicate queue rather than merging rows automatically. Show the compared values, sources and last update. A named owner should decide whether to merge, relate or keep the records separate. Preserve both original rows and record the decision: a mistaken merge can attach information to the wrong party or make reconciliation harder.

Inconsistent identifiers

Identifiers fail quietly when a reference is stored as a number in one place, text in another or with prefixes and leading zeros removed. Staff then search manually, create a replacement row or treat a real record as missing. Inventory the identifiers used across the workflow: customer number, ATS record, property reference, invoice number, job code or another locally defined key. State which system issues each one and whether its format can ever change.

Preserve the supplied value before normalising it. Apply explicit matching rules such as trimming spaces or standardising a known prefix; do not remove characters because they look inconvenient. Flag collisions, pattern failures and unmatched references. When no reliable identifier exists, resolve that operating-design problem rather than asking AI to invent one.

1. List every identifier, issuing system, allowed format and responsible owner.
2. Keep the raw value beside any normalised comparison value.
3. Test leading zeros, punctuation, case, blank cells and spreadsheet auto-formatting.
4. Send collisions and unmatched references to a named reviewer.

Mixed formats

Dates, telephone numbers, postcodes, currency and percentages can behave inconsistently in sorting, formulas and imports. A date such as 03/04/26 is ambiguous without an agreed convention; a percentage may be stored as 5, 0.05 or text. Profile each column before changing anything, keep a protected source copy and work on a controlled version.

Choose a canonical format for the destination, then convert only when the source meaning is clear. Flag ambiguous dates, unexpected units and values that would lose precision. Test exports because visible spreadsheet formatting may disappear in CSV. A human reviewer should approve conversions with operational or financial consequences.

Missing required fields

A blank cell is only a problem if the field is required for a defined next step. Mark requirements by workflow stage rather than declaring every column mandatory. A contractor invoice may need a job reference before matching, while a marketing list may not. Record whether a blank means not supplied, not known, not applicable or removed. Those states should not be collapsed into one empty value if staff act differently on them.

Build a completeness view naming the missing field, source record, queue and permitted action. It may prepare a factual follow-up, but a person should approve communication and alternatives. Track repeated causes such as an unclear form or failed integration; correcting intake may be better than repeatedly repairing the spreadsheet.

1. Define required fields for the selected hand-off or decision point.
2. Use explicit missing-value states where they affect the next action.
3. Route gaps to an owner instead of silently dropping incomplete rows.
4. Track upstream causes and update the controlled intake where appropriate.

Uncontrolled free text

Free text is useful for genuine notes but unreliable as a hidden status system. Values such as in progress, underway, waiting and client contacted may mean the same thing—or important differences no one has defined. Review distinct values and ask the people who use them what action each value triggers. Do not map terms automatically until the owner approves a controlled vocabulary and a route for unclear historical entries.

Separate structured fields from notes. A controlled status, owner, next-action date and exception reason can sit beside a limited notes field. Preserve original text so reviewers can check context. Sensitive personal information in notes needs appropriate access, purpose, retention and deletion controls. Use the smallest taxonomy that supports real work.

Build a controlled cleanup workflow

Choose one spreadsheet with a business owner, a defined downstream use and enough representative rows to expose exceptions. Freeze an authorised source copy, profile the five problem types and agree proposed rules before applying changes. Produce a change set or review workbook that shows the original value, proposed value, reason and confidence. The owner accepts, changes or rejects each material correction before an operational file is replaced.

Test normal rows, blanks, duplicates, ambiguous dates, identifier collisions, unexpected free text and a failed export. Reconcile row counts and key totals where relevant, but do not treat totals alone as proof of correctness. Record approvals and keep a rollback route. If the spreadsheet feeds client, financial, legal, compliance or operational decisions, the responsible person must review the output and retain that decision; this guide does not replace specialist advice.

1. Define the source, owner, purpose, destination and approved backup.
2. Profile issues without altering the operational workbook.
3. Prepare traceable changes and exceptions for human review.
4. Validate the approved output in its real downstream process.
5. Document recurring controls so the same problems do not immediately return.