A free split expenses spreadsheet, and how its formulas work
One row per expense, a 1 under everyone it concerns, and the Balances sheet tells each person what they are owed or owe. The template is free, has no macros and opens in Excel, LibreOffice and Google Sheets.
5 min read
To split expenses in a spreadsheet, list one expense per row with its amount and who paid, then put a 1 under each person it concerns. Each person's balance is what they paid minus their share of every row. Our free split expenses spreadsheet (.xlsx) does this for up to six people, to the cent, and checks that the balances add up to zero.
It has no macros and needs no sign-up. It opens in Excel and LibreOffice, and in Google Sheets through File > Import.
What is in the file
Three sheets. How to use repeats the instructions below. Expenses has room for 200 rows. Balances works out each person's position and checks the whole thing.
On the Expenses sheet, row 1 holds the names, in columns E to J: replace Léa, Sam, Noé and Kenza with your own, six people at most. Then one row per expense:
| Date | Item | Amount | Paid by | Léa | Sam | Noé | Kenza |
|---|---|---|---|---|---|---|---|
| 07/12 | Groceries | 84.30 | Léa | 1 | 1 | 1 | 1 |
| 07/12 | Gas | 62.00 | Sam | 1 | 1 | 1 | 0 |
| 07/13 | Cabin | 420.00 | Léa | 1 | 1 | 1 | 1 |
These three rows are the example shipped in the file, an invented weekend. Kenza came by train, so she has a 0 under the gas. The file works in any single currency; the amounts here read as dollars.
The person columns are what most homemade sheets lack. Without them, you know what was paid but not who it falls on, and there is no way to get a balance.
How the formulas work
Grey columns to the right do the arithmetic; leave them alone. Column K counts the participants on the row. One "Share of" column per person then divides the amount between the 1s, to the cent: when the amount does not divide evenly, the leftover cents go to the first participants on the row, so the shares always add back up to the amount. Three people splitting $10 gives 3.34, 3.33 and 3.33.
The Balances sheet then reads, for each person:
- Put in: the total of the rows where they are the payer, a
SUMIFon the Paid by column; - Falls on them: the sum of their Share column;
- Balance: put in minus falls on them. Positive means they are owed money, negative means they owe it.
If you prefer to build your own sheet, the core is the same two formulas. With payers in column D and amounts in column C, what Léa put in is =SUMIF(D:D,"Léa",C:C). Add a column that divides each amount by its number of participants, =IF(SUM(E2:H2)=0,0,C2/SUM(E2:H2)), and what falls on Léa is =SUMPRODUCT(E2:E201,I2:I201). That simple version can be a cent off on amounts that do not divide evenly; the template's share columns are there to avoid exactly that.
The check cell
Every balance sheet must add up to zero: what some are owed is exactly what the others owe. The Check cell at the bottom of the Balances sheet tests that, and also that the shares add up to the total spent. If it says a row is filled in wrongly, the Check column on the Expenses sheet points to the row: no participant on it, or a payer whose name is not in row 1.
Repayments along the way
When someone pays back part of what they owe before the end, enter it as a row like any other: paid by the person paying back, with a 1 under the person receiving it and 0 everywhere else. Their balance goes up by that amount, the receiver's goes down by the same, and nobody else is affected.
Settling up from the balances
The template stops at the balances; it does not list the transfers. One simple rule works by hand: the person who owes the most pays the person owed the most, as much as the smaller of the two amounts, then you repeat with what is left. Each transfer brings at least one person to zero, so a group of n people never needs more than n − 1 transfers with this rule.
With the example: Noé sends Léa $146.73, Kenza sends Léa $126.07, Sam sends Léa $84.75. Léa receives $357.55 and everyone is at zero.
Where a spreadsheet stops
The template says what it does not do: unequal shares, several currencies, expenses that come back every month. Past six people, you would have to add columns and extend every formula.
The other limits come from how people use it. Entering an expense on a phone, in the right row and the right cell, is awkward, so entries get saved for later and forgotten. The file usually belongs to one person, who ends up chasing everyone else. For two or three people over a short trip, none of that matters much. For a household that shares costs for months, it usually does.
Frequently asked questions
Does the template work in Google Sheets? Yes. Use File > Import in Google Sheets. Sharing the sheet with the group by link lets everyone add their own rows.
How do I record an expense paid by two people? Make two rows, one per payer, each with the amount that person paid and the same participants.
Can I split unevenly, by income or by room size? Not in this template: each row is split equally between the 1s. For a split by percentages or shares, you need a weight per person and per row, which is where an app becomes easier.
Why are my balances a cent off from my own calculation? Because the template keeps the cents and gives the leftover ones to the first participants on the row. That is what keeps the total at exactly zero.
Kotisso, the app we publish, keeps the same kind of tally from a phone, with equal, share, percentage or exact-amount splits and expenses that repeat weekly or monthly. Only the person who creates the group needs an account, and the tally exports for free as a CSV file with one column per person, plus a PDF. It does not move money, and there is no iOS app: start a group on Kotisso.
