Spreadsheet cleanup

How to reconcile a messy purchasing spreadsheet

Keep every row traceable to its source, explain the cleanup rules, and list the records that need a decision.

· About 3 minutes

A purchasing file can look tidy while still containing an incorrect vendor match or a missing order line. A useful cleanup should let someone trace each result back to its source and understand which questions remain open. Start with a working copy, clear rules, and a check that every row and amount is accounted for.

1. Preserve the source and define a row

Keep the original export unchanged. Record its date, filename, and row count, then give each source row a stable reference. Before removing duplicates, establish what a row represents: an order line, shipment, receipt, or invoice line. Two rows with the same purchase order and item may represent separate deliveries.

Confirm which reference files to use for vendor IDs, item codes, and other fields that must match an approved list. Do not assume the newest-looking spreadsheet is the right one.

2. Write the rules before applying them

Separate formatting changes from business decisions. Trimming spaces may be straightforward; deciding that two vendor names represent one company needs evidence. Preserve meaningful leading zeros, punctuation, and case in identifiers. Confirm date formats, currency, and units before converting values.

Keep a rule log with the affected field, transformation, and reference used. If a rule could merge distinct records, test it on examples and get the process owner’s approval first.

3. Keep uncertain records visible

In the real procurement example, currency text is converted to numeric values without changing the pound amount. An extra space is removed from a source column heading so matching fields appear in one column. Possible duplicate transactions are kept for review.

Use an exception log with the source row, issue, evidence needed, decision owner, and status. A matching name or transaction reference is a reason to check the source, not proof that two rows are the same transaction.

4. Check the finished file against the source

The procurement handoff preserves all 7,787 selected spending lines. The original, prepared, and supplier-report totals are reconciled in the downloadable workbook, and no source line is removed.

Those checks explain where the source values went. They do not prove that every underlying transaction is correct. Keep calculated amounts separate, reconcile each currency separately, and record the checks required before any live use.

Before you use the cleaned file

  • Can every output row be traced to its source?
  • Do row counts and comparable totals reconcile?
  • Are excluded duplicates retained and explained?
  • Do unresolved records have a decision owner?
  • Are formulas, dates, identifiers, and representative edge cases checked?

Common failures include deleting apparent duplicates too early, treating blanks as zero, and hiding unresolved records to make the totals look cleaner. A clear handoff includes the original data, cleaned view, rules, and exceptions.

Start with one manageable file

The fixed spreadsheet cleanup packages each cover one flat CSV file or one XLSX file with one worksheet, containing one rectangular table with one header row and an input file up to 12 MB. The $49 USD simple cleanup covers up to 20,000 data rows, 20 columns, three agreed repeatable cleanup rules, and one consolidated in-scope preference revision. The $99 USD standard cleanup covers up to 50,000 data rows, 25 columns, five rules, two consolidated in-scope preference revisions, before-and-after sums for up to three agreed numeric columns, and a rule-by-rule validation report. A free fit review checks the file structure, requested rules, and how many records may need your decision before the accepted scope and delivery date are confirmed in writing.

Use these checks with your own records, systems, and approved policies. Linked portfolio examples identify their public sources and limits.

Have a task in mind?

Start with the result you need.

Tell us what you need
Need help using this site?

New here? You do not need to know the service name. Tell us what you have now and what a useful finished result would look like.

  1. Start a short inquiry. A few sentences are enough, and no account or files are needed.
  2. Compare services, package prices, and quote options, or see complete public-source examples before you ask.
  3. Already working with Cleargrain? Use Client Login for private requests, files, messages, and project review.

Still unsure? Describe the result you need, including technical detail if useful. Larger spreadsheets and custom work are welcome; we’ll review the task before agreeing on the work and price.

What do the file types and technical terms mean?
PDF
A document you can read in a browser or PDF reader.
XLSX
An Excel workbook. Open it in a spreadsheet app to inspect the full example.
DOCX
An editable Word document. Open it in Word or another compatible document app.
CSV
A plain spreadsheet file. Open it in a spreadsheet app to see rows and columns.
TXT
A plain-text checklist or template. Open it in a text editor or copy its text.
SOP (standard operating procedure)
A step-by-step guide for a recurring task.
CRM (customer relationship management)
A system used to organize customer or contact records.
Workflow
The steps that move a task from start to finish.
Flat file
One table of rows and columns, with one header row.
Exception list
Records that need a decision or more information, with references to the original rows.
Source-ready procedure
A procedure written from approved material you already have, rather than from a supplied walkthrough.
Preference revision
One collected round of requested adjustments within the agreed work. Correcting work that fails the written scope is separate.
White-label support
Work prepared for your agency to review and present in its own branding.

To download a file, select its download link. It may open in your browser or appear in Downloads. If the browser asks, choose where to save it. Downloading an example does not place an order.