A spreadsheet can manage a club's payment reminders without another app. Use one row for each member's charge, enter a real due date, and let conditional formatting show which rows need attention. The treasurer still checks the club's payment record and sends the message.

It won't verify a transfer or send a reminder by itself. This setup works well for recurring dues, sports-team fees, PTA collections, and small event reimbursements. Turns out, row design matters more than the colors.

Define one row before you add colors

Decide what one row represents before you build any formatting. Use one row per member per billing period or event charge.

If Jordan owes April and May dues, create two rows. Don't overwrite April with May.

Keep each member's amount due in the tracker. Store an event's total cost on a separate split tab, then copy each person's share into the member rows.

Start with this layout:

Column What it records Example
Member Person who owes the money Jordan Lee
Charge or Period What the payment covers April dues
Due Date Agreed payment deadline 2026-04-15
Amount Due Member's share of the charge $50.00
Paid? Yes or No status No
Paid Date Date the club confirmed payment
Last Reminder Date the latest reminder was sent
Notes Receipt reference or useful context Monthly membership

A date such as 2026-04-15 should be stored as a date value, not plain text. That makes sorting, filtering, and date formulas more reliable.

Calculate shares for events

Club dues usually don't need a Participants column. For a dinner, tournament, trip, or other shared expense, create an Event Splits tab.

For an equal split, use these columns:

  • A: Expense
  • B: Total cost
  • C: Number of participants
  • D: Equal share

In D2, enter:

=IF(OR(B2="",C2="",C2=0),"",B2/C2)

The formula stays blank while the inputs are incomplete. It also avoids a division error when the participant count is zero. Copy the result into the Amount Due cell for each participating member.

Uneven shares need a different rule. A weight might represent nights stayed, meals used, or another measure the group agrees on before collecting money. Use this member-level structure:

A: Event
B: Member
C: Total Cost
D: Weight
E: Amount Due

In E2, use:

=IFERROR($C2*$D2/SUMIF($A$2:$A$50,$A2,$D$2:$D$50),"")

Repeat the same event name and total cost on each related row. When every member has a weight of 1, the formula produces an equal split.

Add clean inputs and protect the formulas

Format Due Date as Date and Amount Due as Currency. Leave an unknown date blank. Don't type TBD into a date column that your rules need to read.

For the simple tracker, select the Paid? range, such as E2:E500, and add a dropdown with only Yes and No. In Excel, use Data > Data Validation > List and set the source to Yes,No. In Google Sheets, use Data > Data validation and create the same two-option dropdown.

Microsoft's Excel data validation guidance describes list restrictions as a way to keep entries consistent in shared workbooks.

Thing is, a dropdown only helps when everyone uses the same labels. Don't mix No, Unpaid, and N/A if your formulas expect No.

Protect the split formulas and summary cells. Give the treasurer edit access, and limit editing to the cells that need regular updates.

Apply conditional formatting for reminders

Select A2:H500 if you want the whole row to change color. If you only want the date cell highlighted, select C2:C500 instead.

In Excel, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. In Google Sheets, choose Format > Conditional formatting > Custom formula is.

Add a red rule for overdue charges:

=AND($C2<>"",$C2<TODAY(),$E2="No")

Add a yellow or orange rule for charges due today through the next seven days:

=AND($C2<>"",$C2>=TODAY(),$C2<=TODAY()+7,$E2="No")

Add a green rule for paid rows:

=$E2="Yes"

The dollar signs lock the column while the row number stays relative. That lets the same rule evaluate each row correctly.

Microsoft's date-highlighting example shows the Excel approach to date-based formatting.

Don't use a weekend check to label a payment overdue automatically. If the club agrees that a Saturday deadline moves to Monday, enter Monday as the agreed due date or explain the exception in Notes.

Test each rule with a past date, a date within seven days, a paid row, and a blank date. Blank-date checks matter.

Add a small control panel

Put these formulas above the tracker or on a separate Summary tab:

Unpaid rows: =COUNTIF($E$2:$E$500,"No")
Outstanding: =SUMIF($E$2:$E$500,"No",$D$2:$D$500)
Due in next 7 days: =SUMIFS($D$2:$D$500,$E$2:$E$500,"No",$C$2:$C$500,">="&TODAY(),$C$2:$C$500,"<="&TODAY()+7)

These totals assume one row per member charge and no duplicate rows for the same obligation. If you use an event split tab, enter only each member's share in Amount Due.

Review and send reminders on a set schedule

A fixed review day keeps the sheet from becoming a forgotten list.

  1. Review the tracker on a set day, such as Friday, and filter for red or yellow rows.

  2. Check the club's receipt, cash log, or payment confirmation before marking a row paid.

  3. Send a private reminder for each confirmed unpaid charge. Keep the wording neutral:

    Hi Jordan, April dues of $50.00 are still marked unpaid and were due April 15. If you've already paid, send the payment date or confirmation so I can update the record. Thanks.

  4. Change Paid? to Yes, enter the Paid Date, and record the Last Reminder date after each update.

  5. Add the next billing period as a new row. Keep older rows so the club can review its payment history.

The color is a prompt to check the record, not proof that money is missing.

Keep sharing and privacy simple

To be honest, a view-only link is not automatically private. It can still show every member's balance and notes.

Share the full tracker only when the club has agreed that members may see the group ledger. If balances should stay private, keep the main file with the treasurer and send individual reminders instead. A separate member-facing sheet can show general dues information without exposing other members' balances.

Don't put passwords, bank account numbers, or full payment credentials in the Notes column. Record only the detail needed to identify the payment.

If members pay in parts

A Yes/No column hides partial payments. If split payments happen often, use a Payments tab rather than writing notes such as "half paid."

Use these columns on the payment log:

Member, Charge or Period, Payment Date, Amount Received, Method, and Confirmation Note.

A partial-payment version of the main tracker can use this order:

Member, Charge or Period, Due Date, Amount Due, Amount Received, Balance, Status, Paid Date, Last Reminder, Notes

If Payments is the sheet name, calculate the amount received for row 2 with:

=SUMIFS(Payments!$D:$D,Payments!$A:$A,A2,Payments!$B:$B,B2)

Then calculate the balance in F2:

=IF(D2="","",MAX(0,D2-IF(E2="",0,E2)))

Calculate the status in G2:

=IF(D2="","",IF(F2=0,"Paid",IF(E2>0,"Partly paid","Unpaid")))

For this version, change the conditional-formatting rules to reference $G2 and use the balance column for outstanding totals. Protect the formula columns after testing them.

Fix common spreadsheet mistakes

  • A share formula divides by zero: Keep the blank or IFERROR check in the event split formula.
  • A date is treated as text: Format the column as Date and enter a real date value.
  • One row is reused for several months: Add a new row for every member charge and billing period.
  • A formula is overwritten: Protect formula cells and designate one person to maintain the file.
  • A payment is marked too soon: Confirm the receipt, cash record, or payment confirmation first.

Create a few dummy rows before sharing the file: one overdue charge, one charge due within seven days, one paid row, and one blank date. Confirm that the colors and totals change as expected, then replace the test rows with real entries.