Google Sheets works well for a small team expense tracker when the sheet records more than a running total. Add a real date, the payer, the cost, the split rule, and each person's share. Then a trip, event, club fund, or household group can review charges in date order.
Keep expenses and repayments separate. A repayment isn't a new purchase. Log it on a Settlements tab so the summary shows what remains.
Use three tabs when the group needs balances
For a simple chronological list, the Transactions tab is enough. If people need to see who owes what, three tabs keep the logic readable.
| Tab | What it holds |
|---|---|
| Transactions | One row for each shared expense |
| Settlements | Money actually sent from one person to another |
| Summary | Amount paid, amount owed, and the remaining balance |
Don't mix a $20 repayment with a $20 grocery purchase. The totals become difficult to explain.
Choose columns that answer the money questions
The date column does the organizing, but the other fields prevent confusion about who paid and how the cost was divided.
| Column | What to enter | Why it helps |
|---|---|---|
| Date | The actual expense date | Sorts trips, events, and monthly costs chronologically |
| Description | A short detail such as team bus fuel | Makes the charge recognizable later |
| Category | Travel, Meals, Supplies, Rent, or Utilities | Supports category filters |
| Amount | The full cost as a positive number | Provides the amount to split |
| Paid By | The person's exact name | Feeds the paid totals |
| Split Type | Equal, Usage-based, Nights-stayed, or Custom | Records the rule used |
| # Sharing | Number of people included in the split | Supports equal-share formulas |
| Receipt or Notes | A receipt link or explanation | Preserves useful context |
| Participant shares | One column per person | Shows each person's portion |
| Share check | Participant shares minus Amount | Should equal 0.00 |
Put participant names in the share headers, such as John, Sarah, Mike, and Alex. Use the same spelling on the Summary tab and in the Paid By and settlement fields.
Use one row per expense. If you want lodging and parking reviewed separately, enter two rows.
Build the Google Sheets tracker
-
Open a blank Google Sheet, rename it, and create three tabs named
Transactions,Settlements, andSummary. -
On
Transactions, enter this row-one structure. Replace the example names with your actual group members:Date | Description | Category | Amount | Paid By | Split Type | # Sharing | Receipt or Notes | John | Sarah | Mike | Alex | Share check -
Format column A as a date. Format Amount and the participant share columns as currency. Freeze the first row so the headers remain visible while people add entries.
-
Add a test expense. For example, enter
2026-01-15,Gas for team trip,Travel,45.50,Alex,Equal, and4. If all four people participated, the four share cells can total $45.50, such as $11.38, $11.38, $11.37, and $11.37. -
For an equal split, enter
=IF($G2=0,"",ROUND($D2/$G2,2))in each participating share cell and copy it across those cells. Leave nonparticipants blank. Because cents may not divide evenly, adjust one share until the check column reaches zero. -
In the Share check cell, enter
=ROUND(SUM(I2:L2)-D2,2). A result of0.00means the participant shares match the expense total. A nonzero result needs a quick correction. -
On
Settlements, add these headers:Date | From | To | Amount | Note. Record a repayment when money actually moves. Use positive amounts, and keep the names consistent with the other tabs.
For a custom split, type the actual amount owed in each participant share cell instead of using the equal-share formula. That works for usage-based costs, different room sizes, nights stayed, or another rule your group has agreed to use.
Add a Summary tab for who owes what
On Summary, put the participant names in B1:E1. Keep them in the same order as the share columns on Transactions, and spell them exactly the same way.
Set up the labels in column A:
| Cell | Label |
|---|---|
| A2 | Paid |
| A3 | Owed |
| A4 | Gross balance |
| A5 | Sent in settlements |
| A6 | Received in settlements |
| A7 | Remaining balance |
| A8 | Status |
Enter these formulas in column B, then copy them across to column E:
| Cell | Formula | Meaning |
|---|---|---|
| B2 | =SUMIF(Transactions!$E$2:$E$1000,B$1,Transactions!$D$2:$D$1000) |
Total paid by the person |
| B3 | =SUM(Transactions!I$2:I$1000) |
Total owed by the person in the first share column |
| B4 | =B2-B3 |
Paid minus owed |
| B5 | =SUMIF(Settlements!$B$2:$B$1000,B$1,Settlements!$D$2:$D$1000) |
Money sent by the person |
| B6 | =SUMIF(Settlements!$C$2:$C$1000,B$1,Settlements!$D$2:$D$1000) |
Money received by the person |
| B7 | =B4+B5-B6 |
Balance after recorded settlements |
| B8 | =IF(ROUND(B7,2)=0,"Even",IF(B7>0,"Should receive","Should pay")) |
Plain-language status |
When the B3 formula is copied right, its share column moves from I to J, K, and L. The formulas assume the tracker uses rows 2 through 1000; extend those ranges if your group needs more rows.
A positive remaining balance means the person should receive money. A negative balance means the person should pay. If Alex paid $45.50 and Alex's assigned share was $11.37, the gross balance would be $34.13 before any settlement.
Changing a note to "settled" won't change the calculation. Add the actual repayment to Settlements.
Sort and filter by date safely
Select the entire Transactions range before sorting. Use Data > Sort range > Advanced range sorting options, then sort by Date from A to Z. Sorting only column A can separate dates from their descriptions, payers, and share amounts.
Create a filter on the header row to view one category, payer, or date period at a time. Google Sheets also supports temporary filter views for people who want a private view of a shared sheet. See Google's sort and filter instructions for the current menu flow.
Turns out, the date has to be a real date value. If entries sort alphabetically, enter =DATEVALUE(A2) in a helper column, format the result as a date, and replace the text entries with corrected date values.
Share access without losing control
Use the Share button to invite the people who need the file. Give Editor access to active contributors, and use Viewer access for someone who only needs to review totals. Commenter access can suit a person who needs to raise questions without changing the numbers.
Thing is, edit access should stay narrow. Protect the header, participant formulas, Share check column, and Summary tab with Google Sheets' protected sheets and ranges feature. Protection helps prevent accidental edits, but it doesn't replace careful sharing permissions.
Receipt links need their own access check. If a receipt is stored elsewhere, make sure the intended viewers can open that file without exposing unrelated documents.
Set a routine the group can follow
To be honest, a tracker needs a routine more than it needs decoration. Choose a deadline that fits the group, then make the responsibility clear.
| Moment | Practical rule |
|---|---|
| New expense | Add the date, payer, amount, and split before the receipt is forgotten |
| Receipt capture | Link the receipt or explain why one is unavailable |
| Review | Check the date range and Share check column on a weekly or monthly schedule |
| Settlement | Add the From, To, amount, and date as soon as money changes hands |
A rule such as "add receipts within 48 hours and review on Saturday" is concrete, but use a different schedule if your group will follow it more reliably.
Common mistakes to avoid
The formulas are simple. Inconsistent entries cause most of the trouble.
- Typing dates as words or text instead of date values.
- Using nicknames in one row and full names in another.
- Sorting only the Date column instead of the full transaction range.
- Entering a repayment as a new expense.
- Leaving the Share check column nonzero.
- Giving edit access to people who only need to read the totals.
When a simpler log is enough
A Transactions-only sheet works for a club, team, or travel group that just needs a dated record of costs. Add Settlements and Summary when people need an ongoing view of reimbursements.
The sheet records what the group enters. It doesn't send money, verify receipts, or decide whether a split is fair. Agree on the rule first, then document it in Split Type and the participant shares.
FAQ
Can I track expenses with only a Date, Description, Amount, and Paid By column?
Yes, if you only need a chronological spending record. Those fields won't show each person's share or calculate a reimbursement balance.
How should I handle an uneven split?
Use Custom or another clear Split Type, then enter the actual amount owed in each participant share column. Check that the shares add up to the full Amount before moving on.
Should a reimbursement be listed as an expense?
No. Record the original cost on Transactions and the repayment on Settlements. This keeps spending totals separate from money transferred between people.
Can several people edit the sheet at once?
Yes, provided they have edit access. Keep the formula and summary areas protected, and ask contributors to add rows rather than overwrite existing entries.
Why does my date sort incorrectly?
The cells may contain text rather than date values. Use =DATEVALUE(A2) in a helper column, format the result as a date, and correct the original entries.
Create the three tabs now, enter two test expenses and one settlement, and confirm that every Share check cell reads 0.00 before inviting the group.