Shared Expenses Spreadsheet Checklist, No Arguments

A shared expenses spreadsheet works when every row answers five questions: who paid, how much, when, who takes part and how it's split. Miss one and the balance stops adding up.

A shared expenses spreadsheet is a spreadsheet where a group (a couple, roommates, friends on a trip) records every shared expense and works out how much each person owes or is owed. Setting it up properly takes, as a rule of thumb, an hour. Fixing it after the first argument takes a lot longer.

Key takeaways

• Give each question its own column (date, paid by, amount, participation, rule) and lock the Paid by column with a drop-down list of names, so the formula doesn't break because of a lowercase "ana".
• Write down four rules before the first entry: what counts, how each category is split, who logs and within how many hours, and which day closes the month.
• Store a weight in the participation cell itself (1 for equal, 60 and 40 for proportional to income, empty for anyone who sat out). One formula covers every case.
• Only ask for payment when the balances add up to zero. If they don't, there's a value stored as text or a row with no participants.

Which columns does a shared expenses spreadsheet need?

Eight columns, in this order: Date, Description, Paid by, Amount, Rule, one participation column per person, one share column per person and Settled. The five questions get answered on every row, and the balances sheet reads everything without guessing.

• Date (formatted as a date): without it you can't filter by month, and the monthly close turns into manual work.
• Short description ("groceries", "rent", "gas"): three weeks later, nobody remembers a €47.30 entry with no name (the example amounts in this article are in euros).
• Paid by, with a drop-down list: use data validation with the group's names, because "Ana" and "Ana S." count as different people for the sum.
• Amount, numbers only: typing "€" by hand turns the value into text, and the sum silently ignores it.
• Rule (Equal, Income, Fixed amount): it's just a label, but it lets you filter and see why a row was split the way it was.
• Settled (yes/empty): stops you from charging twice for an expense that's already been paid.

Google Sheets is a practical choice, because it lets you edit at the same time in the browser and on your phone, without anyone sending files by message.

What should the group agree on before logging the first expense?

Agree on four rules in writing, on a tab called Rules: what counts, how each category is split, who logs and how quickly, and which day closes the month. A rule that only exists in people's heads is the source of the next argument.

• What counts: only shared household or trip expenses. Personal purchases stay out, so the sheet doesn't become surveillance of anyone's life.
• How each category is split: rent and fixed bills can be proportional to income, groceries split equally. If Ana earns €1,200 and Bruno €800, the split is 60/40, and €700 rent comes to €420 and €280.
• Who logs and when: whoever pays logs it, within the next 48 hours. Anyone who didn't log it has no right to claim reimbursement.
• Closing day and payment method: for example, the 1st of each month, by bank transfer or a payment app.

How do you build each person's balance formula?

A person's balance is what they paid minus the share that falls to them. Positive means they're owed money; negative means they owe. With participation stored as a weight, a single formula calculates every share, and the balances sheet adds up two columns.

Put the weights in the participation cells. With data starting on row 3, Ana's share (column I, with the weights in F, G and H) looks like this:

=ROUND($D3*F3/SUM($F3:$H3),2)

Copy it across to Bruno's and Carla's columns and down. In a spreadsheet set to English, the function names are in English and the separator is a comma.

On a Balances tab, with the person's name in A2, the balance is:

=SUMIF(Entries!$C:$C,A2,Entries!$D:$D)-SUM(Entries!I:I)

For the next person, swap the last column for theirs (J, then K). Don't put the balance inside the entries tab: referring to its own column creates a circular reference.

How does the spreadsheet handle someone who sat out an expense?

Someone who sat out has an empty participation cell, and their share on that row is zero. The expense amount is never edited to "compensate" for the absence. That way the row keeps showing what was actually paid and everyone else's balance comes out right.

Ana pays for groceries, Bruno pays for the internet and Carla pays for a dinner Bruno didn't attend.

Expense	Paid by	Amount	Ana	Bruno	Carla
Groceries	Ana	€90.00	1	1	1
Internet	Bruno	€36.00	1	1	1
Dinner	Carla	€60.00	1		1

Person	Paid	Share	Balance
Ana	€90.00	€72.00	+€18.00
Bruno	€36.00	€42.00	-€6.00
Carla	€60.00	€72.00	-€12.00

The total paid is €186, the shares add up to €186, and the balances add up to zero. Bruno sends €6 to Ana, Carla sends €12, and nobody needs to swap money with each other.

With rounding to two decimals, dividing by three can leave a one-cent difference. Accept that remainder at closing and agree on who absorbs it, instead of tinkering with the formulas.

What logging routine keeps the spreadsheet up to date?

Logging on the same day, by whoever paid, takes about 30 seconds on a phone. Letting entries pile up for "the weekend" is what produces forgotten amounts and arguments about who paid for what.

• Add a Receipt column with the link to the receipt photo for expenses above an amount the group sets. It settles the doubt without an interrogation.
• Open the Balances tab once a week. A balance that grows quickly is a warning to settle before the end of the month.

The spreadsheet is great for custom categories and a yearly history. It falls short when someone in the group never opens the file: in that case, splitting expenses by link means that person doesn't have to get into a sheet or install anything.

What should you check before closing the month and asking for payment?

Before asking for payment, the balances must add up to zero. If they don't, the error is on one specific row, and the steps below help you find it.

1. Add up the balances in a control cell (=ABS(SUM(B2:B4))) and confirm it's close to zero.
2. Compare counts: the number of numeric values must equal the number of filled rows. If it's lower, a value was typed as text.
3. Look for rows without weights: a row with an Amount and no participation leaves the share at zero, and the money "disappears" from the calculation.
4. Send the summary to the group and give them 48 hours to dispute it before asking for payment.
5. Make the fewest transfers: with n people, at most n-1 are enough.
6. Mark Settled on the paid rows and start the next month on a new tab, keeping the previous one as an archive.

Frequently asked questions

How do you log expenses in a foreign currency on a trip?

Add two columns, Currency and Rate, and convert to euros on the row itself: Amount in euros = original amount × rate. Agree beforehand whether you use the day's rate or the one on the card statement. Picking just one rule avoids two identical entries giving different amounts.

How do you stop someone from accidentally deleting a formula?

Protect the share columns and the Balances tab, and leave only the input columns editable for the group. If a mistake gets through, the Google Sheets version history lets you go back to the previous state. Test the recovery once with a dummy value, before you need it.

What if someone in the group doesn't want to open the spreadsheet?

For anyone who refuses to edit spreadsheets, Split The Bill sends a private payment link.