A template for expense tracking, in five columns
A spreadsheet is the right tool for splitting money with a flat or a trip, until it isn't, so here is a clean one to copy, the single formula that produces balances, and the four jobs it will never do.
You are about to build a spreadsheet for a flat or a trip, and you would rather copy a working one. Good instinct. Here is a template for expense tracking you can rebuild in five minutes, the one formula that turns a list of payments into balances, and the four things this sheet will never do for you.
A spreadsheet is the right tool for a while. The day it stops being right is predictable, so we will show you that day too.
Why copy a template for expense tracking
A blank sheet is easy to start and easy to get wrong. Most people add a column too many, forget who a row belongs to, or type a running total that quietly goes stale. Copy a shape that already balances and you skip all of that.
The spreadsheet has kept small groups honest for decades, and this expense tracker template is just the smallest version that still works. If you are splitting a lease, the same idea sits underneath how roommates divide rent.
The five columns, and why not six
A shared expense tracker spreadsheet needs only five columns: date, what, who paid, amount, and who shares it. One row per payment. That is the whole structure.
| Column | Example | What it is for |
|---|---|---|
| Date | 2026-09-14 | When the money moved, so rows sort and you can find the taxi nobody remembers taking. |
| What | groceries | A plain name for the expense, like the ramen night, so a balance stays a story. |
| Who paid | Lucas | The one person who actually paid this bill. |
| Amount | €40.00 | The figure, always with its currency symbol. |
| Who shares it | Lucas, Aiko, Sofia, Tomas | The names splitting this row, however many that is. |
*Five columns, and what each one is for.*
Why not a sixth? The tempting extra is a running total or a per person balance typed into a cell. Don't.
A stored total is a number you have to remember to update, and the first time you forget, the sheet lies. Balances are not data you keep. They are data you compute, every time, from the five columns above.
The one formula that produces balances
Here is an example. Four flatmates, four rows:
- 2026-09-14, groceries, Lucas paid €40.00, shared by Lucas, Aiko, Sofia, Tomas.
- 2026-09-15, ramen night, Aiko paid €32.00, shared by Lucas, Aiko, Sofia, Tomas.
- 2026-09-16, taxi, Sofia paid €24.00, shared by Lucas, Aiko, Sofia, Tomas.
- 2026-09-17, concert tickets, Lucas paid €50.00, shared by Lucas, Aiko.
One formula reads all four rows and returns a person's balance:
= SUMIF(paid, name, amount) − SUMPRODUCT(ISNUMBER(SEARCH(name, shares)) * amount / sharers)
The first half adds everything a person paid. The second half adds their share of every row their name appears in, dividing each amount by the number of names in that row's shares cell. That count is just the commas in the cell plus one. The same expense tracking in excel works the same way in Google Sheets, and it is the honest version of what you do by hand when you split a restaurant bill fairly.
Apply it to the four rows and the balances fall out. Lucas paid €40.00 and €50.00, so €90.00, and his share across the rows is €10.00, €8.00, €6.00 and €25.00, so €49.00. His €90.00 against €49.00 leaves €41.00 owed to him. Aiko paid €32.00 and owes a share of €49.00, so €32.00 against €49.00 leaves €17.00 she owes.
Sofia paid €24.00 and shares €24.00, so she is even at €0.00. Tomas paid nothing and shares €24.00, so he owes €24.00. The €17.00 from Aiko and the €24.00 from Tomas add back to the €41.00 Lucas is owed. The sheet balances to zero, because every euro someone owes is a euro someone else is owed.
How to handle a bill two of four people share
Look at the concert tickets. Lucas paid €50.00, but only Lucas and Aiko went. That is why the fifth column exists. In the who shares it cell you write only their two names, so the formula divides €50.00 by two, not four.
Each of them carries €25.00, and Sofia and Tomas carry nothing for that row. Change the names in that one cell and the whole balance updates. This is the everyday case, the dinner half the flat skipped, and it is exactly the arithmetic behind settling a group up.
The four things a spreadsheet will not do
Build it properly and it will serve you. It will also stop, in four specific places.
First, an edit that keeps history. Change €40.00 to €45.00 in a cell and the old number is gone. There is no record that it was ever €40.00, or why it moved. That is the opposite of an append-only ledger, where a correction is a new entry, and it is why we hold that an edit must restate its split rather than overwrite it.
Second, two currencies. One amount column cannot know that €40.00 and $30.00 are different money. Add them and you get a number that means nothing. Currencies are defined precisely by ISO 4217, and a plain sheet treats them as the same, which is one of the quieter ways money maths goes wrong.
Third, settling in the fewest transfers. Your sheet gives balances. It does not tell Tomas and Aiko who should send money to whom so the group clears in as few payments as possible. That last step is its own small problem, and it is worth doing well.
Fourth, anybody else being able to check it. One person owns the file, edits the file, and everyone else trusts the file. A double-entry system records each amount twice so a second person can verify it, and a spreadsheet does not, which is part of why people eventually compare the apps.
When to stop
Stop the day the sheet cannot answer a fair question. Your flat outgrows a roommate expense tracker the moment someone travels and a second currency lands, or a number changes and you want the old one back, or the group is large enough that the numbers behind shared bills start to matter and one owner is no longer enough. Those days are predictable, and when they arrive, the honest move is a tool built for it, which is also true of rent split apps.
We record who paid and who owes, we keep balances per currency, and we never move or hold your money. When your spreadsheet reaches its edge, Dimesum picks up exactly where it stops.
Common questions
Q: How do you make a shared expense tracker in Excel?
Open a blank sheet and add five columns: date, what, who paid, amount, and who shares it. Enter one row per payment. Then add a small balances area with a SUMIF and SUMPRODUCT formula that takes what each person paid and subtracts their share of the rows they are in. That is the whole build, and it works the same in Google Sheets.
What columns does a shared expense spreadsheet need?
Five. Date tells you when. What names the expense, like the ramen night. Who paid records the single payer for that row. Amount holds the figure with its currency symbol, like €40.00. Who shares it lists the names splitting that row. A sixth column for a running total goes stale the first time you forget it, so let a formula compute balances instead.
How do you work out balances from a list of expenses?
For each person, add up everything they paid. Then add up their share of every row their name appears in, dividing each row's amount by the number of names sharing it. Subtract the second total from the first. A positive result means the group owes them money; a negative one means they owe the group. Every balance should sum to zero.
Can a spreadsheet handle two currencies?
Not on its own. A single amount column cannot know that €40.00 and $30.00 are different money, so it will add them into a total that means nothing. You can add a currency column and track a separate balance for each currency by hand, but the sheet will not convert between them or keep them apart for you. That work stays yours.
When should you stop using a spreadsheet for shared expenses?
Stop the day the sheet cannot answer a fair question. When a number changes and you need the old value back, when a second currency appears, when you want the fewest transfers to settle, or when someone else wants to check the maths, the spreadsheet is past its edge. Those four needs tend to arrive together, sooner than you expect.
Popular posts
- The append-only ledger that keeps split-expense balances exact6 min read
- Why editing a shared expense must restate the split5 min read
- Six money bugs in multi-currency expense splitting6 min read
- Settle up: how to clear group expenses in fewer transfers6 min read
- How to split an itemized bill when one dish wasn't shared6 min read