A due-date column makes a shared expense sheet useful after the purchase. Use one row per expense. The sheet can show what was bought, who paid, how much is involved, and whether reimbursement is still open.

This free layout suits roommates, travel groups, clubs, sports teams, families, and small committees. It tracks the record; it doesn't send payments or verify that a transfer happened. Who still owes money, and which bill needs attention first? A real due date and a consistent completion rule make those answers easier to find.

Use this column layout

Create a tab named Expenses and add these headers in row 1. The column letters matter because the formulas below use them.

Column Header What to enter
A Date The day the expense occurred
B Description A short label such as rental car, uniforms, or utilities
C Category Travel, groceries, lodging, supplies, or another group
D Payer The person who paid upfront
E Amount The total expense, formatted as currency
F Split Type Equal, usage-based, reimbursement, or custom
G Due Date The date a reimbursement or group payment is expected
H Status A formula-generated result
I Completion Date The date the relevant payment was fully settled
J Receipt/Notes A receipt link, explanation, or payment note

Use actual date values in columns A, G, and I. A date that only looks like text can stop comparisons from working. Pick one format and use it consistently, such as YYYY-MM-DD.

The Split Type field describes the agreed rule. It doesn't calculate each person's share by itself. If you need individual balances, add the optional Splits tab described below.

Add a due-date status formula

Put this formula in H2, then copy it down the column:

=IF(B2="","",IF(I2<>"","Paid",IF(G2="","No due date",IF(G2<TODAY(),"Overdue","Pending"))))

The formula leaves unused rows blank. It marks an expense as Paid when a completion date exists, shows No due date when the deadline is missing, and compares open rows with today's date.

Turns out, the hardest part is not the formula. It's deciding what "paid" means. For a single reimbursement, use the date the recipient receives the money. For a group bill, use the date everyone has settled, or track each person's payment on the optional tab.

If you want to identify late payments after they are completed, use this version instead:

=IF(B2="","",IF(I2<>"",IF(AND(G2<>"",I2>G2),"Paid late","Paid"),IF(G2="","No due date",IF(G2<TODAY(),"Overdue","Pending"))))

This status is a current view because TODAY() changes as the sheet recalculates. The completion date remains your historical record.

Build a small dashboard

A separate Dashboard tab keeps summaries away from the working ledger. Rename the main tab Expenses if you want to use these formulas as written.

For total spending, use:

=SUM(Expenses!E2:E)

To see how much one person has paid, use:

=SUMIF(Expenses!D2:D,"Jordan",Expenses!E2:E)

Replace Jordan with a cell reference if you want to switch between people.

A category summary can use:

=QUERY(Expenses!A1:J,"select C, sum(E) where B is not null group by C label sum(E) 'Total'",1)

To display only overdue rows:

=IFERROR(FILTER(Expenses!A2:J,Expenses!H2:H="Overdue"),"No overdue rows")

These formulas summarize the ledger. They don't calculate who owes whom. A payer total is the amount someone advanced, not necessarily that person's final share.

Set up the sheet in a practical order

  1. Create a blank Google Sheet and rename the working tab Expenses. Add a second tab called Dashboard.

  2. Paste the ten headers into row 1. Add one test row with a description, payer, amount, and due date.

  3. Format Amount as currency. Format Date, Due Date, and Completion Date as dates.

  4. Enter the status formula in H2 and fill it down far enough for your group. Keep the formula column separate from manual entry fields.

  5. Add a dropdown to F2:F with options such as Equal, Usage-based, Reimbursement, and Custom. A dropdown reduces spelling differences in reports.

  6. Add conditional formatting to H2:H. Use custom rules such as these:

    • =$H2="Overdue" for red
    • =$H2="Pending" for yellow
    • =OR($H2="Paid",$H2="Paid late") for green

  7. Test the workflow before inviting editors. Enter a past due date with no completion date, confirm that the row becomes overdue, then add a completion date and check the result.

A simple test catches most formula problems. Delete the test row afterward, or label it clearly so nobody mistakes it for a real expense.

Track individual reimbursements when needed

The main tab works well for a shared expense register. It becomes too broad when several people repay the same payer on different dates.

Thing is, a label such as Equal doesn't tell the sheet whether Alex owes $40, $52.50, or nothing. Create a Splits tab when the group needs person-level amounts.

Column Header What it records
A Expense A matching description or expense reference
B Person The person who owes the payer
C Amount Due That person's agreed share
D Amount Paid What that person has paid so far
E Due Date Their individual deadline
F Status A formula-generated result
G Paid Date The date their amount was fully paid

Enter one row for each person who owes money. Keep the amounts positive. In F2, use:

=IF(B2="","",IF(D2>=C2,"Paid",IF(D2>0,"Partial",IF(E2="","No due date",IF(E2<TODAY(),"Overdue","Pending")))))

This version distinguishes a partial payment from an unpaid one. Leave the main expense's completion date blank until the whole reimbursement is settled, or define a different rule and document it in the Notes column.

For an outstanding summary, put each person's name in column A of a Balances tab. In B2, C2, and D2, use:

=SUMIF(Splits!B:B,A2,Splits!C:C)
=SUMIF(Splits!B:B,A2,Splits!D:D)
=B2-C2

Label those columns Amount Due, Amount Paid, and Open Balance. These figures reflect what the group recorded. They do not verify a bank or payment-app transfer.

Share the tracker without losing control

Google Sheets can be shared with specific people or through link access. Choose the least access that fits the job.

  • Give editors to people who add expenses or update payment dates.
  • Give commenters to people who need to ask questions without changing rows.
  • Give viewers to people who only need to review the record.

Protect row 1 and the formula column so an accidental edit doesn't replace the headers or status logic. A protected range helps, but it doesn't replace a clear group rule about who updates each field.

If you use link sharing, review the access setting before copying the link. A private group usually has less risk when access is limited to the intended people rather than broadly editable. Don't store bank account numbers, passwords, or other unnecessary sensitive information in the sheet.

Use comments for disputes or missing receipts instead of deleting a row. The original entry helps explain what happened later.

Set a routine for due dates and receipts

The payer should add an expense while the receipt and details are still available. The person responsible for reimbursement should agree to the due date before the row is treated as final.

One row, one story. It sounds obvious, but groups often combine a utility bill, a reimbursement, and a later adjustment in one row, which makes the amount and deadline hard to interpret. Add a new row for a new expense or correction.

Review the Overdue rows on a regular day, such as the day before your group normally settles expenses. Use the Notes column for a receipt link or a short explanation, and record the completion date after payment rather than simply changing the status text.

To be honest, most broken trackers fail because people type over formulas, use inconsistent names, or leave the due date blank. Protect the formula range, use dropdowns where they help, and agree on whether the deadline applies to one person or the entire expense.

Create the Expenses tab first, add the status formula, and test one overdue row before sharing the file. Then add real expenses and invite only the people who need editing access.