Free guide
Messy data to clean spreadsheet: before and after
Five real examples of messy source files, across five different kinds of paperwork — plus the four habits behind every one of them. No signup required; skim it, use whichever parts are useful, and clean your own data with them if you'd rather do it yourself.
Bank statement PDF
Before (the file)
A 3-page bank statement PDF where date, description and amount run together in one column, negative amounts are shown in parentheses, and a running balance is printed after every line.
After (the spreadsheet)
Four columns — Date, Description, Amount, Balance — with every date in the same YYYY-MM-DD format and every amount as a plain number instead of parentheses.
Why it works: A spreadsheet only helps if every row follows the same shape. Splitting the merged text into columns and standardising the date and number format is what makes the data sortable and filterable at all.
Photographed paper receipts
Before (the file)
A folder of 40 photographed receipts in different handwriting, several with an amount that is smudged or hard to read, and no shared layout between them.
After (the spreadsheet)
One row per receipt — Date, Vendor, Amount, Category — with every amount we could not read clearly left blank and flagged in a Notes column instead of guessed.
Why it works: A wrong number quietly entered is worse than no number at all. Flagging what cannot be read keeps the sheet trustworthy, even if a few cells need a second look.
Three supplier price lists
Before (the file)
Three supplier price lists in three different layouts — one Excel file, one PDF table, one a plain-text email — each naming the same kind of product differently ("SKU", "Item Code", "Product #").
After (the spreadsheet)
One merged sheet with a single SKU column, consistent product names, and a Supplier column so every row still shows exactly where it came from.
Why it works: Merging only works if the same thing is always called the same thing. Keeping a Supplier column means nothing is lost in the merge — you can still trace any row back to its source.
Scanned signup forms
Before (the file)
120 paper signup forms scanned to PDF, with names in inconsistent capitalisation and phone numbers written with and without a country code.
After (the spreadsheet)
One row per person — Name, Phone, Email, Join date — with names in one consistent format and phone numbers in one consistent format, ready to import into any system.
Why it works: A list is only as useful as its weakest format. Picking one format per field before import avoids the duplicate-looking entries that inconsistent formatting causes later.
Batch of scanned invoices
Before (the file)
60 scanned supplier invoices, each its own PDF with its own layout, several showing a total with no currency symbol or label at all.
After (the spreadsheet)
One sheet — Invoice #, Supplier, Date, Amount, Currency — where currency is always stated rather than assumed, and every line total is checked against the invoice's own printed total.
Why it works: An unlabelled number is a guess waiting to happen. Naming the currency on every row, and checking totals against the source rather than trusting them blindly, is what makes the sheet safe to act on.
The four habits behind every cleanup above
One format per column, not whatever the source used
Dates, amounts and names each get a single consistent format across every row, even when the original files disagreed with each other.
Flag anything unreadable instead of guessing it
A smudged number or an unclear word is left blank and marked, never filled in with a best guess. A blank cell is honest; a guessed one is a risk.
Keep a line back to the source
A Source, Supplier or file-reference column means every row can still be checked against the original, even after several files have been merged into one.
Check totals against the source, don't just copy them
A printed total can be wrong. Adding up the line items and comparing the result to the source's own total catches mistakes a straight copy would miss.
Would rather not clean it yourself?
Send SheetShift your PDFs, scans or messy lists, and you'll get back one clean, checked spreadsheet — ready within 48 hours.
Get your sheet