Use one Google Sheets row for each shared expense, or for each person's share when reimbursements are separate. A formula in the Paid Status column can then show Paid, Pending, or Overdue from the payment and due dates.
The sheet records the agreement and its history. It doesn't send money, verify a transfer, or replace the original receipt.
Set up columns that match the money
Start with a row structure that matches what people owe. One row per bill is fine when the group only needs one status; use one row per person's share when each person can pay separately.
| Column | What to enter | Purpose |
|---|---|---|
| A: Date | Date of the expense | Places the receipt in time |
| B: Description | Utilities, groceries, or rental car gas | Identifies the charge |
| C: Amount Share | Amount owed for that row | Feeds summary totals |
| D: Paid Status | Formula | Shows the current state |
| E: Payment Date | Date the person paid | Triggers Paid |
| F: Due Date | Agreed payment deadline | Triggers Overdue |
| G: Receipt Link | Link to the stored image or PDF | Preserves proof |
| H: Assigned To | Person who owes the share | Makes ownership clear |
| I: Notes | Split rule, dispute, or context | Keeps decisions with the row |
For an even $100 split among four people, enter four $25 rows if you need four separate payment statuses. If the group only tracks the bill as a whole, one $100 row is simpler.
Add the paid status formula
Create a sheet named Shared Receipt Tracker and put the headers in row 1. Headers stay visible.
- Enter the column names from the table above.
- Format columns A, E, and F as dates. Format column C as currency. Use actual date values, not notes that only look like dates.
- In D2, paste this formula:
=IF(LEN(E2)>0, "Paid", IF(LEN(F2)=0, "Pending", IF(TODAY()>F2, "Overdue", "Pending")))- Copy D2 down through the rows you expect to use.
LEN(E2)>0 treats a populated payment-date cell as paid. LEN(F2)=0 keeps an empty due date from being treated as overdue. The formula uses the current date, so the status can change as TODAY() recalculates.
With this version, a line stays Pending on its due date and becomes Overdue the next day. If your group treats the due date itself as late, change TODAY()>F2 to TODAY()>=F2.
Turns out, column references are easy to mix up. In this layout, payment date is E, due date is F, and receipt link is G. A formula that checks G will inspect the link, not the payment date.
Make each status easy to see
Select D2:D100, choose Format > Conditional formatting, and add custom formula rules like these:
| Status | Custom formula | Suggested appearance |
|---|---|---|
| Overdue | =$D2="Overdue" |
Red fill |
| Paid | =$D2="Paid" |
Green fill |
| Pending | =$D2="Pending" |
Yellow fill |
The dollar sign locks the D column. The row number stays relative, so the rule adjusts as it moves down the range.
Protect the formula cells after testing them. Choose Data > Protect sheets and ranges, select D2:D100, and limit who can edit that range. Group members can still update the expense, payment, due-date, and receipt-link columns.
Add a simple summary
Put these formulas on a separate Summary tab or in unused cells outside the tracker:
| Metric | Formula |
|---|---|
| Paid shares | =SUMIF($D$2:$D$100, "Paid", $C$2:$C$100) |
| Pending shares | =SUMIF($D$2:$D$100, "Pending", $C$2:$C$100) |
| Overdue shares | =SUMIF($D$2:$D$100, "Overdue", $C$2:$C$100) |
Extend every range if the tracker grows beyond row 100. These formulas total the Amount Share column, not the original receipt total. That distinction matters when one receipt appears across several share rows.
Keep receipt links separate from payment status
Paste the link to the original receipt into column G. Keep the status formula in column D, and enter the payment date in E. Don't replace a formula with the word Paid.
Sometimes one receipt covers several shares. Repeat the same link across those rows. That's fine. Repetition is clearer than making everyone search through another tab later.
To be honest, a receipt link only helps if the intended reviewers can open it. Test the link with the access level your group will use, and avoid putting bank account numbers or other private financial details in a broadly shared sheet.
If you also need to track missing uploads, add a separate Receipt Status column. A payment can be Paid while its receipt is still missing, so combining those two ideas creates confusion.
Agree on the split rule before anyone pays
Your formula tracks status. It doesn't decide whether the amount is fair.
Equal splits are simple. Usage, room size, nights stayed, or income can better fit an uneven arrangement. A reimbursement-after-proof rule can require a receipt or other agreed evidence before a line is treated as settled.
Write the rule in Notes before the first payment. For recurring utilities, record each billing period separately. For a trip, enter lodging deposits, meals, gas, cancellations, and reimbursements as separate rows so dates and statuses don't blur together.
Thing is, a status label doesn't settle a disputed amount. Record the question in Notes and decide whether your group needs a separate dispute column.
Share the sheet with a clear update process
Use Share to choose specific people or link access, then select Viewer, Commenter, or Editor. Give Editor access only to people who need to enter expenses or payment dates.
Specific-person sharing is a better fit when receipts contain personal details. If you use link access, check whether the link permits viewing or editing before sending it.
One updater and several reviewers can keep formula columns intact. Use comments for questions, and set a regular review point, such as a weekly check-in for household bills or an update after each major trip booking.
Keep tracking separate from payment. The sheet can document a request, a due date, a receipt, and a payment date; a bank or payment app handles the actual transfer.
Test the tracker before sharing it
Create a temporary sample row with a description such as Utilities. Use a due date a few days ahead, leave the payment date blank, and confirm that the status shows Pending. Change the due date to yesterday, check for Overdue, then add a payment date and confirm Paid.
Watch for these common problems:
- D2 checks G2 instead of E2, so the receipt link controls the status.
- A due date is blank, but the formula has no blank-date guard.
- The formula was copied only through the first few rows.
- Someone typed a note such as "sent" instead of a payment date.
- A receipt link works for its owner but not for the group's reviewers.
- An editor overwrote a formula cell with manual text.
Delete the test row or label it clearly before the group begins using the sheet.
FAQ
Will the paid status update automatically?
It can change as TODAY() recalculates. The payment date in E takes precedence, so a populated payment date shows Paid even when the due date has passed.
Can one receipt cover several people?
Yes. Use one row per person's share when their payment statuses differ, then repeat the shared receipt link in each related row.
Can this replace a payment app?
No. It is a recordkeeping tool for shared expenses, receipts, requests, and status. It doesn't move funds or confirm that a transfer cleared.
Create the sheet, run one temporary row through Pending, Overdue, and Paid, then protect D2:D100. After that, share the file with the smallest access level your group needs.