Free Excel template

Free club membership and dues tracker template

For treasurers of clubs, associations, alumni groups, PTAs and leagues who collect yearly dues. List each member and their type; the template looks up the dues from your rate table, works out each balance, flags who is overdue once the grace period ends, and totals expected and collected dues by member type.

cellwise-club-membership-dues-tracker-template.xlsxExcel workbook · 5.7 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H	I
1	Club membership and dues tracker, 2026-27 season
2	As-of date	Nov 2, 2026			Member type	Annual dues
3	Dues due by	Oct 15, 2026			Adult	60
4	Grace period (days)	14			Family	95
5	Days past due date	18			Senior	40
6					Student	25
7
8	Member	Type	Member since	Years	Dues	Paid	Paid on	Balance	Status
9	Alice Moreno	Adult	Mar 14, 2019	7	60	60	Sep 28, 2026	0	Paid
10	Brian Hughes	Family	Jun 2, 2021	5	95	95	Oct 2, 2026	0	Paid
11	Carmen Lee	Senior	Jan 20, 2015	11	40	40	Sep 30, 2026	0	Paid
12	David Novak	Adult	Sep 10, 2024	2	60	30	Oct 12, 2026	30	Partial, overdue
13	Erin Walsh	Student	Sep 5, 2025	1	25	0		25	Overdue
14	Frank Osei	Adult	Apr 22, 2018	8	60	60	Oct 14, 2026	0	Paid
15	Gina Russo	Family	Feb 11, 2020	6	95	95	Oct 5, 2026	0	Paid
16	Hector Ruiz	Senior	Nov 30, 2012	13	40	0		40	Overdue
17	Ines Duarte	Student	Jan 15, 2026	0	25	25	Oct 1, 2026	0	Paid
18	James Patel	Adult	May 8, 2023	3	60	0		60	Overdue
19	Kara Bennett	Family	Aug 19, 2017	9	95	50	Oct 20, 2026	45	Partial, overdue
20	Luis Ortega	Adult	Oct 1, 2022	4	60	60	Oct 9, 2026	0	Paid

How the formulas work

Dues from your rate table

E9=IFERROR(VLOOKUP(B9,$E$3:$F$6,2,FALSE),0)

VLOOKUP with FALSE finds the exact member type in the rate table and returns its dues. Change the rate once in column F and every member of that type updates. IFERROR returns 0 for a typo like "Adlut", so check for members showing $0 dues; a drop-down list on column B (Data > Data Validation > List, source E3:E6) prevents the typo in the first place.

Balance and status with a grace period

I9=IF(H9<=0,"Paid",IF($B$5>$B$4,IF(F9>0,"Partial, overdue","Overdue"),IF(F9>0,"Partial","Unpaid")))

Balance (column H) is dues minus paid. A zero or negative balance is Paid. Otherwise the formula checks whether today is more than the grace period past the due date: B5 works out the days past due (18 on November 2), which is more than the 14-day grace period, so unpaid members are Overdue and part-payers are Partial, overdue. Before the grace period ends they show Unpaid or Partial.

Years as a member

D9=DATEDIF(C9,$B$2,"y")

DATEDIF with "y" counts complete years from the join date to the as-of date. It is handy for long-service recognition or loyalty discounts: row 38 counts members with five or more years. DATEDIF does not appear in Excel's formula autocomplete, but it works in every version.

Expected and collected dues by type

C30=SUMIF($B$9:$B$26,A30,$E$9:$E$26)

SUMIF adds the Dues column for every member of the type in A30. Column D does the same with the Paid column, and E is what is still outstanding. This is the table to bring to a board meeting: in the sample, Family memberships have the most still outstanding ($140).

Count members to chase

C37=COUNTIF(I9:I26,"*overdue")

The wildcard * stands for any number of characters, including none, so "*overdue" matches both Overdue and Partial, overdue in one COUNTIF. COUNTIF is not case-sensitive, which is why the capital O in Overdue still matches. Adding a second COUNTIF for "Overdue" would count those members twice, a common mistake with wildcards. Filter column I to check the list before you send reminders.

Make it yours

Use a fixed as-of date in B2 rather than TODAY() when you send a report to the board, so the statuses do not change after you send it. Update it each time you work on the sheet.
Members paying in installments: keep a separate payments sheet (date, member, amount) and replace column F with =SUMIF(Payments!B:B,A9,Payments!C:C).
Highlight problem rows: select A9:I26, then Home > Conditional Formatting > New Rule > Use a formula, enter =ISNUMBER(SEARCH("overdue",$I9)) and pick a light red fill.
Start each season with a copy of the sheet (right-click the tab > Move or Copy > Create a copy), clear Paid and Paid on, and update the due date and rates.

FAQ

How do I track membership dues in Excel?

List members with their type, look up the dues for that type from a small rate table with VLOOKUP, enter what each person has paid, and subtract to get the balance. A status column with IF then shows Paid, Partial or Overdue.

How do I flag overdue payments in Excel?

Compare the as-of date with the due date plus the grace period, and check whether a balance remains: for example =IF(AND(balance>0,as_of-due_date>grace),"Overdue","").

Can I charge different dues to different members?

Yes. Add a row to the rate table for each member type (for example Junior or Lifetime with 0 dues), and the VLOOKUP picks it up. For a one-off discount, type the amount straight into the Dues cell for that member.

Functions used

Related guides

More free templates

Updated November 2, 2026