Excel bookkeeping for freelancers – practical guide

Excel is fine—if you keep it disciplined

Many freelancers stay in Excel for bookkeeping because it is flexible and cheap. The key is to separate raw data from reports, lock formats, and avoid manual typing where possible.

The following setup works for small teams and can be migrated to accounting software later without losing structure.

Workbook structure

• Raw data: one sheet per bank account with CSV imports. No formulas, just clean columns.
• Categories: mapping table (description → category/VAT code).
• Reports: P&L, VAT summary, and cashflow pulling from raw data via INDEX/MATCH or XLOOKUP.
• Settings: VAT rates, fiscal year dates, and account numbers.

Templates & formulas

Lock cell formats early: DD.MM.YYYY for dates, comma decimals, and text for IBAN/reference numbers. Use tables to avoid range chaos.

• Lookup rules: MATCH descriptions to categories; use PRIORITY for exact matches first.
• VAT split: formulas that split gross into net/VAT per category.
• Checks: totals vs. bank balances; flag when they diverge to catch missing rows.

Importing bank statements

Avoid typing rows. Import clean CSVs so Excel does not reformat dates or decimals. KontoCSV outputs UTF-8 CSV and Excel (XLSX) with balance columns intact, ready to paste into your raw data sheet.

• Keep a consistent column order (date, description, amount, balance, reference).
• Use Power Query or copy-paste into a formatted table to avoid broken formulas.
• Store original PDFs plus CSVs for auditability.

Automation ideas

• Rules-based categorization using a helper sheet of keywords.
• Monthly import macro: load new CSVs, append, refresh pivots.
• Conditional formatting to flag duplicates or missing balances.
• Export summaries to your tax advisor as CSV/PDF once a month.

Common mistakes

• Mixing raw data with formulas in the same sheet.
• Letting Excel auto-format dates or strip leading zeros from references.
• Dropping the balance column, making reconciliation harder.
• Not backing up the workbook or versioning CSV imports.

Conclusion

Excel bookkeeping works if you structure it well and avoid manual typing. Clean CSV imports from KontoCSV keep balances and formats intact, so your formulas stay stable.

Try the workflow with three free pages. If you later switch to accounting software, the same CSV exports will import cleanly.