A Google Sheets tracker can split San Francisco vacation rental costs without another bill-splitting app. Give each charge its own row, mark participating people with 1 or 0, and let formulas calculate each person's share.
It works for deposits, nightly rental charges, cleaning fees, groceries, and similar trip costs. The sheet records who paid and who owes; it doesn't send money or decide a disputed split.
Build the sheet around one charge per row
Start with one tab named Expenses. Use one row for each charge, and put participant names in their own columns.
The formulas below assume columns G through J contain Alice, Bob, Carol, and Dana. Column K counts participants, while column L calculates the share for one participant.
| Column | Purpose | Example |
|---|---|---|
| A: Date | Date of the charge or payment | 2026-03-15 |
| B: Description | Clear name for the expense | Rental deposit |
| C: Category | Type of cost | Rental, Deposit, Cleaning |
| D: Amount | Full charge amount | $1,000 |
| E: Split type | Rule used for the split | Equal, nights stayed, usage |
| F: Paid by | Person who paid the charge | Alice |
| G:J: Participants | One column per person; enter 1 or 0 | 1 |
| K: Participant count | Number of people flagged for the row | Formula |
| L: Per-person share | Amount divided by the participant count | Formula |
| M: Notes | Receipt link or explanation | Receipt in shared folder |
Format columns D and L as currency. Format column A as a date. If a deposit is refundable, label it Refundable deposit instead of treating it like a permanent rental expense.
The layout assumes one payer per expense row. If two people paid the same charge, keep the expense listed once and use a separate payment log for the contributions.
Choose the split rule before entering flags
The formula only sees the flags. Your actual rule belongs in Split type and Notes.
For an equal charge, mark every person who shares it with 1. Use this for a shared booking deposit, common cleaning charge, or group grocery run.
Nights stayed need smaller rows. If everyone stayed for the full booking, one combined row can work. If someone left early, use one row per night or per period where the participants changed.
Usage-based charges should include only the people who used the item. A private add-on should not quietly become a group charge.
Thing is, a four-night row with four 1 flags creates four equal shares. It does not know that one person stayed for only one night.
A 1/0 setup also doesn't calculate income-based or percentage splits. If your group agrees to a custom allocation, record those percentages in a separate allocation table rather than hiding a different rule inside the participant flags.
Add the calculation formulas
Enter the expense rows first, then add the formulas to the calculation columns.
- In
K2, enter=SUM(G2:J2). This counts the people flagged for that expense. - In
L2, enter=IFERROR(D2/SUM(G2:J2),""). This divides the full charge by the number of flagged participants. - Copy both formulas down through your planned expense range, such as row 25.
- Add data validation to G2:J25 if you want to limit entries to 0 and 1.
- Treat a blank share as something to review. It usually means the amount has no participant flags.
Use 0 for someone who did not share a charge. A blank cell also behaves like zero in the sum, but explicit zeros are easier to audit.
Here is a small example:
| Date | Description | Amount | Split type | Paid by | Alice | Bob | Carol | Dana | Per-person share |
|---|---|---|---|---|---|---|---|---|---|
| 2026-03-15 | Rental deposit | $1,000 | equal | Alice | 1 | 1 | 1 | 1 | $250 |
| 2026-03-15 | Cleaning charge | $180 | equal | Bob | 1 | 1 | 1 | 1 | $45 |
| 2026-03-16 | Night 1 | $240 | nights stayed | Alice | 1 | 1 | 1 | 0 | $80 |
| 2026-03-17 | Night 2 | $240 | nights stayed | Alice | 1 | 1 | 0 | 0 | $120 |
Dana is not included in the first night. Carol is not included in the second.
Create a summary for who paid and who owes
Add a Summary tab with four columns:
| Name | Paid | Owes | Net |
|---|---|---|---|
| Alice | |||
| Bob | |||
| Carol | |||
| Dana |
If Alice is in A31, enter this in B31 to total the charges she paid:
=SUMIF($F$2:$F$25,A31,$D$2:$D$25)
For Alice's share of the expenses, enter this in C31:
=SUMIF($G$2:$G$25,1,$L$2:$L$25)
For Bob, use the H column instead:
=SUMIF($H$2:$H$25,1,$L$2:$L$25)
Use the I and J columns for Carol and Dana. In D31, enter =B31-C31, then copy the formula down.
A positive net means the group owes that person. A negative net means that person owes the group. The sheet gives you the balance; your group still needs to choose how to settle it.
The participant criteria must be numeric. A formula that searches for Alice's name in a 1/0 flag column won't calculate her share. The criteria is 1, and the sum range is the per-person share column.
Add a simple check below the summary:
=SUM($B$31:$B$34)-SUM($C$31:$C$34)
The result should be zero when every expense has a payer and complete participant flags. If it is not zero, look for a missing flag, a misspelled payer name, or a row with no participants.
Add category and high-cost views
Keep Category and Split type separate. Category describes what the charge was; split type describes how the group divided it.
On the Summary tab, a rental-only total can use:
=SUMIF($C$2:$C$25,"Rental",$D$2:$D$25)
To review large charges, use this on a separate area or tab:
=FILTER(A2:M25,D2:D25>500)
Change 500 to the amount that deserves a second look for your trip. This view can surface a large deposit, an added night, or an unexpected charge without changing the main tracker.
For a refundable security deposit, keep the amount in its own category. Record the return separately and note who received it. That prevents a returned deposit from being mistaken for a normal trip expense.
Share the file without exposing the formulas
Invite specific group members by email when possible. An Editor can add charges and change flags, a Commenter can question an entry without changing the data, and a Viewer can review the final totals.
Avoid open edit links when the sheet contains names, receipts, or payment details. Keep one person responsible for access settings and the final summary.
Protect the formula and summary ranges through Data > Protect sheets and ranges. Leave the input columns editable, especially Date, Description, Amount, Split type, Paid by, participant flags, and Notes. When you add rows, copy the formulas into the new calculation cells.
Paste receipt links into Notes and check that the group can open them. A link that only the payer can see won't help much during a review.
Use a simple review routine
To be honest, the sheet only stays accurate when someone owns the boring updates.
- The payer adds each charge when it appears.
- A reviewer checks the amount, category, receipt, and participant flags once a week.
- Group members comment on unclear charges instead of overwriting the original entry.
- Before settlement, the group reviews the Net column and the zero-balance check.
Use the same spelling for each person's name. Don't alternate between Alex, Alexander, and an email address in the Paid by column.
Turns out, most disagreements come from the rule rather than the formula. Write a short note such as "cleaning split equally" or "night 2 excludes Dana" while the trip details are still fresh.
Handle cancellations and changing participation
Add a cancellation fee as its own row. Mark the people responsible under the rule your group agreed to, and explain the decision in Notes.
If someone joins late or leaves early, split the affected rental charges into separate rows. Don't silently rewrite an old row after people have reviewed it; add an adjustment or a note so the change has a visible history.
For a partial refund, record the adjustment separately. A negative amount can work if the same participants receive the credit, but a separate refund record is clearer when only one person receives the money.
When a spreadsheet is enough
A spreadsheet fits a small group that wants a shared record, clear receipts, and manual settlement. It separates tracking from payment, which can be useful when the group already has its own way to send money.
A payment app may help with requests or transfers, but it won't replace a well-documented expense list. Copy the final balances into the group's chosen payment method and keep the sheet as the trip record.
Common questions
Can I use this for groceries, gas, or meals?
Yes. Add each item as a new row, choose a category, and use the same participant flags. The rental-specific part is the category and split rule, not the formula.
What happens if someone forgets to update a flag?
The affected person's total will be wrong. Review the participant columns together, correct the row, and rerun the summary check before anyone settles.
Can 1/0 flags handle a 60/40 split?
Not directly. Use agreed percentage allocations in a separate table or create allocation rows that show each person's amount. A binary flag only says whether someone is included.
Is this tax-ready?
It provides basic trip records with dates, amounts, categories, and notes. It isn't tax advice, and tax treatment depends on the purpose of the expense and the relevant U.S. rules. Keep receipts separately.
Create the Expenses tab, enter the participant names, and test one equal split before sharing the file with the group.