Excel Data Cleanup Services

The file that will not import

An import error rarely describes the full problem. One rejected row may be the first invalid date, not the only one. A successful import can be worse when leading zeros disappear, identifiers become scientific notation, duplicate contacts are created or empty-looking cells contain spaces.

The first control is to stop changing the submitted file. OveliTHub takes a locked or cryptographically hashed copy where appropriate, records the filename, size, worksheet names, received time and agreed scope, then works from a separate version. The original is never “cleaned in place.”

Before quoting the full project, a representative sample is profiled. The defect profile counts structural, type, value, formula, duplication and destination-rule issues and identifies decisions the client must make. This avoids pricing a workbook only by row count when the real work sits in inconsistent exceptions.

The eleven things that are usually wrong

1. Near-duplicate records. “Acme Ltd,” “ACME Limited” and “Acme Ltd.” may be one organisation—or distinct entities with a similar name. Email, address, identifier and context must support the decision.
2. Dates stored as text. Values that look like dates may not sort, group or calculate correctly, and ambiguous forms such as 03/04/26 cannot be converted safely without a locale rule.
3. Numbers stored as text. Amounts, quantities and percentages can arrive with apostrophes, spaces or symbols. Conversely, codes with leading zeros should often remain text.
4. Trailing and non-breaking spaces. Invisible characters make equal-looking values fail lookups, joins, validation and duplicate tests.
5. Merged cells. A visual heading spanning several columns interrupts a rectangular data table and can break sorting, filtering and row-based import.
6. Inconsistent countries and states. “UK,” “U.K.,” “United Kingdom” and a blank do not form a controlled category. A mapping must preserve meaning and distinguish unknown from not applicable.
7. Mixed currencies and units. A column labelled “Price” may combine currencies, tax treatments or units. Conversion requires source, rate or rule, date and approved target—not a formatting change.
8. Broken or hard-coded formulas. Lookups return errors after rows move; one monthly formula contains a typed value; references point to an old sheet; totals exclude appended rows.
9. Hidden rows and columns with stale values. Filtering or presentation can conceal records that remain inside formulas, exports or totals. Hidden does not mean excluded.
10. Multiple header rows. Titles, notes, repeated headers and blank separators can make the actual field names and data range ambiguous to an importer.
11. Several values in one free-text field. A cell contains two phone numbers, several owners or a product plus quantity. Splitting it requires a destination model and reliable delimiter or review.

Other problems can include inconsistent case, blank rows inside tables, duplicate column names, invalid emails, formula-result errors, mixed decimal separators, external links, macros, protected ranges, comments carrying real instructions and manual colour coding used as data. The profile records what exists in this file rather than applying a generic checklist blindly.

“Clean” is defined by the destination system

A sheet is not clean because every column has the same colour and every visible value looks tidy. It is clean for a stated purpose when its structure, data types, required values, keys, categories and file format satisfy the destination rules and the unresolved exceptions are visible.

The reader should specify the destination before the quote. A phone column that accepts punctuation in one CRM may require digits and a separate country code in another. Blank may mean “unknown,” “not yet collected,” “not applicable” or “intentionally withheld.” Those meanings should not be collapsed for visual neatness.

Checking records against an external authority is a separate data verification service. Appending third-party facts is data enrichment. This project remediates the values and structure already supplied unless another scope is explicitly approved.

The cleanup follows a controlled sequence

1. Receive and preserve. Transfer through the approved route, inventory files and worksheets, retain the untouched original and establish who may access it.
2. Profile the structure. Identify ranges, headers, types, formulas, links, hidden content, validation, protection, macros and named ranges relevant to scope.
3. Measure defects. Count blanks, invalid types, duplicate candidates, inconsistent categories, formula errors, structural breaks and destination-rule failures.
4. Agree the rules. For each defect class, document transformation, authoritative source, survivorship, exceptions, rounding, locale, null and prohibited assumptions.
5. Test a representative slice. Apply the rules to common and difficult rows, compare results with client expectations and revise before bulk transformation.
6. Apply repeatable transformations. Use documented formulas, queries, scripts or controlled manual actions suited to the file. Preserve keys and row lineage.
7. Verify the output. Recount rows and keys, test destination rules, reconcile control totals, recalculate formulas and compare a risk-weighted sample to the original.
8. Test the actual import. When authorised and practical, run a small destination test or validate against its current template before producing the final batch.
9. Deliver and explain. Provide the cleaned file, original, change log, exceptions, rules and a short handover with any remaining client decisions.

The process can be repeated for correction, but it is not pitched as a recurring transformation pipeline. Scheduled batches and ongoing system-to-system processing belong to data processing services.

Nothing is deleted silently

The change log records rule ID, affected field or rows, before and after treatment, reason, operator or automated step, time and verification. Aggregate transformations can be logged by rule with a linked affected-row list rather than producing an unreadable cell-by-cell diary.

Rows that cannot be resolved confidently move to a flagged exceptions sheet. Each exception retains its stable key, issue, source values, reason, available options, proposed action if any and client decision status. Rejected rows are reconciled to the starting population. Deletion, exclusion and merge are different outcomes and are counted separately.

Deduplication is a judgement, not a button

Exact matching can find identical stable identifiers. Fuzzy matching can generate candidates using normalised name, email, phone, address or other permitted features. A similarity score is not proof that two records represent the same person, company, invoice or product.

The client approves matching rules and thresholds. High-confidence candidates can follow an agreed merge rule; ambiguous pairs go to review. The survivorship rule defines which values win: most recent verified value, authoritative system, nonblank approved source, highest-quality record or field-specific precedence. Conflicting high-risk fields are never silently chosen.

A merge record lists the retained ID, retired IDs, matched evidence, surviving fields and decision. That allows a later CRM or ERP import to update relationships rather than merely remove a row.

Excel, Google Sheets and CSV fail in different ways

Excel workbooks

Microsoft’s current Excel specifications and limits, checked 2 September 2026, state that a worksheet supports 1,048,576 rows by 16,384 columns. That is a hard grid boundary, not a recommended operational size. Performance also depends on memory, formulas, styles, links, add-ins and the environment. Microsoft notes that older .xls sheets have a much smaller row limit than .xlsx, so the received format is part of profiling.

Formula-heavy models require a separate impact map. We record formula cells, hard-coded exceptions, dependencies, named ranges, external links, macros and calculation settings relevant to the work. Cleanup is applied to a copy, the workbook is recalculated in an approved environment, critical outputs reconcile and formula changes appear in the log. If formulas cannot be understood or safely tested, data-table remediation is separated from model repair rather than risking the model.

CSV files

CSV is plain delimited text, not a workbook. Microsoft’s current text and CSV guidance, checked 2 September 2026, says saving to text formats saves only the active worksheet and warns that unsupported features can be lost. Its current save guidance says text-format saves remove formatting.

CSV therefore does not preserve formulas as a functioning model, multiple sheets, styles, validations, merged cells or workbook controls. Delimiter, quoting, line breaks, decimal and date interpretation need testing. Leading zeros and long identifiers can be changed when a CSV is opened using default type detection. Microsoft’s UTF-8 CSV guidance describes using a byte-order mark or importing through Power Query/Text Import for correct opening. We agree encoding and import method, then test accented and non-Latin characters.

Google Sheets

Google Sheets is useful for controlled collaboration, comments and validation, but imported workbooks, functions, permissions and connected data can behave differently. Google’s current data-ingestion guidance, checked 2 September 2026, recommends File > Import over direct copy-and-paste for larger supported files and points very large CSV analysis towards BigQuery with Connected Sheets when a direct import is too large.

We do not convert a complex workbook to Sheets merely for convenience. The client identifies the required final environment, and the test covers formulas, values, locale, dates, protected areas, validations and imports that matter there.

What you get back

• Untouched original: the received source preserved and clearly labelled, with access limited under the agreement.
• Cleaned working file: the remediated workbook, Sheet or CSV in the agreed destination structure and version.
• Change log: transformations, affected population, approvals and checks linked to rule identifiers.
• Flagged exceptions sheet: every unresolved, excluded, ambiguous or decision-dependent row with its source lineage.
• Rules document: field definitions, types, allowed values, key rules, deduplication and survivorship, locale, nulls and future-entry controls.
• Verification report: row and key reconciliation, defect results, control totals, formula or import tests, samples and residual risks.

Secure handling is defined before transfer: approved storage, named access, encryption available in the chosen tools, retention, local-copy restrictions, incident reporting and deletion or return after acceptance. A redacted structural sample can often support initial assessment without exposing full commercial data.

Stop the same mess coming back

The rules document becomes the prevention plan. Use data validation and controlled dropdowns for stable categories, keep identifiers and dates in defined types, separate multiple values into appropriate fields, protect formulas, assign one accountable file owner and establish an intake method for changes.

Visual formatting should not carry business meaning by itself. A red cell needs a status value; a hidden row needs an explicit exclusion flag; a worksheet tab should not be the only place where period or region is encoded. Entry instructions and validation messages should explain the accepted format.

When concurrent users, relationships, history, volume, permissions or automation exceed what the workbook can govern reliably, the answer may be a database, CRM, ERP, PIM or other system—not a larger spreadsheet. OveliTHub can prepare an import-ready dataset but does not choose or implement the destination platform under this cleanup scope.

Ongoing entry of new records belongs to data entry services, and sustained daily volume may require a remote data entry team. Analysis and dashboards belong to data analytics services.

Pricing is based on defects, decisions and risk

Row count matters for processing and verification, but it does not predict effort alone. A large file with five consistent, rule-based defects can be easier than a small workbook with twenty inconsistent patterns, undocumented formulas, merged layouts and ambiguous duplicates.

The sample quote considers files and sheets, record population, columns, distinct defect classes, destination rules, duplicate-matching complexity, formula and macro risk, external links, transformations, exceptions, review rounds, security conditions, required import testing and deadline. The quote identifies assumptions and what happens if the full file contains materially different defects from the sample.

This is project work with a defined acceptance point, not an automatic retainer. If the same cleanup must be repeated every week, the useful next conversation is about entry controls or a governed processing workflow. Send a sample for a fixed assessment.

Where spreadsheet cleanup stops

• We fix existing supplied structure and values under approved rules; we do not key new records from documents in this scope.
• We do not verify facts against external authorities unless separately scoped.
• We do not append purchased or researched company, person or product attributes.
• We do not interpret accounting, legal, clinical or regulated meanings without an authorised specialist rule.
• We do not create dashboards, business conclusions or management recommendations from the data.
• We do not silently delete records, choose duplicate survivors or repair an opaque financial model without acceptance criteria.

Readers who prefer a self-directed checklist can use the guide on how to clean up an Excel spreadsheet.