People rarely fight over shared expenses because somebody refuses to pay. The fight starts when nobody agreed on the math before running the card. Two roommates track dinner differently. Suddenly the math derails.
A running note works fine for two people. Add a third roommate or book a vacation house, and that notepad falls apart fast. Now you must track four distinct moving parts: the original vendor invoice, who paid it, each person's exact cut, and the settlement transfers between members.
Can you manage all four moving parts in a free spreadsheet without buying an app subscription?
Yes. A five-tab Google Sheets setup tracks uneven shares, logs receipt links, and updates who owes what in real time.
The five-tab tracker architecture
Spreadsheets get messy when everything lands in one giant table. Shared purchases collide with settle-up payments, formulas break, and nobody knows who owes what. Giving each task a dedicated tab keeps the math clean.
Name your five tabs People, Expenses, Shares, Payments, and Balances.
| Tab | Job |
|---|---|
| People | Lists active group members and default percentages |
| Expenses | Logs invoices and bills with unique Expense IDs |
| Shares | Allocates exact portions of each bill to members |
| Payments | Records money sent between members to settle debts |
| Balances | Summarizes net positions (who owes what to whom) |
Keep every expense ID unique. Your lookup formulas rely on those codes to connect people to receipts.
1. People tab
Add each person once. This tab acts as your master roster and feeds the dropdown menus on other sheets.
| Column | Header | What goes here | Example |
|---|---|---|---|
| A | Name | Participant name | Avery |
| B | Default share | Baseline percentage for shared bills | 33.33% |
| C | Notes | Room assignment or dietary notes | Master bedroom |
Spell names identically across tabs. Write "Alex" on one tab and "Alexander" on another, and the formulas treat them as two distinct roommates. That small typo creates a phantom balance that never settles.
2. Expenses tab
Enter each vendor bill here once. Do this even if four people split the cost.
| Column | Header | What goes here | Example / Formula |
|---|---|---|---|
| A | Expense ID | Unique transaction code | EXP-001 |
| B | Date | Transaction date | 2026-06-18 |
| C | Description | Plain description of the purchase | Household groceries |
| D | Category | Budget bucket | Groceries |
| E | Total | Full amount charged | $86.40 |
| F | Paid by | Person who paid the vendor | Avery |
| G | Split rule | Summary of the split method | Equal among 3 |
| H | Receipt link | Link to cloud drive file | Drive link |
| I | Split total | Audit formula checking assigned shares | =SUMIF(Shares!A:A,A2,Shares!C:C) |
| J | Notes | Specific context | Casey was away |
Format column E as currency. Set column I to percentage formatting. A finished split must show 100.00% in column I.
3. Shares tab
This tab breaks bills down person by person. When three people split an eighty-dollar grocery run, that purchase gets three distinct rows under the same expense ID.
| Column | Header | What goes here | Example / Formula |
|---|---|---|---|
| A | Expense ID | Matching code from Expenses | EXP-001 |
| B | Person | Participant owing a share | Avery |
| C | Share percent | Their fractional responsibility | 33.33% |
| D | Amount owed | Calculated cost assigned to them | =IF(A2="","",ROUND(VLOOKUP(A2,Expenses!A:E,5,FALSE)*C2,2)) |
Penny rounding requires an intentional rule. A $100 bill split three ways divides into 33.33%, 33.33%, and 33.34%. Let the third person take the extra cent so the split reaches 100% instead of stranding money in formula limbo.
4. Payments tab
Log transfers here only after funds clear through a bank transfer, cash, or a payment app.
| Column | Header | What goes here | Example |
|---|---|---|---|
| A | Date | Date funds transferred | 2026-06-20 |
| B | From | Person sending money | Blake |
| C | To | Person receiving money | Avery |
| D | Amount | Total dollar amount | $28.80 |
| E | Reference | Transfer confirmation or note | Bank transfer #104 |
| F | Notes | Explanation | June grocery settle |
Never log a peer-to-peer reimbursement as a negative expense on the Expenses tab. That warps your category spending.
5. Balances tab
This sheet pulls numbers from the other four tabs. It shows where everyone stands right now. Put member names in column A, then drag these formulas down.
| Column | Header | Formula or logic |
|---|---|---|
| A | Person | Enter name from People tab (=People!A2) |
| B | Expenses paid | =SUMIF(Expenses!F:F,A2,Expenses!E:E) |
| C | Shares owed | =SUMIF(Shares!B:B,A2,Shares!D:D) |
| D | Net before payments | =ROUND(B2-C2,2) |
| E | Payments sent | =SUMIF(Payments!B:B,A2,Payments!D:D) |
| F | Payments received | =SUMIF(Payments!C:C,A2,Payments!D:D) |
| G | Current net | =ROUND(D2+E2-F2,2) |
| H | Status | =IF(G2=0,"Settled",IF(G2>0,"Receive","Pay")) |
Read column G directly. A positive balance means the group owes that person cash. A negative balance means that person needs to send money.
Choosing a split rule before buying
Thing is, no spreadsheet formula rescues an agreement people never made. You have to settle the split logic before swiping the card, not after an invoice arrives.
Equal splits work well for shared staples like paper towels, cooking oil, trash bags, or standard household groceries.
Room-size splits fit shared leases. If one housemate takes the master bedroom with a private bath and walk-in closet, divide rent by square footage or agree on an upfront split like 40/30/30.
Usage-based splits solve irregular costs. Electric bills with air conditioning overages, road-trip fuel when one traveler skipped half the drive, or subscription services only two roommates stream belong in this category.
Nights-stayed splits make group trips fair. Divide total lodging cost by total occupied room-nights, then multiply by the exact nights each guest slept there. A guest staying two nights should not pay an equal share of a seven-day cabin rental.
Personal purchases stay off the tracker completely. Individual takeout orders, private snacks, and pharmacy items get paid directly by the buyer.
Step-by-step workflow for logging shared bills
Consistency protects your records. Log receipts while the purchase is fresh rather than letting paper stack up for a month.
- Generate a new identifier on the Expenses tab, like
EXP-015. - Input the date, total amount paid, payer name, and expense category.
- Note the split rule in column G so everyone understands the rationale later.
- Switch to the Shares tab and insert a row for each participant splitting the purchase.
- Add each member's percentage share and check the calculated dollar amounts.
- Return to the Expenses tab and verify that column I reads
100.00%. - Paste a link to the receipt stored in your shared drive folder.
Remember that payers still take an assigned share. If Avery fronts $86.40 for groceries shared equally with Blake and Casey, Avery must appear on the Shares tab for $28.80. Avery paid the vendor, but Avery still consumed a third of the groceries.
Upfront deposits paid by two people need careful entry. Make two separate expense rows with consecutive IDs, log each person's exact outlay on their own row, and link the same receipt to both lines. Never write "Both" or "Split" in the Paid by column.
Why reimbursements stay separate from expenses
An expense represents money leaving your group to pay an outside merchant. A reimbursement represents money moving between group members to square up personal IOUs. Mixing these two transactions corrupts your accounting.
To be honest, watching someone record a $20 payback as a negative grocery line makes me wince. That single entry converts an actual $60 grocery run into an artificial $40 category total on your monthly summary, even though the house ate $60 worth of food. Your category reports become unreliable immediately.
Here is what the Balances tab looks like after Avery covers a $240 group booking:
| Person | Expenses paid | Shares owed | Net before payments |
|---|---|---|---|
| Avery | $240.00 | $80.00 | +$160.00 |
| Blake | $0.00 | $80.00 | -$80.00 |
| Casey | $0.00 | $80.00 | -$80.00 |
Blake sends $80 to Avery and you record that transaction on the Payments tab. Blake's Payments sent column updates to $80, which shifts Blake's Current net to $0.00. Avery's balance adjusts from +$160.00 down to +$80.00. The sheet balances cleanly, and your original $240 expense remains accurate.
Permissions, validation, and receipts
Shared files get corrupted when someone accidentally overwrites a working formula. Basic Google Sheets controls prevent those mishaps without locking collaborators out.
Set up dropdown validation first. Select the Paid by column on Expenses and the Person column on Shares, open Data > Data validation, and point the range to People!$A$2:$A$20. Restricting entries to the People tab stops spelling mistakes from creating phantom debts.
Protect your formula columns next. Right-click column D on Shares along with columns B through H on Balances, choose Protect range, and restrict edit access to yourself. Your housemates can still log new rows and enter receipts without breaking summary calculations.
Save receipt images in a shared cloud folder using a predictable pattern:
YYYY-MM-DD_Vendor_Amount
A receipt stored as 2026-06-18_Costco_86-40.pdf takes seconds to locate if someone questions an entry months later.
Keep legal realities in mind. A spreadsheet tracker does not replace formal legal agreements. Security deposits, official lease contracts, and tenancy regulations depend on signed documents and local property laws rather than spreadsheet cells.
Common mistakes that break the sheet
Watch for these recurring errors:
- Leaving trailing pennies unallocated. Splits adding up to 99.99% prevent the Balances tab from reaching zero. Give the extra cent to the payer or alternate who covers it.
- Skipping the 100% check. If column I on Expenses reads 66.67%, a roommate was left off the Shares tab.
- Overwriting adjusted rows. When a vendor issues a partial refund, keep the original line intact. Add a separate negative adjustment row and explain it in the notes.
- Inconsistent names. Nicknames or accidental spaces after a name break lookup formulas.
- Delayed entries. Entering bills three weeks late turns simple reimbursement into an argument about old meals.
Turns out a ten-minute check on Sunday evening stops long, uncomfortable money talks at month-end.
Closing out monthly expenses or group trips
Choose a recurring settlement date each month. For vacations, close the books within forty-eight hours of returning home.
Start by filtering the Expenses tab by date range. Check column I to make sure every entry reads 100.00%. Then open the Balances tab. Anyone showing a negative Current net balance sends funds directly to someone showing a positive balance.
Send a clear settlement request:
I logged EXP-042 for our internet bill ($90.00 total, split three ways). The sheet shows your balance is -$30.00 owed to Avery. Please send payment and check the sheet before Friday.
Record every settlement on the Payments tab as soon as money transfers. When all Current net cells show $0.00, your month is settled. File an archive copy of the sheet in Google Drive for future lease or tax reference.
Open a fresh Google Sheet today. Add the five tabs, plug in the lookup formulas, and run two test rows with your roommates before real expenses start piling up. Building the framework now saves hours of frustration later.