One person buys groceries, another pays the broadband bill, and a transfer later settles the difference. A normal spending chart can mistake that transfer for another expense or new income. A shared-expenses spreadsheet needs to answer two separate questions: what did the household buy, and who still owes whom?
BankSync can bring supported UK bank records into Excel. Your allocation sheet records the agreement between people. Keep the source transactions intact and calculate settlement separately.
Separate purchase from settlement
| Record | Purpose | Include in household spending? |
|---|---|---|
| Merchant purchase | What the household consumed | Yes, for the agreed household portion |
| Reimbursement | Settles an amount between people | No additional expense |
| Personal portion | Part of a purchase outside the shared budget | Exclude from shared spending |
| Refund | Reverses part of an earlier purchase | Reduce the relevant expense |
Prepare a source sheet and an allocation sheet
Save the workbook in OneDrive. Connect the relevant bank accounts with their owners' authorisation and create a Transactions feed to a dedicated worksheet. Map date, amount, account, description and currency; retain identifiers that let you trace a row back to its source. Verify a known purchase and a known transfer after the first run.
In a separate Allocations sheet, record source reference, purchase description, total, shared amount, payer, each person's agreed share, and review status. Avoid tying allocations only to spreadsheet row numbers: sorting and new imports can move a record. A stable source reference is a better join key.
If personal accounts contain transactions you do not want in a shared workbook, decide the scope before connecting or sharing. A filtered Excel view is a presentation choice, not a separate access boundary.
Work one purchase through the model
Assume Alex pays £120 for groceries and Sam pays £60 for broadband. Both costs are fully shared and the agreed split is equal. Total shared spending is £180, so each person's share is £90.
Alex has paid £30 more than their share. Sam has paid £30 less. A £30 reimbursement from Sam to Alex settles the position. Household spending remains £180 after the transfer.
Illustrative equal split
| Person | Paid merchants | Agreed share | Settlement position |
|---|---|---|---|
| Alex | £120 | £90 | Receives £30 |
| Sam | £60 | £90 | Pays £30 |
| Household | £180 | £180 | Net £0 |
Make settlement auditable
Agree the allocation
Use equal shares, fixed percentages or item-specific amounts. Record the rule; do not infer it from who happened to pay.
Review the purchase
Confirm the shared portion against the receipt. A £100 shop containing £20 of personal items has only £80 to allocate.
Calculate each position
Merchant payments minus agreed share gives the amount overpaid before settlements. Use positive values consistently in the allocation model.
Record the reimbursement
Match the posted transfer to a settlement record. Reduce the amount owed without adding another merchant expense.
Check the household total
All allocated shares should sum to the shared purchase total. Settlement positions should sum to zero for the group.
Handle refunds and unequal splits explicitly
If £20 of the groceries is refunded, link the refund to that purchase and reduce the shared cost to £100. Under the same equal split, total spending becomes £160 and each share £80. Alex's net merchant payment is now £100, so the amount Sam owes becomes £20 before any settlement already made.
If the £30 reimbursement happened before the refund, the revised calculation should show £10 owed back to Sam. Do not erase the first settlement to make the latest result look simpler; retain the history and record the correction.
For unequal shares, store percentages or explicit amounts on the purchase. Check that percentages add to 100%, or that allocated amounts add to the shared portion. Rounding can leave a penny difference; assign it consistently and show the adjustment.
Keep three useful views
Use an unreviewed-purchases view, a settlement summary by person, and a household-spending view by category. Avoid combining all three into one number. The person owed money can differ from the category where spending increased.
At the weekly review, check unmatched transfers, refunds and any purchase whose allocated shares do not add up. Keep UK feeds posted-only unless you deliberately manage pending entries: a pending and final record may arrive separately. Use a normal incremental run rather than repeatedly restarting the feed from the beginning.
If you also need to plan which account pays the next bill, pair this with the payday-to-payday calendar. A fair settlement and enough cash on the payment date are related but different jobs.
Reviewed: 1 November 2026. Product references: Excel setup, UK banks, and transaction handling.