Someone covers a $60 grocery run, types the full receipt into the shared sheet, and three weeks later nobody remembers whether they got paid back. Most shared expense trackers break down right there. People confuse the total bill with the reimbursement amount. Someone pays for dinner, enters the whole receipt, and never logs who repaid them. A dedicated reimbursement column untangles the mess. It separates what was spent from what's still owed, which keeps everyone honest without the awkward end-of-month interrogations.
The Core 8-Column Layout
Open a new tab, name it Expenses, and set up these eight headers across row 1. Each detail gets its own cell.
| Column Name | Purpose | Example Entry | Notes / Formula |
|---|---|---|---|
| Date | Date the expense occurred | 2026-03-15 | Format as Date (YYYY-MM-DD) |
| Description | Short name of the purchase | Grocery run | Plain text |
| Category | Spending bucket | Groceries | Use consistent categories |
| Total Amount | Full receipt amount | 120.00 | Format as Currency |
| Paid By | Person who fronted the cash | Alex | Use a dropdown list |
| Split Method | Rule for dividing the bill | Even | Dropdown: Even, Custom, or Full |
| Reimbursement Due | Amount owed back to the payer | 80.00 | =IF(F2="Even", D2*(2/3), "") |
| Status | Settlement status | Pending | Dropdown: Pending or Settled |
One transaction per row, always. Combining two receipts into a single entry is how totals go wrong later.
Fixing the Split Math
Thing is, basic split math trips up more households than you'd expect. Say three roommates share an apartment and Taylor covers a $150 electric bill. Taylor didn't loan the house $150: she covered $50 for herself and $100 for the other two, so the sheet needs to log $100 as the reimbursement target rather than the full receipt amount, because logging the whole bill means someone gets overpaid.
For an even three-way split, here's the formula for column G:
=IF(F2="Even", D2 * (2/3), "")
If your group would rather track each individual share, divide the total evenly across all members instead:
=IF(F2="Even", D2 / 3, "")
Pick one method and use it everywhere. Mixing both styles in the same column is how balances go sideways.
Dropdown Lists for Consistent Records
Spelling breaks shared sheet formulas faster than anything else. One person types Alex, another types alex with a trailing space, and your summary formulas suddenly count two different people. In-cell dropdowns stop the typos at the source.
The steps:
- Select column E, starting at cell E2 and running down through your active range.
- Click Data in the top menu and open Data validation.
- Add a rule, select Dropdown, and list the names of everyone in your group.
- Repeat for column H using simple status tags like Pending and Settled.
Google's official instructions on creating in-cell dropdown lists also show how to assign custom colors to each status.
Filter Views for Shared Access
Here's a headache every shared sheet eventually hits. Two people open the file at once, one applies a regular column filter, and the view rearranges for everybody. Filter views fix that. Turns out, they save your personal display settings, so what your roommate sees on their phone or laptop stays put. If you want the full picture, this walkthrough on filter views without affecting others covers the basics, and Google's developer guide explains how to manage data visibility with filters.
Creating one is quick. Go to Data, select Filter views, and click Create new filter view. Then click the filter icon on column H and uncheck Settled so only Pending balances show. Last, name the view Unpaid Bills in the top-left banner.
Building a Clean Summary Tab
Create a second tab named Summary. Put each person's name in column A, then add a conditional formula in column B that totals pending amounts. No manual tallying.
The usual trap is SUMIF, which happily counts bills someone settled six months ago. SUMIFS checks both the name and the reimbursement status, so use it instead:
=SUMIFS(Expenses!G:G, Expenses!E:E, A2, Expenses!H:H, "Pending")
The formula scans the Expenses tab, matches the payer in column E against the name in cell A2, and sums only the rows where column H still reads Pending. Once a payer gets their money, flip that row's status dropdown to Settled. The balance drops to zero on its own.
Protecting Formulas and Setting Ground Rules
Shared sheets usually die the same way: someone adds a receipt and wipes out a formula cell while they're at it. Protecting the calculation columns stops that. This walkthrough of protect sheets and ranges covers the mechanics; for this setup you'd lock columns F and G and leave A through E open for daily entry.
To be honest, the sheet can't send payments by itself. That part runs on a group routine. Pick a set day, such as the first of the month, and use it to review the Summary tab, transfer the funds, and toggle every completed row to Settled. Put that reminder in your phone now, before the first receipt goes in.