Create three linked tables in Airtable - People, Expenses, and Balances - using linked record fields and rollups with SUM(values) for totals, AVERAGE(values) for per-person shares, and ARRAYJOIN(values, ", ") for expense lists to track roommate contributions and owed amounts.

This setup helps U.S. roommates splitting rent, utilities, groceries, and reimbursements who need a free-form tracker beyond basic spreadsheets. Add a People table for roommate names and balances. Log expenses with who paid and who splits them. Use rollups in People to sum what each paid and their share of total expenses. Subtract to get balances. Review weekly to settle IOUs.

When to Use Airtable for Roommate Expense Tracking

A simple spreadsheet works for equal monthly splits or one-off trips with few expenses. Use Google Sheets for basic rent and utilities where everyone pays the same amount each month.

Airtable fits recurring expenses with multiple payers and custom splits, such as room-size based or usage-based for groceries and chores. Linked tables and rollups provide summaries without manual formulas across sheets. It handles growing lists of expenses over months or years, showing who owes what at a glance.

Consider Airtable when your group has uneven contributions, like one roommate covering more utilities. For two roommates with identical bills, a shared sheet with columns for date, amount, and paid-by is enough.

Base Structure - Three Essential Tables

Start with these core tables. Each has recommended fields for roommate tracking.

People table lists roommates. Columns:

  • Name (single line text)
  • Expenses Paid (rollup: SUM(values) from Expenses "Paid By" link)
  • Share of Expenses (rollup: AVERAGE(values) from linked "Split With" fields across expenses)
  • Balance (formula: {Expenses Paid} - {Share of Expenses})

Expenses table logs each payment. Columns:

  • Date (date)
  • Description (single line text, e.g., "February rent")
  • Amount (currency)
  • Category (single select: Rent, Utilities, Groceries, Other)
  • Paid By (link to People table, single)
  • Split With (link to People table, allow multiple)

Balances table details individual shares per expense (optional for detailed views). Columns:

  • Person (link to People)
  • Expense (link to Expenses)
  • Share Amount (formula or rollup: {Amount} / COUNT(values) from "Split With" for equal splits)

This structure uses linked records so changes in one table update summaries automatically.

Set Up Linked Records Between Tables

Linked records connect tables so rollups pull data across them.

  1. In the Expenses table, add "Paid By" as a linked record field. Select the People table. Set to single link.

  2. Add "Split With" as another linked record field to People. Enable "Allow linking to multiple records."

  3. In the Balances table, add "Person" linking to People (single) and "Expense" linking to Expenses (single).

  4. When creating rollups later, select these linked fields as the source. For example, in People, link back to Expenses via "Paid By" to sum amounts.

Test links by adding a sample expense: Paid By to one roommate, Split With to all. The links appear in both tables.

Add Rollup Fields for Totals and Balances

Rollups aggregate data from linked records. Use these formulas from Airtable's rollup field overview.

In People table:

  • Expenses Paid: Rollup on "Paid By" link from Expenses. Field: Amount. Formula: SUM(values). Shows total each person paid.

  • Expenses List: Rollup on "Paid By." Field: Description. Formula: ARRAYJOIN(values, ", "). Lists recent expenses as text.

  • Share of Expenses: For equal splits, rollup on all Expenses linked via "Split With" (may need a lookup or formula adjustment). Use AVERAGE(values) on share amounts.

For balance: Formula field with {Expenses Paid} - {Share of Expenses}. Positive means they overpaid; negative means they owe.

Advanced options:

  • Recent expenses: ARRAYSLICE(values, 1, 2) for first two.
  • Last two: ARRAYSLICE(values, -2).
  • Filter: IF(values > 0, SUM(values), 0) to exclude zeros.

In Balances: Rollup share as {LINKTOEXPENSE.Amount} / (1 + LEN({Split With}) - 1) for per-person equal split.

Update expenses weekly to keep rollups current.

Sharing and Permissions for Roommates

Invite roommates via email from the base's Share button. Choose Editor for those adding expenses, Viewer for read-only balance checks.

Editors can add records and comment on disputes, like "Confirm grocery receipt." Viewers see summaries without editing.

Set a group update cadence: One person logs expenses Sundays; all review balances Fridays. Use Airtable comments for notes, not financial data storage.

For privacy, share views filtered by person. No sensitive account details needed.

Common Mistakes and Fixes

Forgetting to select the linked field when creating rollups: Rollups show blank. Fix: Edit field, choose "Paid By" or "Split With" as the linked source.

Unfiltered rollups including zero-amount expenses: Totals skew. Fix: Add filter like IF(values > 0, values) in rollup formula.

Not adjusting for uneven splits: Equal AVERAGE fails for room-size shares. Fix: Use formulas in Balances table, like Amount times room percentage, then rollup those.

Overcomplicating without receipts: Trackers drift without proof. Fix: Add a URL field for photo links or shared Google Drive folder.

Missing weekly reviews: Balances grow unnoticed. Fix: Calendar reminders for group checks.

FAQ

How do I calculate uneven splits like room-size based?
In Expenses, add a "Split Percent" formula per person or use Balances table with custom formulas (e.g., Amount * 0.4 for larger room). Rollup those adjusted shares.

Can I use rollups for categories like utilities only?
Yes, add a filter in rollup: IF({Category} = "Utilities", values, 0), then SUM.

What's the difference between SUM(values) and AVERAGE(values)?
SUM totals all linked amounts (e.g., total paid). AVERAGE divides by count (e.g., per-person share).

How often should we review the tracker?
Weekly for active households; monthly for stable rent splits. Settle balances before new expenses.

Is Airtable enough, or do I need a payments app?
Airtable tracks and summarizes; pair with cash apps for reimbursements. Use for records, not payments.

How do I export for records or taxes?
Use CSV export from table views. Filter by date or person. Keep receipts separate; consult IRS guidance for shared expense records.

Next, create a test base with sample data: Add two roommates, three expenses (rent $2000 split even, utilities $150 uneven). Verify rollups match. Scale as needed.