Set up a shared expense tracker in Google Sheets by adding columns for Date, Description, Amount, Paid By, Paid Status, Receipt Status, and Notes. Use conditional formatting like =$F2="Receipt Missing" (from mintline.ai) to highlight unpaid or incomplete items.
This template helps U.S. roommates track utilities and rent splits, travel groups monitor trip costs like gas and meals, or families record uneven contributions without paid apps. Add a dropdown for Paid Status (Unpaid, Paid, Partial) and running tallies for IOUs, as in Smartsheet's expense report guidance. Share with edit access for active members and view-only for others.
Recommended Columns for Paid Status Tracking
Essential columns capture shared expenses clearly. Start with Date for when the cost happened, Description for details like "July utilities" or "road trip gas," and Amount for the total.
Add Paid By to note who covered it upfront, such as one roommate for groceries. Paid Status uses a dropdown: Unpaid, Paid, Partial. This flags IOUs quickly in reimbursement workflows.
Include Receipt Status (Receipt OK, Receipt Missing, Photo Attached) and Notes for comments like "Venmo pending." For splits, add columns for Share % or Individual Owe, with a Running Tally column to show reimbursement amounts due, per Smartsheet guidance.
For roommate utilities, equal split might mean each owes Amount divided by group size. Usage-based splits work for uneven shares, like larger rooms paying more on rent. Track these qualitatively in formulas to avoid disputes.
| Column | Purpose | Example |
|---|---|---|
| Date | Expense date | 2026-07-15 |
| Description | Item details | Electricity bill |
| Amount | Total cost | $120 |
| Paid By | Who paid upfront | Alex |
| Paid Status | Reimbursement stage | Partial |
| Receipt Status | Proof status | Receipt OK |
| Share % | Split per person | 25% |
| Running Tally | Owed amount | $30 |
| Notes | Updates | Venmo sent 7/20 |
Setup Steps for Your Shared Expense Tracker
Create a new Google Sheet named "Group Expenses 2026." Freeze the first row: View > Freeze > 1 row.
In Paid Status (column E), add data validation: Select E2:E100, Data > Data validation > Criteria: List of items > Unpaid,Paid,Partial. Repeat for Receipt Status (F): Receipt OK,Receipt Missing,Photo Attached.
For summaries, use =FILTER(A2:D100, B2:B100>100) to show large expenses over $100, as in Relayfi's tracker template. Place this in a "Large Expenses" tab.
Add =QUERY(A2:D100, "SELECT C, SUM(D) GROUP BY C LABEL SUM(D) 'Total'") for category totals like travel or utilities. In a dashboard sheet, sum unpaid: =SUMIFS(Amount_range, Paid_Status_range, "Unpaid").
Set up Running Tally in column I: =I1 - (Amount * Share%) for each person's balance, adjusting for who paid.
Highlighting Paid Status with Conditional Formatting
Visual flags make unpaid items stand out. Select rows A2:H100, Format > Conditional formatting.
For unpaid: Custom formula = $E2="Unpaid" > red fill. For partial: =$E2="Partial" > yellow.
Flag receipt gaps with =$F2="Receipt Missing" (from mintline.ai) > orange.
For budget warnings, =AND(C2>=B2*0.8, C2<=B2) highlights when spending hits 80-100% of a planned amount (from Relayfi) > yellow.
Apply one rule at a time, ordering from specific to general. Test by changing a status.
Updating and Reviewing Paid Status Workflow
Update weekly: One person logs new expenses with receipts. Group reviews via shared link, marking Paid Status after proof like Venmo screenshots in Notes.
Workflow: Log expense > Assign Paid By and Share % > Initial status Unpaid > Payer shares proof > Updater changes to Paid > Zero Running Tally.
Common mistakes: Forgetting to update tallies after payments, leading to double-counts. Over-editing by all members causes version conflicts. Not archiving paid rows, cluttering the sheet.
Script reminders: "Check Paid Status: Unpaid items over 30 days get a note." For roommates, review monthly before rent. Spreadsheets suit simple groups; add apps only for scanning if needed.
Sharing and Permissions for Group Access
Share via button: Click Share > Add emails or copy link. For roommates handling rent splits, give edit access to active members. Advisors or infrequent users get view-only, per ExpenseSorted guidance.
Use "Anyone with link" set to Editor for trusted groups, Viewer otherwise. Avoid full account access or password shares. Notify via group chat: "Sheet updated - review unpaid."
Restrict to link-only, no public. Revoke access when someone leaves the group.
Limitations of Google Sheets for Paid Status Tracking
Google Sheets tracks status well but lacks built-in payments or auto-verification. Formulas like SUMIFS for unpaid totals (=SUMIFS(B2:B100, E2:E100, "Unpaid")) work editorially but test for your version.
No reminders or receipt scanning; manual updates needed. For large groups, edit conflicts arise. Spreadsheets suffice for informal U.S. roommate or travel tracking without fees.
Consider apps for automation if scanning dominates, but Sheets often enough for IOUs under $500 total.
FAQ
How do I create a paid status dropdown in Google Sheets?
Select the column, Data > Data validation > List of items: Unpaid,Paid,Partial. Apply to E2:E100.
What formula flags unpaid shared expenses?
=FILTER(A2:H100, E2:E100="Unpaid") shows only unpaid rows (Relayfi example).
Can I sum only unpaid amounts by category?
=SUMIFS(B2:B100, D2:D100, "Travel", E2:E100, "Unpaid") totals unpaid travel (Relayfi).
Is conditional formatting safe for group sheets?
Yes, it updates live for all viewers; rules apply sheet-wide without conflicts.
When should I switch from Sheets to an app for tracking?
If receipt scanning or reminders exceed manual effort, or for 10+ members.
How often to review paid status in a roommate group?
Weekly for bills, monthly for tallies; set calendar reminders.
Next, build your sheet with these columns and test a sample expense like a $200 grocery run. Adjust splits for your group's rules, and archive yearly for records.