Two records run the whole tracker. Purchases go on a Transactions sheet, and actual repayments go on a Settlements sheet. Keep them apart and the balance reflects the group, not just the latest receipt.
Each person's number follows one rule:
net balance = amount paid - assigned share + amount sent - amount received
A positive balance means the group owes that person. A negative balance means they owe the group. The sheet tracks the math. It doesn't move money, and it doesn't confirm that a transfer happened.
Build the Transactions sheet
One row per purchase. That's the entire habit. The layout below handles roommate rent, utilities, groceries, vacation costs, group dinners, club supplies, and similar shared expenses.
| Column | Heading | What goes there |
|---|---|---|
| A | Date | The date of the purchase |
| B | Description | A clear detail such as Groceries or February rent |
| C | Category | Rent, utilities, travel, meals, supplies, or another label |
| D | Paid By | The person who paid the vendor |
| E | Amount | The full cost as a number |
| F | Split Method | Equal, percentage, usage-based, custom, or reimbursement |
| G | Split Count | Number of participating people for an equal split |
| H:K | Person shares | One dollar-share column per participant |
| L | Split Check | Confirms that shares add up to the amount |
| M | Notes | Receipt details, exceptions, or an agreed rule |
Type the participants' real names into H1:K1, spelled exactly as they'll appear on the Balances sheet. Sarah in one place and Sara in another reads as two different people to the formulas.
- Create a workbook with
Transactions,Balances, andSettlementssheets. - Add the headings in row 1 and format the amount and share columns as currency.
- Freeze the first row if your spreadsheet tool supports it.
- Enter one expense per row, including the person who paid it.
- Keep formula cells in the summary and split-check columns away from the cells people edit regularly.
One payer per row, for now. Bills covered by two or more people get their own section below.
Enter each expense once
Say John pays $120 for groceries and four people split the cost. The row looks like this:
| Date | Description | Category | Paid By | Amount | Split Method | Count | John | Sarah | Mike | Alex | Check |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 2026-01-10 | Groceries | Groceries | John | 120 | Equal | 4 | 30 | 30 | 30 | 30 | OK |
The equal-share formula goes into H2, then gets copied across the share cells of everyone who participated:
=ROUND($E2/$G2,2)
This one goes into L2 and copies down the column:
=IF(ROUND(SUM($H2:$K2),2)=ROUND($E2,2),"OK","CHECK SPLIT")
If someone sat out, enter 0 instead of leaving their cell blank. A blank could mean anything. A zero means what it says. Set the split count to the number of actual participants, and copy the equal-share formula only into their cells. The check column should still read OK.
Rounding can leave a stray cent unassigned. Hand the remainder to one participant, then confirm the check still says OK.
For a custom split, type the exact dollar shares into H:K. For a percentage-based row, a share cell can hold something like =ROUND($E2*40%,2), with the other cells carrying percentages that add to the remaining 60. Either way, the check column catches shares that fall short or overshoot.
Create the "who owes what" summary
Lay the Balances sheet out horizontally, so each person's name lines up with the matching share column on Transactions.
| Row label in column A | Formula in B | Copy across |
|---|---|---|
| Paid to vendors | =SUMIF(Transactions!$D$2:$D$1000,B$1,Transactions!$E$2:$E$1000) |
Yes |
| Assigned share | =SUM(Transactions!H$2:H$1000) |
Yes, one share column per person |
| Unsettled net | =B2-B3 |
Yes |
| Sent in settlements | =SUMIF(Settlements!$B$2:$B$1000,B$1,Settlements!$D$2:$D$1000) |
Yes |
| Received in settlements | =SUMIF(Settlements!$C$2:$C$1000,B$1,Settlements!$D$2:$D$1000) |
Yes |
| Who owes what | =B4+B5-B6 |
Yes |
| Status | =IF(ROUND(B7,2)=0,"Settled",IF(B7>0,"Group owes them","They owe group")) |
Yes |
Put John, Sarah, Mike, and Alex in B1:E1, in the same order as the share columns H:K on Transactions. The Assigned share formula should point at those columns to match. Add a fifth person later and you'll add one share column plus one matching summary column.
Turns out the grocery row already proves the system works. John paid 120 but carries only a 30 share, so his unsettled net sits at positive 90. Sarah, Mike, and Alex each show negative 30 until they settle.
SUMIF handles one condition. SUMIFS handles several. Microsoft's references for SUMIF and SUMIFS cover the syntax Excel uses.
Add category and payer checks
Category totals let you review rent, groceries, travel, or utilities without touching the balance math. This formula totals grocery purchases paid by John:
=SUMIFS(Transactions!$E$2:$E$1000,Transactions!$C$2:$C$1000,"Groceries",Transactions!$D$2:$D$1000,"John")
In Google Sheets, you can also summarize categories with:
=QUERY(Transactions!A1:M1000,"select C, sum(E) where E is not null group by C label sum(E) 'Total'",1)
That QUERY option only exists in Google Sheets. In Excel, use SUMIFS or a small summary table instead.
Record reimbursements as transfers
A repayment is not a second grocery purchase. It moves money between people, so it gets its own record:
| Column | Heading | Example |
|---|---|---|
| A | Date | 2026-01-20 |
| B | From | Sarah |
| C | To | John |
| D | Amount | 30 |
| E | Method | Bank transfer |
| F | Reference | January groceries |
The Sent in settlements and Received in settlements rows on Balances pull from this sheet. A $30 payment from Sarah to John lifts Sarah's balance by 30 and drops John's by 30.
Thing is, logging that repayment as another expense inflates what the group actually spent. Record the payment once, and leave the original purchase row unchanged.
Some one-sheet templates handle reimbursements by setting the payer at 100 percent and everyone else at 0. That convention can work, but only if the row is marked clearly as a reimbursement, excluded from purchase totals, and never entered a second time on Settlements. A separate sheet is just easier to audit.
Choose the split rule before you enter rows
Equal isn't automatically fair. The right rule depends on who actually benefited from the expense, and on whatever your group worked out and agreed to beforehand, which is why writing the rule down matters more than which rule you pick.
| Split method | Useful for | Record carefully |
|---|---|---|
| Equal | Shared rent, common supplies, or a group meal | Confirm who participated |
| Per person | Dinners, gifts, and event costs | Use zero for nonparticipants |
| Usage-based | Utilities, mileage, or shared equipment | Keep the meter, mileage, or usage note |
| Nights stayed | Hotels, vacation rentals, and trip deposits | Record the dates or nights |
| Room size | Roommate rent arrangements | Write down the agreed room rule |
| Income-based | Household contributions with different incomes | Discuss and document the percentage |
| Exact dollar amount | Pet costs, personal purchases, or owner-specific expenses | Enter the agreed share directly |
Put the chosen rule in the Split Method column, and explain unusual decisions in Notes. A short note can prevent a long argument later.
Handle expenses with multiple payers
If two people paid the same bill, don't enter the expense twice. A duplicated row quietly doubles everyone's assigned shares.
Keep one Transactions row for the full cost, and add a Payments sheet showing who actually handed over money:
| Date | Description | Payer | Amount | Expense ID |
|---|---|---|---|---|
| 2026-01-10 | Groceries | John | 70 | G-001 |
| 2026-01-10 | Groceries | Sarah | 50 | G-001 |
Point the Paid to vendors row on Balances at this sheet:
=SUMIF(Payments!$C$2:$C$1000,B$1,Payments!$D$2:$D$1000)
The Transactions row stays the single source for shares. Who covered the bill and who benefited from it are now two separate questions, answered in two separate places.
Share the file without losing the math
Give editing access to the people who enter receipts. Everyone else can review the record without touching formulas, assuming your spreadsheet tool supports separate access levels.
Protect the summary and formula columns where you can. Keep version history available before resolving a disputed entry, and hang onto a dated copy or monthly export in case the group ever needs older records.
Agree on an update rhythm too. Some groups log each expense as it happens, others batch receipts once a week, and plenty of groups drift between the two, which is fine as long as receipts don't pile up for weeks. Something like "add receipts within 48 hours and review on Saturday" is concrete, but pick a deadline your group can actually follow.
Test the tracker before sharing it
Start with a small test set where you already know the right answers.
| Test | Expected result |
|---|---|
| Add the $120 grocery row | John shows positive 90; each other participant shows negative 30 before settlement |
| Add a $30 settlement from Sarah to John | Sarah reaches zero and John's balance falls by 30 |
| Change one participant's share to zero | The remaining shares must still total 120 |
| Enter a custom percentage split | The Split Check cell returns OK |
| Change a participant name in one sheet only | The mismatch should be corrected before real entries begin |
The first test catches broken formulas. The fourth catches a split rule that doesn't add up. To be honest, this is the step most tempting to skip. Don't. A formula slip caught today costs a minute; caught after three weeks of entries, it costs an afternoon of untangling.
Common mistakes to avoid
Most balance errors trace back to a handful of small habits:
- Counting a repayment as a second purchase.
- Leaving a nonparticipant blank instead of entering
0. - Using inconsistent names across the transaction and summary sheets.
- Overwriting a formula while entering a receipt.
- Mixing percentage values and dollar amounts in the same share row.
- Recording a disputed expense without a note or receipt reference.
If a balance looks wrong, check that row's Split Check first. Then work through the payer, the assigned shares, and the Settlements entries one at a time.
FAQ
Can the person who paid also have a share?
Yes. Paid By and personal share answer different questions. If John pays $200 for a shared cost and his own share is $50, he starts out positive $150 before other rows and settlements get counted.
How should roommates split an uneven bill?
Enter each person's exact dollar share, or calculate shares from an agreed percentage. Roommates often document room size for rent; utilities may depend more on usage or occupancy. The spreadsheet can compute the result, but the group still has to choose the rule and record it.
What if someone disputes a balance?
Pull up the original receipt, the payer, the participant cells, and the settlement record, then review them together. Describe any temporary agreement in a note instead of deleting the original row. Version history can help track down an accidental edit.
Is a spreadsheet enough for a travel group or club?
Often, yes, when the group wants a shared record and will enter receipts consistently. Apps may help with reminders or receipt scanning. They don't remove the need for a clear split rule, a real payment record, and a final review.
Create the Transactions, Balances, and Settlements sheets, enter the $120 test expense, and share the file only once the split check and the summary balance both behave as expected.