Use a custom Google Sheets template with columns for date, description, payer, amount, category, split type, and balances, plus formulas for totals and summaries. Share via link for real-time group edits. This free setup works for U.S. roommates tracking rent and utilities, families splitting groceries, trip groups handling travel costs, or clubs managing events. No paid apps or accounts needed for basic tracking and reimbursement records.
Start with a new blank sheet at sheets.google.com. Groups can log expenses, calculate who owes what, and settle balances monthly. Relay Financial and Expense Sorted outline practical columns and formulas for shared use.
Core Columns for a Shared Expense Tracker Template
Recommended columns capture key details for shared expenses. Start with these in row 1: Date (e.g., 2026-01-15), Description (e.g., "February rent"), Payer (e.g., "Alex"), Amount (e.g., 1200), Category (e.g., "Rent"), Split Type (e.g., "Equal"), Shares per Person (e.g., 4), and Balance (formula-driven).
For split types, use "Equal" for even divisions like group dinners, "Usage-based" for uneven utility splits (e.g., higher for bigger rooms), or "Income-based" for couples with different earnings. Tradeoffs include equal splits for simplicity but potential unfairness if incomes vary; usage-based needs clear rules upfront.
For roommate reimbursements, mark the entry as "Reimbursement" in Split Type, with the payer at 100% and others at 0%, per Expense Sorted. This flags one-off payments without altering group balances. Add a Notes column for receipts or rules, like "Alex paid deposit, reimburse after move-in."
Format columns: Currency for Amount, date picker for Date, dropdowns for Category (Rent, Utilities, Groceries, Travel) and Split Type. Freeze row 1 and add filters via Data > Create a filter for quick sorting.
Setup Steps to Build and Customize Your Template
-
Go to sheets.google.com and click Blank.
-
Enter column headers in row 1 as listed above.
-
Input sample data: Row 2 - 2026-01-15, "Rent", "Alex", 1200, "Rent", "Equal", 4, (leave for formula).
-
Add a second sheet named "Balances" for summaries. List group members in column A (e.g., Alex, Jordan), with running totals in B.
-
Format: Select Amount column, Format > Number > Currency. For Date, Format > Number > Date.
-
Test with group data: Add utilities (Jordan pays 150, "Usage", shares 1.2 for bigger room).
Customize for your group - add "Nights Stayed" for trips or "Itemized" for events. Use conditional formatting: Highlight over $100 expenses in yellow via Format > Conditional formatting, with custom formula =AND(C2>100) on Amount column.
Per Shared Contacts and GeeksforGeeks, real-time collaboration shows live changes as group members edit.
Useful Formulas for Totals, Filters, and Summaries
Formulas automate calculations. In Balances sheet, for Alex's total paid: =SUMIF('Sheet1'!C:C, "Alex", 'Sheet1'!D:D).
For category totals like travel: =SUMIFS(Sheet1!D:D, Sheet1!E:E, "Travel", Sheet1!F:F, "Yes"), adapted from Relay Financial blog for multi-condition sums.
Filter high expenses over $100: In a new sheet, =FILTER(Sheet1!A:D, Sheet1!D:D>100). This pulls rows dynamically.
For summaries: =QUERY(Sheet1!A:F, "SELECT E, SUM(D) GROUP BY E LABEL SUM(D) 'Total'") groups and sums by category.
Budget check: =AND(C2>=B2*0.8, C2<=B2) for conditional formatting on a Budget vs Actual row, highlighting 80-100% spend in yellow, per Relay Financial.
Test formulas in your sheet - ranges like A2:D100 adjust as data grows. For reimbursements, add =IF(F2="Reimbursement", D2, D2/Shares) in a Share column. These work for platform-specific shared tracking but verify outputs.
Relay Financial blog on Google Sheets expense formulas
Sharing, Permissions, and Collaboration Basics
Click Share button (top right). Under General access, change from Restricted to Anyone with the link via dropdown, per GeeksforGeeks. Set role to Editor for full edits or Commenter for notes only.
Copy link and send via text or group chat - no emails needed, as noted by Shared Contacts. Assign Editor to active members (e.g., roommates logging rent) and Commenter to approvers (e.g., trip planners), via Derrick App guidance on role-based access.
Use built-in chat (top right) for questions like "Confirm this utility split?" Changes appear live for real-time collaboration, per Expense Sorted.
Common setup: Editor for all in small groups; restrict to Commenter if disputes arise. Revoke access anytime via Manage access.
GeeksforGeeks on sharing Google Sheets
Common Mistakes and When to Stick with Sheets vs. Apps
Avoid unversioned edits - use File > Version history > See version history weekly. Don't ignore receipts; add hyperlinks in Notes (right-click cell > Insert link to Google Drive photo).
Poor split rules lead to disputes - document upfront (e.g., "Utilities: equal unless usage meter proves otherwise"). Update cadence: Log weekly, review monthly, settle quarterly.
Sheets suffice for informal U.S. groups with low volume (under 50 entries/month), like roommates or trips - free, no learning curve, exports to PDF for records. For high volume, receipt scanning, or automated payments, consider apps as supplements, but keep a sheet for independent records.
Pitfalls: Over-sharing (use link expiration), formula errors (copy-paste breaks ranges), no backups (enable via settings).
Expense Sorted on roommate Sheets templates
FAQ
How do I handle uneven splits like income-based or per-room in the template?
Add a "Share %" column next to Shares per Person. For income-based, calculate percentages (e.g., 60/40) manually or via formula =D2*(Income%/100). Multiply by Amount for individual owes.
What's the best way to add receipts or proof to Google Sheets?
Upload photos to Google Drive, insert hyperlinks in Notes column. Or embed images via Insert > Image > Image in cell for visuals.
Can multiple people edit at once without conflicts?
Yes, real-time edits show live; conflicts auto-resolve or prompt via Version history, per editorial notes on collaboration.
How often should our group review and settle balances?
Weekly logs, monthly reviews via QUERY summary, settle when balances exceed $50/person to avoid buildup.
Is this template good for trip reimbursements or just roommates?
Works for both - use Category "Travel", Split Type "Per Person" or "Nights Stayed"; mark reimbursements as 100/0% for post-trip settles.
What if someone leaves the group - how to update shares?
Archive old rows (cut to history sheet), adjust Shares per Person forward. Recalculate balances with new divisor.
Next, build your sheet with sample data, share the link, and log your first expense. Review formulas after a week of use.