5 Copy-and-Paste AI Prompts to Clean Up Messy Bookkeeping Spreadsheets
Five battle-tested ChatGPT prompts that turn chaotic freelancer bookkeeping spreadsheets into clean, accountant-ready data in minutes — no formulas required.
If you run a one-person business, your bookkeeping spreadsheet probably looks like a crime scene: duplicate rows, inconsistent date formats, vendor names spelled four different ways, and a 'Misc' category doing way too much heavy lifting. The good news — you don't need a bookkeeper or a new app. Five well-crafted AI prompts can do most of the cleanup work for you. Here they are, in the order you should run them.
What you'll need before you start
- Your transactions exported as CSV (Google Sheets → File → Download → CSV).
- Any LLM with a chat interface — ChatGPT free, Claude free, or Gemini all work.
- About 20 minutes of focused time once a month.
Tip: redact account numbers and full card numbers before pasting. The prompts below only need date, description, and amount columns to work.
Prompt 1 — Normalize vendor names
'AMZN Mktp', 'Amazon.com*A1B2C', and 'AMAZON DIGITAL' are all the same vendor. This prompt collapses them so your reports stop double-counting.
You are a meticulous bookkeeping assistant.
Below is a CSV of bank transactions with columns: date, description, amount.
For each row, return a new column "vendor" with the cleaned, canonical vendor name.
Rules:
1. Strip transaction IDs, store numbers, and city codes.
2. Collapse known variants (e.g. "AMZN Mktp", "Amazon.com*A1B2C" → "Amazon").
3. Title-case the result.
4. If unclear, set vendor to "Unknown" — do not guess.
Return ONLY a CSV with columns: date, description, amount, vendor.
Transactions:
{{PASTE_CSV}}Prompt 2 — Standardize dates and amounts
Mixed formats like 5/3/26, 2026-03-05, and 'Mar 5' break every pivot table. This prompt forces ISO dates and signed numeric amounts.
Reformat the CSV below.
Rules:
- Convert every date to ISO 8601 (YYYY-MM-DD). Assume US format (M/D/Y) when ambiguous.
- Convert every amount to a signed number with 2 decimals. Debits negative, credits positive.
- Remove currency symbols and thousands separators.
- Drop any row where date or amount cannot be parsed, and list those rows separately under a "## Skipped" heading.
Return the cleaned CSV first, then the skipped rows.
Data:
{{PASTE_CSV}}Prompt 3 — Auto-categorize transactions
This is the workhorse. It assigns every row to a fixed category list so your end-of-year totals actually mean something. Pair it with our full walkthrough in the
[Categorize a Year of Business Expenses with One Prompt](/posts/expense-categorization-prompt) guide for the multi-pass version.
You are a bookkeeping assistant for a sole trader.
Assign each transaction below to ONE category from this fixed list:
Software, Marketing, Travel, Meals, Office, Contractors, Bank Fees, Personal, Income, Uncategorized.
Rules:
1. Use "Personal" for anything that looks non-business (groceries, Netflix, etc.).
2. Use "Uncategorized" only when truly unclear — do not guess.
3. Add a "confidence" column: high, medium, low.
Return CSV with columns: date, vendor, amount, category, confidence.
Data:
{{PASTE_CSV}}Prompt 4 — Detect duplicates and suspicious rows
Review the CSV below for data hygiene issues.
Flag rows that match ANY of these patterns:
- Exact duplicate (same date, vendor, amount).
- Near-duplicate (same vendor + amount within 2 days).
- Round-number amounts over $500 with vague descriptions.
- Negative amounts in an income category, or positive amounts in an expense category.
Return a markdown table with columns: row_number, issue_type, reason, suggested_action.
Data:
{{PASTE_CSV}}Prompt 5 — Generate a month-end summary
Once the data is clean, this final prompt gives you a one-glance financial snapshot you can send to yourself, your accountant, or your future self at tax time.
Below is a cleaned, categorized CSV of business transactions for {{MONTH_YEAR}}.
Produce a concise month-end summary with:
1. Total income, total expenses, net profit.
2. Top 5 expense categories by dollar amount.
3. Top 3 vendors by spend.
4. Any month-over-month anomalies if I provide a prior month for comparison.
5. A 2-sentence plain-English commentary I could send to my accountant.
Format the response in clean markdown with headings.
Data:
{{PASTE_CSV}}Which LLM should you use for each prompt?
| Prompt | Best free model | Why |
|---|---|---|
| 1. Normalize vendors | ChatGPT (GPT-4o-mini) | Strong pattern matching on short strings |
| 2. Standardize dates | Claude Haiku | Strict instruction-following on formatting |
| 3. Categorize | ChatGPT (GPT-4o-mini) | Best balance of speed + accuracy |
| 4. Detect duplicates | Claude Haiku | Better at multi-condition logic |
| 5. Month-end summary | Gemini Flash | Long context + clean markdown output |
Putting it all together — the 20-minute monthly ritual
- Export last month's transactions as CSV (2 min).
- Run Prompt 1 → paste output back as input for Prompt 2 (5 min).
- Run Prompt 3 on the standardized data, then Prompt 4 to catch issues (8 min).
- Run Prompt 5 on the final cleaned data and save the summary in your records (5 min).
Want to automate this entire chain so it runs by itself? See our guide on [automating invoice reminders with Zapier](/posts/automate-invoice-reminders-zapier) — the same pattern works for piping CSVs through an LLM on a schedule.
Frequently asked questions
- Strip account numbers, card numbers, and personal addresses first. The prompts only need date, description, and amount. For maximum privacy, run a local model with Ollama or use Claude with 'Do not train on my data' enabled in settings.