A shared Google Sheets ledger can keep rent, utilities, groceries, and reimbursements in one place. The reliable setup has three tabs: Expenses, Payments, and Summary.
Who paid, who owes, and who has repaid whom are different facts. Keep them separate. That one choice prevents a reimbursement from looking like a second grocery bill.
This layout works well for a small household with a few named roommates. You can add more people later, but the formulas must grow with the columns.
Decide the split rules first
Agree on the split before anyone enters numbers. Write the rule in the workbook so nobody has to reconstruct the decision weeks later.
| Split method | Use it for | How to record it |
|---|---|---|
| Equal | Rent or shared costs everyone uses | Enter the same percentage for each roommate, such as 25 for four people |
| Room-size | Rooms have noticeably different sizes or features | Agree on percentages that total 100 |
| Usage-based | Only some people use an item or service | Give non-users 0 and record the reason in Notes |
| Nights-stayed | Temporary housing or partial-month sharing | Base the percentages on the nights everyone agreed to cover |
| Income-based | The group wants contributions tied to income | Use only with clear consent and a written rule |
Thing is, a percentage is only fair when everyone understands what it represents. Use one method per expense, and don't quietly change the method halfway through the month.
A reimbursement is not a split method. It records money moving after the original bill has already been logged.
Create the workbook tabs
Start with a blank Google Sheet and create these tabs. Keep the names simple because the formulas below use them directly.
| Tab | Purpose |
|---|---|
| Expenses | Records the original shared bills |
| Payments | Records reimbursements that actually happened |
| Summary | Shows each person's paid, owed, and remaining balance |
| Rules | Optional notes about due dates, split decisions, and receipt storage |
Put the roommate names in the Summary header and use the exact same spelling everywhere. Alex and Alexander are different text to a formula.
Build the Expenses tab
Use row 1 for headers. The percentage columns contain whole numbers, not decimal percentages.
| Column | Header | What to enter |
|---|---|---|
| A | Date | Date the bill occurred |
| B | Description | For example, August rent or electric bill |
| C | Category | Rent, Utilities, Groceries, Supplies, or Deposit |
| D | Paid by | The roommate who paid the landlord, store, or provider |
| E | Amount | Full cost of the bill |
| F | Split method | Equal, Room-size, Usage-based, or another agreed label |
| G | Alex % | Alex's share as a whole number |
| H | Blair % | Blair's share |
| I | Casey % | Casey's share |
| J | Drew % | Drew's share |
| K | Alex owed | Formula-generated dollar amount |
| L | Blair owed | Formula-generated dollar amount |
| M | Casey owed | Formula-generated dollar amount |
| N | Drew owed | Formula-generated dollar amount |
| O | Notes | Receipt link, explanation, or exception |
| P | Percent check | Confirms the row totals 100 |
For a $1,600 rent payment made by Alex and split equally four ways, enter Alex in Paid by, 1600 in Amount, and 25 in each percentage column. The sheet will calculate $400 owed by each person.
For a $120 electric bill paid by Blair, enter Blair as the payer and use the agreed percentages. The payer still has a share of the bill; paying upfront does not make that roommate responsible for the entire cost.
Use only shared expenses. A personal purchase should stay out of the ledger unless the group has agreed that it belongs in the household budget.
Add the formulas
These formulas assume row 1 contains headers and rows 2 through 1000 hold expenses. They use 25 rather than 0.25 for a 25 percent share.
The first owed formula goes in K2:
=IF($E2="","",$E2*G2/100)
Copy K2 across to N2, then copy the row down. Because the amount column is fixed while the percentage reference moves, the copied formulas calculate each roommate's dollar share.
Put this formula in P2:
=IF($E2="","",SUM($G2:$J2))
Copy it down. A complete row should show 100. A smaller or larger number means the split is incomplete or over-allocated.
Turns out, this check catches many errors before they reach the monthly balance. Format columns E and K:N as currency, and format G:J and P as numbers.
Build the Summary tab
Set up the Summary tab like this:
| Cell or row | Label or formula |
|---|---|
| A1 | Metric |
| B1:E1 | Roommate names in the same order used in Expenses |
| A2 | Total paid |
| A3 | Total owed |
| A4 | Balance before payments |
| A5 | Sent |
| A6 | Received |
| A7 | Balance after payments |
Put these formulas in column B, then copy each one across to column E.
| Cell | Formula | What it shows |
|---|---|---|
| B2 | =SUMIF(Expenses!$D$2:$D$1000,B$1,Expenses!$E$2:$E$1000) |
Total expenses paid by the roommate |
| B3 | =SUM(Expenses!$K$2:$K$1000) |
Total share assigned to the roommate |
| B4 | =B2-B3 |
Position before reimbursements |
| B5 | =SUMIF(Payments!$B$2:$B$1000,B$1,Payments!$D$2:$D$1000) |
Money sent by the roommate |
| B6 | =SUMIF(Payments!$C$2:$C$1000,B$1,Payments!$D$2:$D$1000) |
Money received by the roommate |
| B7 | =B4+B5-B6 |
Position after recorded payments |
When you copy B3 across, its owed range should move from column K to L, M, and N. Check that before sharing the workbook.
A positive final balance means the group still owes that roommate money. A negative balance means that roommate still owes the group. If everyone is entered correctly, the balances should add up to zero.
For category totals, put a category such as Utilities in J2 and use =SUMIF(Expenses!$C$2:$C$1000,J2,Expenses!$E$2:$E$1000) in K2. Copy it down for Rent, Groceries, and other categories.
Track reimbursements on a Payments tab
A payment is not a new household expense. It is a transfer that reduces one person's debt and reduces another person's credit.
Use these columns:
| Column | Header | Example |
|---|---|---|
| A | Date | 2026-08-20 |
| B | From | Blair |
| C | To | Alex |
| D | Amount | 300 |
| E | Note | August rent reimbursement |
If Alex paid $1,200 in rent and four roommates split it equally, Alex's balance before payments is positive $900. Each other roommate has a negative $300 balance. When Blair sends Alex $300, enter that transfer on Payments. Blair reaches zero, and Alex's remaining balance falls to $600 because Casey and Drew still owe $300 each.
Do not enter the $300 again on Expenses. That would count the same household cost twice.
To be honest, a single Settled? checkbox is too blunt when someone pays in parts. The Payments tab shows the date, amount, sender, and recipient for every repayment.
If a roommate pays the landlord directly, record that transaction on Expenses with that roommate in Paid by. Record it on Payments only when money moves between roommates.
Set up the sheet in Google Sheets
-
Open Google Sheets, choose a blank spreadsheet, and give it a clear name such as
Household Expenses. -
Add the Expenses, Payments, and Summary tabs. Add Rules if your group wants a written record of decisions.
-
Enter the roommate names in Summary row 1. Copy those exact names into Paid by, From, and To entries.
-
Add the Expenses headers from the table above. Put the owed formulas in K2:N2 and the percentage check in P2.
-
Format Amount and Owed columns as currency. Format the percentage columns as plain numbers, then freeze row 1 so headers remain visible.
-
Test the workbook with a $1,200 rent row paid by Alex and split 25 percent each. Alex should show positive $900 before payments, while each other roommate should show negative $300.
-
Add a test payment from Blair to Alex for $300. Blair should reach zero. Delete the test rows after everyone confirms the result.
-
Use data validation drop-downs for roommate names, categories, and split methods. This reduces spelling differences and makes the log easier to scan.
Log a bill when it arrives. Review the sheet regularly, then reconcile the Summary before rent or another recurring bill is due.
Share the spreadsheet without losing control
Click Share, add each roommate's email, and choose Editor for people who need to enter expenses. Keep general access restricted when the workbook contains rent amounts, receipt links, or other household details.
Editors can change formulas, so protect the formula cells after testing them. Select K:N and P on Expenses, choose Data, then Protect sheets and ranges, and restrict editing to the person responsible for maintaining the formulas. Protect B2:E7 on Summary as well, while leaving the input columns editable.
Use version history if a formula or row changes unexpectedly. A blank template should be copied before real expenses are added, rather than shared as a live household record.
The spreadsheet records agreements and payments. It does not send money or confirm that a payment app transfer completed.
Fix common spreadsheet mistakes early
| Mistake | Better practice |
|---|---|
| Leaving Paid by blank | Enter the person who actually paid the bill |
| Treating a reimbursement as a new expense | Put the transfer on Payments instead |
| Entering 0.25 for a 25 percent share | Enter 25 when the formula divides by 100 |
| Using different versions of a roommate's name | Use drop-downs and consistent spelling |
| Adding a fifth roommate without updating formulas | Add the new percentage and owed columns, then extend the Summary formulas |
| Forgetting to check the percentage total | Review column P before counting the row |
| Losing a receipt or explanation | Add a receipt link or short note in O |
| Marking a refundable deposit like ordinary spending | Give it its own category and agree how any refund will be recorded |
Adding a roommate requires more than inserting a name. Add the percentage column, the matching owed column, the Summary column, and any data-validation options. Test one row afterward.
A spreadsheet is a recordkeeping tool, not a lease or legal agreement. For rent, deposits, or disputes, follow the lease and local rules because the answer can vary by contract and location.
Create the workbook, enter one test rent row, and have every roommate confirm the result. Once the test passes, replace it with real expenses and record each repayment when the money actually moves.