Most roommate-money chaos traces back to two questions getting mixed up: who paid the bill, and who owes for it. Keep them separate and the math mostly runs itself.

So put the full bill in one Amount column. One row, one total. Record who paid it separately, then calculate each roommate's share from percentages, and send any money that actually changes hands to a separate Settlements tab. That's what keeps a $1,200 rent row from blending into a later $600 transfer.

The layout below is a two-roommate starter using Alex and Jordan. Replace those names with your household's. It handles equal splits, custom percentages, personal charges, and later reimbursements without treating a transfer as a new expense.

Start with one row per bill

Every shared cost gets exactly one row: a rent charge, a utility bill, a grocery run, whatever the household covered together. The full total goes in Amount, and you never create a second expense row for each roommate.

Column What it stores Example
Date When the charge occurred 2026-01-01
Description What the money paid for January rent
Category Rent, utilities, groceries, or another label Rent
Paid by Roommate who paid the bill Alex
Amount Full charge before the split $1,200
Split method Equal, custom, usage-based, or personal Equal
Alex % Alex's agreed share as a percentage 50%
Jordan % Jordan's agreed share as a percentage 50%
Alex share Formula showing Alex's dollar share $600
Jordan share Formula showing Jordan's dollar share $600
Split check Confirms percentages total 100% OK
Notes Receipt reference or a short explanation January lease payment

Amount is the entire charge. Enter it once.

The share columns describe responsibility, not who handed over the card. Paid by and the percentage columns answer different questions, and the sheet only balances if you keep them apart.

Two people sometimes split the paying itself, say each covered half of one grocery run. Use separate rows for that, with the amount each person actually paid and the same bill reference on both. Never put two names in Paid by.

Add the share formulas

Type 50%, not 50. Sheets treats those values very differently, and only one of them splits a bill correctly.

Format the percentage columns as Percent and the share columns as Currency. Then enter these formulas in row 2:

Cell Formula Purpose
I2 =IF($E2="","",ROUND($E2*$G2,2)) Calculates Alex's share
J2 =IF($E2="","",ROUND($E2*$H2,2)) Calculates Jordan's share
K2 =IF($E2="","",IF(ROUND(SUM($G2:$H2),4)=1,"OK","Check split")) Checks that the percentages total 100%

Copy I2:K2 down the sheet. When K shows Check split, the percentages don't total 100%. Fix that before you look at anyone's balance.

Build the workbook in Google Sheets

  1. Create a blank spreadsheet and rename the tabs Expenses, Settlements, and Balances.

  2. On Expenses, paste the headers from the table above into row 1. Keep the order consistent with the formulas.

  3. On Settlements, add the columns shown in the next section. One row per actual transfer between roommates.

  4. Format dates, amounts, and percentages with the appropriate number format. Google's number-formatting help covers currency, dates, and custom formats.

  5. Add data validation to Paid by, From, To, Split method, and Status. A short list keeps Alex, alex, and Alex from becoming separate names.

  6. Freeze row 1 and add a filter. Protect formula columns I:K and the Balances tab, and leave the input cells editable.

A protected range helps prevent accidental edits. It isn't a backup, and it's no substitute for a clear household rule everyone actually agreed to.

Record reimbursements in a Settlements tab

A reimbursement is a transfer, not a new expense. It stays off the Expenses tab entirely.

Date From To Amount Reference Status
2026-02-05 Jordan Alex $25 January groceries Paid

Use Pending if Jordan hasn't sent the money yet, then switch it to Paid once the recipient confirms the transfer.

Thing is, fronting the full amount doesn't make the charge fully yours. If Alex buys $50 of groceries for both roommates, enter Paid by: Alex, Amount: $50, and shares of 50% and 50%. Alex's net position reflects only the $25 Jordan owes, not the entire $50.

Save Alex at 100% and Jordan at 0% for charges that belong entirely to Alex. For a transfer, use the Settlements tab instead of changing the original expense row.

Calculate current balances

List each roommate once in Balances!A2:A3. Everything below assumes the tab names and the Alex and Jordan columns used so far. On signs: positive means the group owes that person, negative means that person owes the group.

Column Formula in row 2 Meaning
B, Share of expenses =IF($A2="Alex",SUM(Expenses!$I$2:$I$100),SUM(Expenses!$J$2:$J$100)) That person's assigned share
C, Paid for bills =SUMIF(Expenses!$D$2:$D$100,$A2,Expenses!$E$2:$E$100) Bills that person paid
D, Before settlements =C2-B2 Net position before transfers
E, Sent =SUMIFS(Settlements!$D$2:$D$100,Settlements!$B$2:$B$100,$A2,Settlements!$F$2:$F$100,"Paid") Paid settlements sent by that person
F, Received =SUMIFS(Settlements!$D$2:$D$100,Settlements!$C$2:$C$100,$A2,Settlements!$F$2:$F$100,"Paid") Paid settlements received
G, Balance after settlements =D2+E2-F2 Remaining amount to receive or pay

Copy B2:G2 down to row 3. Pending settlements don't affect the result, since the formulas only summarize Paid rows and never send or verify money. Column G will happily produce a confident-looking number even with a duplicate row sitting quietly in the middle of Expenses, though, which is why the weekly review later in this article exists.

Add category totals

Category totals show where household money actually goes. Put a category such as Rent in A2 of a summary area, then use this in B2:

=SUMIF(Expenses!$C$2:$C$100,A2,Expenses!$E$2:$E$100)

For an automatically grouped summary, use:

=QUERY(Expenses!A1:L100,"select C, sum(E) where C is not null group by C label sum(E) 'Total'",1)

Keep settlements out of these totals. A reimbursement changes who has paid, but it doesn't create another grocery or utility charge.

Choose a fair split rule

Turns out there's no single fair split. The right rule depends on the expense and on what the household agreed to, not just the number of people in it.

Split rule When it may fit What to record
Equal Shared rent or a household bill used similarly by everyone Matching percentages
Usage-based Utilities or supplies used at different levels An agreed percentage
Room-size-based Bedrooms differ in size or value A written percentage or dollar rule
Per-person A meal, guest expense, or item used only by some roommates 0% for nonparticipants
Nights stayed Temporary housing or a roommate who was away Agreed nights or a related percentage
Income-based A household that has explicitly chosen contributions based on income The agreed ratio, not an automatic assumption

Write unusual rules in Notes or a separate household agreement. A clear rule matters more than a clever formula.

Rounding bites at three roommates. A $2,000 bill split at 33.33%, 33.33%, and 33.34% produces shares of $666.60, $666.60, and $666.80. Agree in advance on who receives the extra cent or cents.

Share the sheet carefully

Invite roommates by email instead of an open link. Editor access makes sense for whoever enters bills, and Viewer or Commenter for someone who only needs to check the balance.

Keep names, rent amounts, and payment references limited to the people who need them. Protect the formula cells and the summary tab, leave the entry columns open, and review the access list when someone moves out.

Use a simple review routine

Bills go in late. Someone renames a roommate. Then two people swear the balance is wrong. A short shared review catches all of it while the receipts and payment details are still findable:

Timing Action
After paying a bill Enter the date, description, category, payer, full amount, and split
After sending money Add a Settlements row with the actual amount and status
Weekly Fix blank fields, duplicate charges, and any Check split result
Monthly Review balances together and export or copy the records if you need an archive

Avoid the common spreadsheet mistakes

Mistake Better approach
Entering the total once for each roommate Enter the full bill once and use share columns
Typing 50 instead of 50% Enter a percentage and format the cell as Percent
Using 100% for the payer on every bill Use 100% only when that person owns the whole charge
Adding a reimbursement as a new expense Record it on Settlements
Marking a transfer as paid too early Use Pending until the recipient confirms it
Adding a roommate without updating formulas Add their percentage, share, check, and balance logic

If a balance ever looks wrong, check for duplicate expense rows and transfers marked Paid too early. Both problems hide quietly, and the total still looks plausible.

Expand the template for more roommates

A third or fourth roommate just means one percentage column and one share column per person. Keep all the percentage columns together, then place the share columns after them.

If the percentage columns are G:I, the split check becomes:

=IF($E2="","",IF(ROUND(SUM($G2:$I2),4)=1,"OK","Check split"))

Add each roommate to Balances and connect that name to the correct share column. For bills that exclude someone, enter 0% for that person and make the participating percentages total 100%.

Participation changes often in some households. If yours is one of them, a separate Splits tab is cleaner. Use one row per expense and roommate with columns such as Expense ID, Roommate, and Share amount. The share total for a name in Balances!A2 can then use:

=SUMIF(Splits!$B$2:$B$100,A2,Splits!$C$2:$C$100)

To be honest, that normalized layout takes more setup up front. It buys you a sheet that doesn't need restructuring every time someone joins, leaves, or sits a bill out.

Test one bill before sharing

Start with one bill you already understand, like the next utility payment. Enter the full Amount once, confirm the split check says OK, and add a settlement only when money actually moves. That small test catches most formula and naming errors before the sheet becomes the household record.

After that, share the sheet and enter the next real bill together. Fixing a formula in week one beats untangling months of entries later.