How to split expenses in a spreadsheet (free template) — and where it stops working

Someone in the flat says “let’s just put it in a spreadsheet.” It is a reasonable instinct: everyone knows how a spreadsheet works, nobody has to install anything, and the costs are simple — rent, internet, groceries, the odd thing one person covers for another. The question is what the sheet should look like so it still tells the truth in month three, with forty rows, when someone asks who owes what.

The answer is a small ledger with three sheets and two formulas. You can download the template or build it by hand in five minutes. And the honest answer to “will it work” is: a spreadsheet works well for two or three people splitting mostly equally, and it breaks at three predictable points — unequal splits, a second currency, and the moment only one person is still updating it.

One row per expense: the ledger model

The mistake most homemade sheets make is tracking balances — a column per person, typed by hand, adjusted after every purchase. Within a month nobody trusts it, because nobody can see how a number got there.

The model that holds up is a ledger: every expense is one row recording three facts — who paid, how much, and for whom. Balances are never typed; they are derived from the rows by formula, so any number traces back to the lines that produced it, and a disputed entry is fixed by correcting one row.

That is also how every expense-splitting app stores your data. The spreadsheet is the same idea with the conveniences removed.

The three sheets

The template has three tabs. Building it yourself, make them in this order.

1. People. Column A, one name per row, under a header. Everything else refers to this list, so spell each name one way and keep them short.

2. Expenses. Five columns: Date, Description, Paid by, Amount, Split among. “Paid by” is one name from the People sheet. “Split among” is either all or a comma-separated list of names — Ben for something only Ben owes, Ana, Cem for something two people share. One row per expense, oldest at the top; nobody edits a row except to correct a mistake.

3. Balances. One row per person with three numbers: Paid (what they put in), Owes (their share of everything) and Net (paid minus owes), plus a check row proving the nets add up to zero. If they do not, a name is misspelled somewhere.

The formulas

Two helper columns on the Expenses sheet do the work. In column F, the number of people in the split:

=IF(E2="all", COUNTA(People!A2:A50), LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+1)

For all it counts the People sheet; otherwise it counts the commas and adds one. In column G, the per-person share: =D2/F2. Fill both down.

On the Balances sheet, with the person’s name in A2:

• Paid: =SUMIF(Expenses!C:C, A2, Expenses!D:D) — every amount where they were the payer.
• Owes: =SUMIF(Expenses!E:E, "all", Expenses!G:G) + SUMIF(Expenses!E:E, "*"&A2&"*", Expenses!G:G) — their share of every all row, plus their share of every row that names them.
• Net: =B2-C2. Positive means the group owes them; negative means they owe the group.

One caveat: the wildcard finds a name inside a longer one, so “Ana” also matches “Dana”. Keep names distinct, or add surnames. Every formula here is plain SUMIF, COUNTA, LEN and SUBSTITUTE, so the file behaves the same in Google Sheets, Excel and LibreOffice.

A worked example: a flat of three

Ana, Ben and Cem share a flat. One month, the Expenses sheet has four rows:

The last row is the interesting one. Ana paid the bike shop because Ben’s card was declined; it is a loan, not a shared cost, so the split is Ben alone. No special case needed.

Shares: the three all rows give each person 500 + 15 + 40 = €555, and Ben also carries the €60 repair, so his share is €615. Paid: Ana €1,560 (rent plus the repair), Ben €45, Cem €120. The Balances sheet reads:

Both totals are €1,725 and the nets sum to zero, so the sheet is consistent. To settle: Ben pays Ana €570 and Cem pays Ana €435. Ana receives €1,005, exactly her net. Two transfers, everyone at zero.

Recording a repayment, and settling up by hand

When Ben pays Ana the €570, delete nothing. Add a row: “Ben settles up”, paid by Ben, amount 570, split among Ana. Ben’s Paid rises by 570, Ana’s Owes rises by 570, and both nets land where they should — Ben at zero, Ana at +435. A repayment is just an expense whose only beneficiary is the person being paid.

What the sheet will not do is tell you who should pay whom. With three people you read it off the Net column. With six and a mix of positives and negatives it is a small puzzle: negative nets pay positive nets, in the fewest payments that do it. How debt simplification works explains the matching; in a spreadsheet you do it on paper.

Where the spreadsheet stops working

Three things push a flat, a trip or a friend group past what the template can carry.

1. Unequal splits. The template splits every row equally among the people named. The moment one row is “Ana pays 40% because her room is bigger”, or a restaurant bill where Cem only had a main, the formula no longer fits. You can fake it — one row per share — but every workaround is one more thing a flatmate has to remember in month three. There are five ways to split an expense, and the spreadsheet does one of them.

2. A second currency. A weekend away adds a row in a different currency, and the Amount column silently adds euros to złoty. You can add a rate column and a converted-amount column, and now every row needs a rate typed in by hand. It survives one trip, not a group whose members live in different countries. Splitting bills across currencies covers what such a group needs.

3. One owner. This is the one that ends most sheets. A shared spreadsheet is technically editable by everyone, but in practice one person owns it and the others post receipts in the group chat for that person to type in. When the owner is busy for two weeks, the ledger is two weeks wrong, and nobody can check their own balance in the shop. The problem is not the software; updating a spreadsheet is a chore and logging an expense in the moment is not.

When a spreadsheet is the right tool

Be fair to the other side. Two people splitting rent and a few bills, or three flatmates who split almost everything equally, are well served by the template. It is free, visible to everyone, and it will still open in ten years. If your costs look like the worked example, you may never need anything else. The point of keeping track of who owes you money is a shared record both sides trust, and a well-built sheet is one. For rent, utilities and groceries, splitting rent and bills with roommates covers the conventions that keep the rows simple.

Move on when you find yourself adding helper columns, converting currencies, or reminding one person to update it.

Where Donget comes in

Donget stores the same ledger the template does — each expense has a Paid by, an amount and the people it is split among, and the Balances tab derives every number from those rows. The difference is the three failure points above. A row can be split Equal, by Amount, by Percentage, by Multiplier (shares) or item by item through Items and Costs, so the unequal cases are a tap rather than a workaround. Each group has a Currency setting, and an expense can be entered in another currency. And because everyone adds expenses from their own phone and the group syncs in realtime, there is no owner to wait for — Everyone’s balance shows the same nets to everyone, and Settle up suggests the fewest transfers that clear the group, the part you were doing on paper.

People join through an Invite link. For the flat in the worked example, either tool does the job; for a six-person trip with a shared chalet, the spreadsheet is where the arguments start. To choose deliberately, expense-splitting apps compared sets out what each free app does and where each one, Donget included, falls short.

Frequently asked questions

Does the template work in Google Sheets and Excel?

Yes. It uses only SUMIF, COUNTA, LEN and SUBSTITUTE, which behave the same in Google Sheets, Excel and LibreOffice Calc. Open the .xlsx in Excel, or upload it to Google Drive and open it with Sheets.

How do I add an expense that only some people share?

Type the names, separated by commas, in the Split among column instead of all — for example Ana, Cem. The share is the amount divided by the number of names listed, and only those people’s Owes figures change.

Can the spreadsheet split an expense unequally?

Not directly: every row is split equally among the people named. The workaround is one row per share with the same payer — €40 split Ana, €60 split Ben. Fine occasionally; if most of your expenses are unequal, move to a tool with percentage and item splits.

How do I record a repayment?

As a row: paid by the person repaying, the amount, and the person being repaid as the only name in Split among. Both nets move and the history stays complete. Never delete or edit an old row to reflect a payment.

How do we work out who pays whom?

Read the Net column. Negative nets pay positive nets until everyone is at zero; with three people it is obvious, with six it takes a few minutes on paper. The spreadsheet will not suggest the transfers.

Is a spreadsheet enough for a flatshare?

For two or three people splitting mostly equally in one currency, yes, as long as someone keeps it updated. It stops being enough when splits are unequal, a second currency appears, or only one person is still entering expenses.

The bottom line

A spreadsheet that records who paid, how much and for whom — and derives the balances instead of typing them — is a perfectly good way for two or three people to split equal costs, and the template gives you that in one download. Watch for the three signs it has outgrown the job: unequal splits, a second currency, and one person doing all the typing.

Download Donget free and keep the same ledger on everyone’s phone, with the unequal splits, the currencies and the settle-up worked out for you.