A good split expenses spreadsheet keeps three numbers in separate columns: the full charge, each person's share of it, and what's still owed between you. Put one shared cost on each row, add a check column that confirms the shares add up to the total, and record paybacks as their own rows so they settle what's owed without counting as spending. Set up that way, the sheet stays fair for months and tells you at a glance who owes whom.
Below is a column-by-column setup that works in Google Sheets or Excel, a worked month with three roommates, and what to do when someone moves out.
Why most shared spreadsheets drift
The common first version has a date, what was bought, who paid and how much. It works for a week. Then someone buys a dinner only two of you ate, someone else pays a roommate back, and a third person types the payback as another expense. By the end of the month nobody trusts the total.
The fix is to stop asking one column to do three jobs. What left someone's card, what each person used, and what's still owed are different numbers, and the sheet should keep them apart.
The columns
Set up one tab for the household with these columns, left to right:
- Date: the day of the purchase or payment.
- What: a short note, such as groceries or internet.
- Type: either Expense or Settle-up. This one column is what keeps paybacks out of spending.
- Paid by: the person whose card or account paid.
- Total: the full charge, exactly as it appears on the card or bank statement.
- One share column per person: how much of that total belongs to each of you.
- Check: the total minus the sum of the shares.
For an even split between three people, each share cell is the total divided by three, for example =E2/3. For an uneven split, such as a dinner only two of you ate, type the shares in directly and leave the third person at 0.
The check column is the part most sheets skip. In Google Sheets or Excel it's =E2-SUM(F2:H2), assuming the shares sit in columns F to H. It should read 0 on every expense row. When it doesn't, a share was typed wrong, and you catch it the day it happens instead of at the end of the month. If a split leaves cents over, such as $100 three ways, give the extra cent to one person so the row still checks to 0.
A worked month with three roommates
Say roommates A, B and C share a flat. In one month their sheet has these expenses:
- groceries, paid by A: $150, split evenly, so $50 each
- internet, paid by B: $75, split evenly, so $25 each
- cleaning supplies, paid by C: $36, split evenly, so $12 each
- a dinner at home for A and B only, paid by A: $90, so $45 for A, $45 for B and $0 for C
- electricity, paid by B: $120, split evenly, so $40 each
The total column adds up to $471. The share columns add up to $172 for A, $172 for B and $127 for C, which is also $471. If those two sums ever differ, the check column will already have flagged the row.
Now the balance. For each person, it's what they paid minus their share:
- A paid $240 and used $172, so A is owed $68
- B paid $195 and used $172, so B is owed $23
- C paid $36 and used $127, so C owes $91
The balances add up to zero, which they always should. C settles up with two payments: $68 to A and $23 to B.
In a sheet, a small summary block does this for you. Paid by A is =SUMIFS(E:E, D:D, "A", C:C, "Expense"), A's share is =SUMIFS(F:F, C:C, "Expense"), and the balance is the first minus the second. Copy it for B and C.
Recording a settle-up
When C sends A the $68, add a new row: date, a note like settle-up, type Settle-up, paid by C, total $68, and no shares. Leave the share columns empty on settle-up rows, because nobody used anything.
Then extend each balance: what you paid in expenses, minus your share, plus settle-ups you sent, minus settle-ups you received. After both payments, A is at $68 minus $68, B is at $23 minus $23, and C is at minus $91 plus $91. Everyone is at zero, and the month's spending is still $471.
This is where many shared sheets go wrong. If the $68 lands as an expense, the month looks $68 bigger than it was. If A counts it as income, A's own budget is off by $68 the other way. A payback only moves money that was already owed.
What it means for your own budget
The share columns are also your real spending. In the month above, A's card shows $240 of charges, but A spent $172: $50 of groceries, $25 of internet, $12 of cleaning supplies, $45 of dinner and $40 of electricity. When A's budget app counts the full $150 grocery charge, groceries look three times higher than they were.
So when you check your own budget, use your share column, not the total. We covered the same idea for utilities and internet with roommates, and in more detail for Splitwise and your budget.
Keeping it fair over time
A few habits keep the sheet useful past the first month:
- Settle on a fixed day. Once a month is enough for most households. Waiting longer lets small balances grow into awkward ones.
- Agree on the split before the bill. Decide which costs are even and which aren't, and write it at the top of the tab. Rent is often the uneven one; the rent split calculator works out a split by income or by room size.
- One row per charge. Don't combine two grocery runs into one row. Matching each row to one card charge makes it easy to check against statements.
- Start a new tab each month or year. Carry the closing balances over as the first rows, typed as Settle-up, so nothing is lost.
When a roommate moves out
Settle everything up to the move-out date first, so the person leaving ends at zero. Prorate bills that cover days after they leave: if they move out on the 10th of a 30-day month, they owe a third of that month's internet. Shared things one person bought, like a couch for the flat, are a separate conversation. Either the others buy out their share of what it's worth now, or it stays with whoever paid for it. Write down what you agreed in the sheet's notes.
If keeping the sheet starts to feel like a second job, Monio connects to Splitwise and links each shared bill to its card charge, so your budget counts only your share and paybacks settle what's owed instead of counting as income.
Questions
What columns does a split expenses spreadsheet need?
Date, what was bought, a type (Expense or Settle-up), who paid, the full charge, one share column per person and a check column. Keeping the full charge, each share and what's owed in separate columns is what stops the sheet from drifting.
How do you split expenses in Google Sheets or Excel?
Put each shared cost on its own row with the total and each person's share, then use SUMIFS to add up what each person paid and what they used. The difference between the two is what each person is owed or owes.
How do you record paying a roommate back in a spreadsheet?
Add a settle-up row with who paid and the amount, and leave the share columns empty. It moves both balances toward zero without making the month's spending bigger.
How do you check a split expenses spreadsheet is right?
Add a check column with the total minus the sum of the shares, which should be 0 on every expense row. The balances for everyone together should also add up to zero.