Group Travel Expense Spreadsheet: Split Costs in Google Sheets
A shared hotel, one dinner somebody skipped, a taxi paid in cash: a group trip can produce a surprisingly awkward last evening. The total spend is easy to find. Working out who should reimburse whom takes a different kind of travel planner.
This group travel expense spreadsheet uses three Google Sheets tabs to separate purchases, each person's share and repayments. You can build it from the copyable blocks below. There is no paid download or add-on, and the formulas stay visible.
What this template does: tracks a three-person trip, supports unequal shares and excluded travelers, and updates balances after repayments. It uses one settlement currency. It does not send money, automatically convert exchange rates or find the mathematically smallest set of transfers.
Agree on the rules before the first booking
Choose a settlement currency and write down who is joining. Decide whether accommodation is split by person, room or nights stayed. Agree on how to handle meals when some people order much more, and whether booking fees belong to everyone.
These decisions matter more than a clever formula. A couple sharing one room and a solo traveler taking another may prefer a room-based split. A friend joining halfway through should not quietly inherit the entire week's costs.
Use the sheet for actual payments. Keep unbooked estimates in a separate travel planner and budget template. Otherwise, a forecast dinner can look like money somebody is already owed.
Step 1: Create the Expenses tab
Open a blank Google Sheet. Rename the first tab Expenses, then paste this tab-separated block into cell A1. The names and amounts are invented examples in one currency, not destination price estimates.
Item Paid by Amount Ana share Ben share Cara share Check Apartment Ana 300 100 100 100 =C2-SUM(D2:F2) Dinner for two Ben 90 45 45 0 =C3-SUM(D3:F3) Taxi Cara 60 20 20 20 =C4-SUM(D4:F4)
If everything lands in one column, use Sheets' Split text to columns command and choose the tab separator. Format columns C:G as numbers with two decimal places. Freeze the first row, and use exactly the same spelling for each payer throughout the workbook.
Columns D:F hold the amount each traveler should bear, including the payer's own share. A zero means that person did not participate. Column G must be zero for every completed row: it subtracts the allocated shares from the amount paid.
For equal shares on a new row, enter the amount divided by three in each share column. For a 100-unit purchase, avoid three rounded shares of 33.33 that leave a cent unassigned: use 33.33, 33.33 and 33.34. Agree on who takes the rounding cent rather than letting it accumulate invisibly.
Unequal shares need no special mode
Suppose a 120-unit dinner belongs 50 to Ana, 40 to Ben and 30 to Cara. Enter those amounts directly. If Cara skips it, her share is zero and the other shares must still add to 120. The sheet does not decide what is fair; it checks that your agreed allocation matches the payment.
Copy the Check formula down for every additional expense. The summary below covers rows 2 through 1000, so extend its ranges if your trip ever exceeds that.
Step 2: Add a Transfers tab for repayments
Create a tab named Transfers. Paste the following header into A1. Leave the rows beneath it empty until money has actually changed hands.
From To Amount
A reimbursement belongs here, not in Expenses. If Ben sends Ana 75, enter Ben in column A, Ana in B and 75 in C. Recording that transfer as another trip purchase would inflate the trip cost and distort everybody's share.
Keep amounts positive. Use the sender and recipient columns to describe the direction. If you entered a transfer by mistake, correct that row rather than inventing a balancing restaurant bill.
Step 3: Build the Balances tab
Create a tab named Balances and paste this block into A1. The formulas use English function names and comma separators; some spreadsheet locales require semicolons between function arguments.
Person Paid for trip Own share Sent repayments Received repayments Balance Ana =SUMIF(Expenses!B$2:B$1000,A2,Expenses!C$2:C$1000) =SUM(Expenses!D$2:D$1000) =SUMIF(Transfers!A$2:A$1000,A2,Transfers!C$2:C$1000) =SUMIF(Transfers!B$2:B$1000,A2,Transfers!C$2:C$1000) =B2-C2+D2-E2 Ben =SUMIF(Expenses!B$2:B$1000,A3,Expenses!C$2:C$1000) =SUM(Expenses!E$2:E$1000) =SUMIF(Transfers!A$2:A$1000,A3,Transfers!C$2:C$1000) =SUMIF(Transfers!B$2:B$1000,A3,Transfers!C$2:C$1000) =B3-C3+D3-E3 Cara =SUMIF(Expenses!B$2:B$1000,A4,Expenses!C$2:C$1000) =SUM(Expenses!F$2:F$1000) =SUMIF(Transfers!A$2:A$1000,A4,Transfers!C$2:C$1000) =SUMIF(Transfers!B$2:B$1000,A4,Transfers!C$2:C$1000) =B4-C4+D4-E4
A positive balance means receive money. A negative balance means pay money. Sending a repayment moves a negative balance toward zero; receiving one reduces a positive balance. That is why sent repayments are added and received repayments are subtracted.
You may replace Ana, Ben and Cara with your group's names in both tab headers and the Person column. The Own share formulas depend on column position, so keep each person's share column aligned with their summary row.
Worked example: settle a 450-unit trip
With the sample purchases and no transfers, the result should be:
| Traveler | Paid | Own share | Balance |
|---|---|---|---|
| Ana | 300 | 165 | +135: receive |
| Ben | 90 | 165 | −75: pay |
| Cara | 60 | 120 | −60: pay |
One valid settlement is Ben sending Ana 75 and Cara sending Ana 60. Enter those two payments in Transfers. All three final balances should become zero. The trip still cost 450; repayments did not make it a 585-unit trip.
Before asking anyone to pay, check two things: every expense's Check cell is zero, and =SUM(F2:F4) on Balances is zero. A zero total is necessary, but it cannot catch every mistake. An incorrect payer name or a transfer to someone outside the group needs a row-by-row review.
Foreign currencies, refunds and deposits
Currency: convert each purchase into the agreed settlement currency before entering Amount and shares. Keep the original currency, original amount and conversion basis in extra note columns to the right. Use the actual card charge where available, or a rate the group explicitly accepts. Never add euros and dollars as if they were the same unit.
Refund: if Ana receives a 30-unit refund for an equally shared purchase, add an expense row with Ana as payer, Amount −30 and each person's share −10. Link the note to the original purchase. This reverses both the payment credit and the cost allocation.
Deposit: enter only the amount paid, allocating that deposit across participants. When the balance is paid, add a separate row for the remaining payment. Do not enter the full booking and then its deposit again.
Make the sheet usable for the whole group
Give editing access only to people who need it, protect formula cells if you wish, and keep receipts somewhere the group can access. Do not put card numbers, passport details or hotel access codes in this workbook. A short receipt reference is enough.
Review purchases while everybody still remembers them. Then settle once, record the transfers and keep a final copy. If a late refund arrives, add it openly and recalculate rather than silently changing an old paid-out settlement.
Can this work for four or more travelers?
Yes, but the supplied block is specifically for three. Add one share column per extra traveler before Check, expand each row's SUM range, and add a Balances row with the matching share-column sum. Update the zero-total check to include everyone. Test with one small expense before entering the whole trip.
This is a transparent DIY template, not a live-tested hosted workbook. Its example balances have been independently recalculated. For the wider trip schedule, pair it with our day-by-day itinerary templates.
Formula reference and editorial note
Google's SUMIF documentation explains the conditional totals used here. The reimbursement method and worked example are original to this guide. Written by the Amicly team, the team behind this travel-planning blog. Reviewed 26 October 2026.
Comments
Post a Comment