Use a shared Google Sheet with one row per purchase and one column for each attendee's share weight. Add a separate payments tab for reimbursements. That setup handles flights to PHX, an Airbnb deposit, rental-car gas, meals, bar tabs, and golf without confusing a real expense with a later payback.
A spreadsheet tracks the agreement and the math. It does not send money or confirm that a transfer arrived, so keep those records separate.
Build the workbook around three tabs
Turns out, the cleanest setup is not very complicated. Create a blank file in Google Sheets with these tabs:
| Tab | What belongs there |
|---|---|
Expenses |
Shared charges, deposits, refunds, and their split rules |
Payments |
Transfers from one attendee to another |
Summary |
What each person paid, owed, sent, received, and still needs to settle |
Keep the original charge in Expenses. Keep later repayments in Payments.
Set up the Expenses tab
Use one row for each purchase or refund. Do not combine an airport ride, dinner, and bar stop into one vague line.
| Column or range | What to enter |
|---|---|
| A - Date | The purchase date |
| B - Description | A useful label, such as Uber from PHX airport |
| C - Category | Travel, lodging, food, drinks, activities, or other |
| D - Amount | The full receipt total in dollars |
| E - Paid by | The attendee who covered the charge |
| F - Split method | Equal, Usage-based, or Custom |
| G - Receipt link | A link to the receipt photo or file |
| H:Q | One share-weight column per attendee |
| R | The row's total share weight |
| S:AB | Formula-calculated dollar shares for each attendee |
The H:Q and S:AB ranges illustrate a ten-person group. Add or remove columns in both attendee blocks together. Put the same names, in the same order, in both blocks.
Use 1 and 0 for an ordinary equal split. A 1 includes someone, while a 0 excludes them. For a custom split, use proportional weights instead. For a $90 dinner split as $40, $25, and $25, enter weights of 40, 25, and 25; the formula will produce those dollar shares.
Enter 1200 in the Amount cell, then format the column as currency. Keep dollar signs out of the raw entry if the sheet treats them as text.
Add formulas that show each person's share
Start with row 2, then fill the formulas down. The formulas below use the example ranges above.
| Location | Formula | Purpose |
|---|---|---|
R2 |
=SUM(H2:Q2) |
Adds the row's share weights |
S2 |
=IFERROR($D2*H2/$R2,0) |
Calculates the first attendee's share |
B2 on Summary |
=SUMIF(Expenses!$E$2:$E$200,B$1,Expenses!$D$2:$D$200) |
Totals what the person in B1 paid |
B3 on Summary |
=SUM(Expenses!S$2:S$200) |
Totals what that person owes |
B4 on Summary |
=SUMIF(Payments!$B$2:$B$200,B$1,Payments!$D$2:$D$200) |
Totals payments sent |
B5 on Summary |
=SUMIF(Payments!$C$2:$C$200,B$1,Payments!$D$2:$D$200) |
Totals payments received |
B6 on Summary |
=B2-B3+B4-B5 |
Calculates the current balance |
Copy S2 across the calculated-share block. The reference to H2 will move to I2, J2, and so on. Copy the summary formulas across as well, keeping the attendee names in row 1 aligned with the calculated-share columns.
On the Summary tab, use these row labels in column A:
| Cell | Label |
|---|---|
A2 |
Total paid |
A3 |
Total owed |
A4 |
Payments sent |
A5 |
Payments received |
A6 |
Balance |
A positive balance means the group owes that person. A negative balance means that person still owes the group. A zero means the ledger is even.
If you want a category view, place this in a separate tab: =QUERY(Expenses!A1:D200,"select C, sum(D) where C is not null group by C label sum(D) 'Total'",1). For a review list, use =FILTER(Expenses!A2:G200,Expenses!D2:D200>100) and change 100 to your preferred review threshold. Google's Sheets function list can help if you adapt these formulas.
Handle uneven Phoenix party costs
The formula supports more than equal splitting. It divides the amount according to the weights you enter.
| Expense | Example weights | Reason |
|---|---|---|
| Flights shared by everyone | 1 for every traveler |
Equal participation |
| Bar tab for drinkers only | 1 for drinkers and 0 for everyone else |
Usage-based split |
| Golf outing | 1 for golfers and 0 for non-golfers |
Only participants share it |
| Custom dinner | 40, 25, and 25 on a $90 charge |
Different agreed amounts |
| Airbnb by nights | One weight per person's agreed nights | A nights-based rule, if the group accepts it |
Agree on the rule before someone pays. That prevents a disagreement from looking like a formula problem.
For a bill with separate food and drinks, use separate rows if the receipt provides those amounts. If a deposit is later refunded, add a negative expense row with the same participants and the person who received the refund as the payer.
Keep reimbursements on their own tab
Thing is, a repayment is not another group expense. Adding Jordan's $40 repayment to the dinner total makes the weekend look more expensive and can distort everyone's share.
Set up Payments with these columns:
| Column | What to enter |
|---|---|
| A - Date | The date the transfer was sent |
| B - From | The person who sent money |
| C - To | The person who received money |
| D - Amount | The transfer amount |
| E - Status | Pending or Confirmed |
| F - Note or receipt | Payment reference or supporting detail |
Suppose Mike paid for a shared dinner and Chris later sends Mike $25. The dinner stays in Expenses. The $25 goes in Payments, with Chris in From and Mike in To.
Record a transfer after the sender says it was sent. Mark it confirmed after the recipient checks that it arrived. Do not delete the original expense after settlement; the payment row is what shows how the balance changed.
Share the sheet without inviting accidental edits
Share the file with specific people when possible. Give edit access to the people entering charges, and use viewer access for attendees who only need to check totals.
| Access or control | Practical use |
|---|---|
| Editors | Add purchases, receipts, and payment records |
| Viewers | Check the Summary tab without changing data |
| Protected ranges | Lock formulas in R, S:AB, and the Summary tab |
In Google Sheets, use Data > Protect sheets and ranges for formula areas. Protection is a guardrail against edits, not a privacy setting, so choose sharing access separately.
Real-time editing is useful during the trip. Keep the formula ranges protected, and avoid giving edit access to everyone just because they want to see the balance.
Use this workflow during the weekend
- Before booking anything, add every attendee's name to the header rows. Decide whether flights, lodging, rides, meals, drinks, and activities are shared equally or by usage.
- After each purchase, add the date, description, category, amount, payer, weights, and receipt link. The receipt may arrive later. Add it later.
- Split mixed charges into separate rows when the receipt supports it. For a shared steak dinner and a drinks-only charge, do not force everyone into the same participant row.
- Review the Summary tab each night during a three-day weekend. Look for missing payers, all-zero weights, and charges that need a group decision.
- Add actual repayments to
Payments, then update their status. Leave the original expense in place. - After the trip, confirm the final balances, save an offline copy, and keep the receipt links with the ledger.
The receipt may arrive later, the tip may be added later, and someone may remember a second stop at the bar after the row was entered. Add it as a separate row. That small habit keeps the history understandable.
Fix common spreadsheet mistakes
| Problem | Repair |
|---|---|
| A participant cell is blank | Use 0 when that person did not share the cost; leave blanks for unfinished rows only |
| The calculated share shows zero | Check that the row has a positive amount and at least one weight |
| A person is missing from the Summary | Match the name and column order in the attendee headers |
| The total is too high | Check whether a repayment was entered in Expenses instead of Payments |
| A formula was overwritten | Restore it and protect the formula range |
| A receipt is disputed | Add a direct receipt link and a short note about the split rule |
To be honest, the final check matters more than a clever layout. Before sharing the link, enter a test flight, a drink-only tab, and a custom $90 dinner. Confirm that the Summary shows the expected paid, owed, and balance figures, then clear the test rows and begin logging the actual trip.