T Bruce Studio practical guide · CSV and Excel data quality · Reviewed 2026-10-30
The principle: Keep the raw export untouched. Never delete or overwrite rows merely because they look inconvenient. A customer export, inventory list and financial transaction register can require different cleaning rules.
1. Make an immutable working copy
Save the original CSV as it arrived, with a descriptive filename and date. Record the source system, export period and the expected number of rows if known. Work on a separate copy. When reviewing client data, use the minimum necessary information and avoid exposing confidential details in public tools.
2. Import carefully: do not just double-click
In desktop Excel, use Data → From Text/CSV (the wording varies across versions). Inspect the delimiter, encoding and preview. If Excel proposes automatic type conversion, make deliberate choices before loading.
| Column | Example | Recommended check |
|---|---|---|
| Product code | 00123 | Keep as text if the leading zeros belong to the identifier. |
| Long reference | 123456789012345678 | Keep as text. Excel numeric precision can alter long identifiers. |
| Transaction date | 03/04/2026 | Confirm whether the source means 3 April or 4 March before converting. |
| Price | 1,250.00 | Confirm decimal and thousands separators, currency and whether negatives are allowed. |
Microsoft's Excel data-import documentation explains several automatic conversions that can remove zeros or change long identifiers.
3. Check for structural problems
- Headings: Confirm every column has a meaningful, unique header. Two columns both called “Amount” are hard to audit.
- Row shape: Every data row should map to the expected columns. A comma inside a quoted address should not become a new column.
- Blanks: Count missing values by column, then decide whether empty means unknown, zero or not applicable.
- Types: Look for amounts stored as text, inconsistent dates and identifiers turned into numbers.
- Duplicates: Distinguish a genuinely repeated export row from two legitimate transactions with the same customer and amount.
4. Use a small test sample before cleaning everything
Choose representative rows: a blank field, a code with leading zeros, a quoted comma, a date at risk of misinterpretation and a deliberate repeated entry. Decide the expected result for each row before applying any transformation.
A safe text cleanup might remove accidental leading or trailing spaces in an ordinary name field. It should not trim meaningful spaces from free-form descriptions or silently convert identifiers.
5. Validate the exported workbook
- Compare row counts before and after; explain any intentional exclusions.
- Spot-check the original and cleaned values for critical identifier, date and amount columns.
- Check that totals agree where valid, but do not assume every amount column should total to a financial control figure.
- Open the final workbook in Excel or your intended spreadsheet software to confirm the file really works.
- Retain the source file and a clear change log.
Run a free first-pass data check
Paste a small, non-sensitive table into Spreadsheet First Aid. It flags exact duplicate rows, blanks and inconsistent column patterns locally in your browser. It does not understand the meaning of an invoice or certify that duplicates should be removed.
For an eligible simple CSV or XLSX that needs a bounded repair and validated editable handover, explore Spreadsheet Rescue · R990. Complex Power Query, macros, cross-system integrations and uncertain corrections remain outside the automated fixed scope.