Sharing expenses in a spreadsheet: the template that works
A spreadsheet is enough to share expenses, as long as you know which columns to create. Here is the full template, the balance formula, and the exact point where it eventually gives out.
5 min read
Before looking for an app, the real question is whether a spreadsheet would do the job. Often, it would. A weekend away with four people, a group gift, two months of shared rent: a shared sheet handles it, for free, without signing anyone up for anything.
It does have to be built properly, though. Most improvised expense-sharing sheets fail for the same reason: they list what was spent without ever saying who it falls on, which makes it impossible to work out a balance. Here is the template that works, and the exact point where it gives out.
The four columns you need
One sheet, one row per expense, and four required columns:
| Date | Item | Amount | Who paid |
|---|---|---|---|
| 12/07 | Supermarket shop | 84,30 | Léa |
| 12/07 | Petrol | 62,00 | Sam |
| 13/07 | Cottage rental | 420,00 | Léa |
That is the bare minimum, and plenty of sheets stop there. That is the mistake: you know what was paid, but not what each person owes.
What is missing is a column per participant, saying whether the expense applies to them. A simple 1 or 0 is enough when everyone splits evenly:
| Date | Item | Amount | Who paid | Léa | Sam | Noé | Kenza |
|---|---|---|---|---|---|---|---|
| 12/07 | Groceries | 84,30 | Léa | 1 | 1 | 1 | 1 |
| 12/07 | Petrol | 62,00 | Sam | 1 | 1 | 1 | 0 |
| 13/07 | Cottage | 420,00 | Léa | 1 | 1 | 1 | 1 |
Kenza came by train, so she is not in on the petrol. Without that column she would pay for it anyway, and nobody would notice before the end of the trip.
The balance formula
A person's balance always reads the same way: what they put in, minus what falls on them. Two formulas, one for each half.
What Léa put in, with the payers in column D and the amounts in column C:
=SUMIF(D:D, "Léa", C:C)
What falls on her, with her participation column as E and row 2 as the first row:
=SUMPRODUCT(E2:E100 * C2:C100 / (E2:E100 + F2:F100 + G2:G100 + H2:H100))
The second one deserves a line of explanation: for each row, the amount is divided by the number of participants on that particular row, and only counted if the person is one of them. That is what lets Kenza skip the petrol without breaking the rest of the sheet.
The balance is then the difference between the two: put in - falls on them.
A positive balance means money is owed to you. A negative one means you owe it.
All the balances together must always come to exactly zero. That is the only check that matters: if it does not come out right, a row has been entered wrong, and there is no point going any further.
The step almost everyone gets wrong: the transfers
Knowing the balances still does not tell you who pays whom. The naive approach is to route everyone through the same person, which multiplies both the transfers and the fees.
The right instinct comes down to one rule: the biggest debtor pays the biggest creditor, you settle whichever of the two is smaller, then start again with what is left.
Take the four balances again: Léa +357,56, Sam -38,04, Noé -146,74, Kenza -172,78.
- Kenza (the biggest debtor) transfers 172,78 € to Léa. Kenza is at zero, Léa drops to +184,78.
- Noé transfers 146,74 € to Léa. Noé is at zero, Léa drops to +38,04.
- Sam transfers 38,04 € to Léa. Everyone is at zero.
Three transfers for four people. That is the worst case under this rule: at most one transfer fewer than there are participants. A sheet that suggests more than that is costing everyone time and money.
The three points where a spreadsheet gives out
It never gives out on the maths. It gives out on how people use it, and always in the same places.
Entering things on your feet. That is the real limit, and it decides everything else. Opening a spreadsheet on a phone, finding the right row, typing an amount into the right cell, one hand full, on the way out of a supermarket: nobody does it. So you record it in the evening, so you forget, so the sheet becomes wrong, and a wrong sheet is worse than no sheet at all, because people trust it.
The single owner. The file belongs to whoever created it. They are the one chasing people, fixing entries, recalculating, and they become the group's bookkeeper without ever asking for the job. Six weeks in, they get tired of it, and the sheet dies.
Shares that are not equal. The template above holds up as long as things are split evenly or not at all. As soon as an expense is split in proportion to income, floor space or nights stayed, columns of 1s and 0s are not enough, and you need a second sheet of coefficients that nobody understands a month later.
When to stay with a spreadsheet, and when to move on
Stay with it if the group is two or three people, if the period is short, if there are few expenses and if everyone already uses one. In that case an app adds nothing that a shared tab does not already do.
Move on as soon as one of those four conditions breaks. In practice: a house share that lasts, a trip with more than four people, or expense sharing that runs over months.
That is exactly where Kotisso takes over. An expense is recorded in ten seconds from a phone, everyone sees their own balance without having to ask, expenses that come back every month are entered once, and the shortest repayment plan works itself out. A group's balances always add up to zero, and you can check it: the accounts export to a spreadsheet, with one column per person, exactly as above.
Everything to do with the accounts is free, and there is no bank account to connect.
Frequently asked questions
Which spreadsheet should I use? The one the group already uses. Google Sheets has the advantage of being shareable with a link and editable by several people at once, which solves part of the single-owner problem. Excel and LibreOffice work fine if the file lives in a shared space.
How do I handle an expense paid by two people? Create two rows, one per payer, each with the amount that person actually put in. A single row with two names in the payer column breaks the formula.
What if someone pays some of it back along the way? Add a repayment row: the amount as a negative on the side of whoever receives it, or more simply a separate transfers sheet whose total you subtract from the balances. Do not record it as an ordinary expense, or it will be split between everyone.
Should I round the figures? No. Keep the cents and let the formula carry them: three people splitting 10 € comes to 3,34 and 3,33 twice, and that is the only split that comes out right. Rounding to 3,33 each makes a cent disappear, and a sheet that does not come to zero casts doubt on everything else.