Why do simple roommate expenses cause so much friction every month? Almost every household argument boils down to four questions: what was purchased, who fronted the cash, what does each person owe, and when is payment due? Turns out, a Google Sheet handles all four without extra software.

You only need an Expenses tab, a Setup tab, and an optional tab for custom shares. Type bill dates when you know them. Save formulas for repeating rules and nothing else.

Start with a usable column layout

Keep your main ledger clean and predictable.

Column What to record
Date When the expense occurred or was posted
Description A clear label such as October rent or Electric bill
Amount The full bill amount
Category Rent, Utilities, Groceries, Internet, Deposit, Moving, or Other
Paid by The roommate who covered the charge
Split type Equal, Usage-based, Income-based, Room-size, Nights-stayed, Reimbursement, or Custom
Paid date When the payment was made
Due date When the bill or reimbursement should be paid
Paid/settled? A checkbox or Yes/No value
Notes Receipt name, transfer reference, or an explanation

Always include Paid by. Even if roommates split every single line item right down the middle, recording who paid stops confusion months down the road. One person might forget they covered internet in August. The record settles it immediately.

Categories only work if spelling stays consistent across the entire year. If you enter Utilities on three rows and switch to Power on the next four, your year-end sums will split into two meaningless halves. Pick a standard label once and stick to it.

Separate the deadline from the payment date

Due dates and paid dates represent two different events. A due date shows when money must arrive, while a paid date marks when funds actually left someone's bank account. They are never interchangeable.

Calculating a due date from the paid date fails because unpaid rows have no payment date to pull from. Putting timing notes like "2 week" into Split Type causes similar headaches, since that column belongs entirely to division rules. The formula is simple. The underlying household rule requires agreement first.

Situation Best approach
A lease, bill, or invoice gives an exact date Type that date directly into Due date
A reimbursement is due a set number of calendar days after the expense Use an optional Days to due column
The agreement counts business days Use WORKDAY instead of simple date addition
A bill is always due on a confirmed day of the month Use a date formula only after confirming the household rule

When every calculated deadline runs on calendar days, put Days to due in column K. Paste this formula into H2 and apply it only to rows governed by that calendar rule:

=IF(OR(A2="",K2=""),"",A2+K2)

For deadlines counting only business days, switch to WORKDAY instead:

=IF(OR(A2="",K2=""),"",WORKDAY(A2,K2))

If your house excludes bank holidays, list those dates in Setup!D2:D20 and reference that range:

=IF(OR(A2="",K2=""),"",WORKDAY(A2,K2,Setup!$D$2:$D$20))

Never drag formulas over confirmed lease dates or invoice deadlines. Manual due dates come straight from bills, whereas reimbursements follow internal house rules.

Track progress by adding a Status column in L. Assuming you have checkboxes in column I, paste this formula into cell L2:

=IF(H2="","Missing due date",IF(I2=TRUE,"Settled",IF(H2<TODAY(),"Overdue",IF(H2=TODAY(),"Due today","Open"))))

If your sheet relies on a Yes/No dropdown in column I, change I2=TRUE to I2="Yes".

Build the tracker in Google Sheets

  1. Open a fresh Google Sheet and name the first tab Expenses.
  2. Add headers in A1 through J1: Date, Description, Amount, Category, Paid by, Split type, Paid date, Due date, Paid/settled?, and Notes.
  3. Put optional helper headers in K1 through M1: Days to due, Status, and Equal share.
  4. Set column C to currency, then format columns A, G, and H as dates.
  5. Highlight cells beneath Paid/settled? and insert checkboxes, or use a dropdown validation with Yes and No.
  6. Create a Setup tab containing your roommate count and any holiday dates for WORKDAY.
  7. Freeze row 1 and turn on sheet filters to sort by status, date, or category.

Keep your Setup tab lean and uncluttered:

Cell Entry
A1 Setting
B1 Value
A2 Roommate count
B2 3
D1 Holiday dates
D2:D20 Optional dates excluded from business-day calculations

Here is how an active ledger looks with sample records. The label No means the box remains unchecked:

Date Description Amount Category Paid by Split type Paid date Due date Paid/settled? Notes
10/01/2026 October rent $2,400 Rent Alex Equal 10/01/2026 No
10/15/2026 Utilities $180 Utilities Jordan Equal 10/15/2026 No
10/05/2026 Alex's groceries $45 Groceries Alex Reimbursement 10/12/2026 No Jordan and Casey each owe $15 if split equally

Rent stays fixed, but groceries fluctuate.

Pick a split method and record the payer

Split type When it fits What to document
Equal Everyone uses the expense similarly The roommate count
Usage-based People consume noticeably different amounts The agreed measurement or estimate
Income-based Contributions follow an agreed income ratio The ratio and who approved it
Room-size or nights-stayed Occupancy differs between roommates The room or stay adjustment
Reimbursement One person fronts a shared cost The payer and each person's amount owed
Custom The expense does not fit another method Individual shares on a Shares tab

Equal splits require the least explanation. Other methods like usage, square footage, income ratios, or nights stayed often match specific living situations better, though they introduce personal assumptions that can create friction if nobody writes down the exact math beforehand. Agree on numbers early.

For equal divisions, drop this calculation into helper column M:

=IF(OR(C2="",F2<>"Equal"),"",C2/Setup!$B$2)

This formula only calculates the per-person amount. It does not balance who owes what back to the buyer.

Thing is, typing Reimbursement in column F does not calculate individual debt on its own. Many spreadsheet setups apply that tag whenever one tenant fronts cash, including this ExpenseSorted spreadsheet example. Treat that label as a note, not an automatic settlement engine.

When expenses divide unevenly, build a dedicated Shares tab:

Expense Roommate who owes Amount owed Settled?
Alex's groceries Jordan $15 No
Alex's groceries Casey $15 No

If Alex bought those items strictly for personal use, skip the shared sheet altogether. If all three people shared them, Alex absorbs $15 while Jordan and Casey owe $15 each. Clear debts require clear names.

Add a dashboard and overdue view

A summary view keeps people out of the raw data. To aggregate overall spending by category, insert this formula into cell A1 on a new Dashboard tab:

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

Format the calculated total column as currency. Rent, utilities, and grocery totals will refresh on their own without anyone editing row formulas.

If you use a Shares tab, enter your roommates' names in Dashboard!A2:A. Calculate what each person currently owes with this formula:

=SUMIFS(Shares!C:C,Shares!B:B,A2,Shares!D:D,FALSE)

Overdue bills deserve their own dedicated space on your dashboard:

=IFNA(FILTER(Expenses!A2:M,Expenses!H2:H<TODAY(),Expenses!I2:I<>TRUE,Expenses!H2:H<>""),"No overdue items")

Because TODAY() updates daily, this list remains completely accurate without manual tweaking. It acts as a filter, not an automated notification system.

You can also highlight deadlines on the main sheet. Select cells A2:M, open conditional formatting, and add a custom formula like =$L2="Overdue". A second rule using =$I2=TRUE can gray out finished rows, much like the steps shown in this checkbox conditional-formatting guide. Colors make unmade payments obvious.

Share the file without losing control

Click the Share button in Google Sheets and invite roommates through their personal email addresses. Grant Editor status only to housemates who must enter costs or confirm payments. Commenter permissions fit anyone who just asks questions or attaches notes, while Viewer access keeps the sheet read-only.

Check Tiller's sharing and permissions walkthrough if you need a refresher on Drive access levels. Your current Google account share window remains your source of truth.

Clear household habits stop spreadsheet errors faster than complex formulas:

  • Log every bill only once.
  • Record the payer and split type on the very same row.
  • Leave checkboxes unchecked until agreed settlement conditions occur.
  • Write disputed sums in the Notes column rather than editing older numbers silently.

Whenever someone updates an amount, add an explanation in Notes. Roommates trust shared numbers when ledger changes leave a visible audit trail.

Keep receipts and payment records together

Save copies of lease agreements, power invoices, and grocery receipts in a shared household Drive folder. Reference the exact filename or subfolder inside the spreadsheet's Notes column so anyone auditing a charge can match the paper to the digital row without digging through old group texts or searching email inboxes. Matching titles makes verification painless.

When paying through apps, log the date, sent amount, recipient name, and transaction confirmation number right in the sheet. Never store bank logins, account passwords, or full account numbers in spreadsheet notes. Google Sheets only tracks your agreement; it never processes payments or verifies external bank transfers.

Schedule a regular household check-in. Sort the tracker by Due date. Inspect rows marked Overdue or Due today, compare line amounts against your shared receipts, and check off Paid/settled? only after everyone confirms the transaction cleared.

Common mistakes to avoid

Mistake Better fix
A blank Paid date makes the due date disappear Base the formula on the expense date or enter the bill deadline directly
Timing text is stored in Split type Put the timing rule in Days to due
The payer is not recorded Add Paid by to every expense row
A reimbursement row hides individual shares Use a Shares tab with one row per person who owes
Someone marks an item settled too early Agree whether settled means sent, received, or otherwise confirmed
A formula gets overwritten Keep formula columns separate and make a backup copy before major edits

To be honest, most shared trackers do not break from complicated math; they collapse because roommates skip ground rules, overwrite shared cells, or assume everyone defines "settled" the exact same way. Build your three initial sample rows first. Have every roommate review the split types and due-date logic before sharing editor permissions with the whole house.