Spreadsheets · Instructions · ResearchOn this page
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 controlled copy, a few explicit rules, and a reconciliation you can explain.
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.
Agree which reference files are authoritative for vendor IDs, item codes, and other controlled fields. A recent-looking spreadsheet is not automatically the approved master.
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 obtain the process owner's decision first.
3. Keep uncertain records visible
In the fictional purchasing workbook, “North Star Packaging” is held for review rather than silently matched to “Northstar Packaging.” Another row lists eight units at $6.25 but reports $52. The calculation is $50, leaving a $2 difference. The original amount stays visible while the discrepancy is investigated.
Use an exception log with the source row, issue, evidence needed, decision owner, and status. “Check vendor ID against the approved master” is more actionable than “bad data.” A suspected duplicate should also remain traceable, even when the agreed rule excludes it from the working subtotal.
4. Reconcile the handoff
Account for all rows in mutually exclusive groups. The same simulated workbook has 30 source rows: 23 ready, six held for review, and one duplicate excluded. Using the original reported amounts, $2,121.60 equals $1,782.60 ready, plus $313 held, plus $26 duplicate. The difference is zero.
That check shows where the source values went. It does not prove every underlying transaction is correct. Keep calculated amounts and variances separate, and reconcile each currency separately. “Ready” should mean the row passed the agreed checks, not that a payment or live-system import is approved.
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 $150 USD spreadsheet cleanup pilot covers one provided spreadsheet or CSV with up to 2,000 rows, an exception log, and one round of changes covering the agreed work. Describe the file and intended result before sending data. Scope and timing are confirmed after review. The service excludes accounting advice, financial certification, and live ERP changes.
Examples are fictional. Adapt the guidance to your records, systems, and approved policies.