How to separate business and personal transactions in Excel

To separate business and personal transactions in Excel, keep the complete bank export and add a classification column. Mark each row Business, Personal, Transfer or Review, then summarise the business rows separately. Do not delete personal spending from the source data: you still need every movement to reconcile the account.

Download the mixed-account workbook (.xlsx). It includes fictional example rows, a classification dropdown, a business-use percentage and a formula-driven summary. It is an organising tool, not a tax-deduction calculator.

Start with a complete account

Export the full period from the bank, or convert your statement PDFs. Use one account and currency per working sheet. Confirm the statement's date range and check the extracted balances before making classifications.

Retain an untouched export. In the workbook, replace the example input cells with your own rows and leave formula columns intact. Amounts are signed: receipts positive, payments negative. Instructions describe the 500-row working area; extend both formulas and summary ranges if you need more.

Classify by evidence, not the shop name

A purchase at a supermarket could be personal food or office supplies. A deposit might be sales, a loan or money moved from another account. Descriptions help you find evidence; they do not determine the answer.

Use a separate business-use percentage

For a genuinely mixed bill, record the whole bank amount for reconciliation, then apply a documented business-use percentage in a separate column. In the example, £40 paid at 50% business use produces £20 of allocated business spending. The remaining £20 stays visible as non-business cash movement.

The workbook's allocated amount is a working figure. Whether a cost is allowable, capital, subject to limits or treated differently for a company requires a separate decision. The HMRC record-keeping guide describes the records a self-employed person needs; the percentage in this example is illustrative, not an HMRC-approved rate.

Keep transfers and repayments out of spending totals

Moving £300 to savings reduces one bank account but does not create a £300 business expense. Likewise, a card repayment settles the card balance; it is not an additional expense on top of the card purchases.

Record the destination account or matching reference in Notes. Owner contributions and drawings also need their own treatment. If this is a limited company's account, personal spending may involve a director's loan account; do not assume the sole-trader approach applies.

Review what the summary leaves out

The workbook shows classified business receipts, allocated business payments, personal rows, transfers and unresolved rows. Review the unresolved total before using any summary. A neat result with five unclassified transactions is unfinished bookkeeping.

Check three things before handing it over:

1. All bank rows remain in the source and working data.
2. Classifications are supported by invoices, receipts or a written explanation.
3. The full signed total still agrees with the statement movement, even though the business-only total does not.

Make next month easier

A separate business account reduces sorting, but historical mixed statements still need work. Save decisions about recurring payees as suggestions to review next month, rather than permanently assuming every payment to that payee is business-related.

For an annual return, pair this workbook with the Self Assessment records checklist. For a repeatable routine, use the minimal sole-trader bookkeeping system. Convert the source statements first, then classify the rows with the evidence beside you.