Membership Register Template

About this template

When to use it

One member per row for a gym, studio or class business — membership type, join date, credits left, expiry, and the date the record has to be destroyed. Three sheets — Dashboard, Input data, Usage guide — and only Input data is typed into. Replace the template subtitle in B3 first.

What the dashboard holds

Key metrics in B7:E7 — rows entered, Credits left total, Credits left average, Credits left max — read column G of Input data. Summary by membership type in B12:F15 groups the same column by the type in column E. The New members per month table (B19:C22) is typed in by hand.

Filling order

1. Rows 2–21 of Input data take Member No., Member name, Date of birth, Phone, Membership type, Date joined, Credits left, Expiry date, Retention until (destroy by) (A–I). Eight sample rows ship, M-2026-001 to M-2026-008.
2. The type labels on the Dashboard are Term (12 months), Term (6 months), Session pass (30), Trial (B12:B15) and the formulas match them exactly.
3. Column G takes plain numbers (note 2). A zero is meaningful — M-2026-006 has run out but has not expired — so enter 0 rather than leaving the cell blank, because a blank is skipped by COUNT.
4. Set the expiry date from the join date and the type, and the retention date from what the member consented to at sign-up.
5. Replace the four monthly counts (Mar–Jun: 32 · 28 · 41 · 47) with your own.

Why the retention column is there

A membership list is personal data from the first row. This template keeps the identity check to date of birth and phone number and no further (note 6): there is deliberately no resident registration number field and no other unique identifier, and the member number is what distinguishes two people with the same name. Column I then records the date on which the record will be destroyed, and note 7 makes it explicit that this date must equal the period the member consented to when joining. That is the whole point of putting it in the register: a consent taken at sign-up is otherwise remembered by nobody, and a member who left two years ago quietly stays on file forever. When someone cancels or expires, keep the row only until the date in column I, then destroy it and delete the row.

How the formulas calculate

• =COUNT(‘Input data’!$G$2:$G$21) counts members with a numeric credit balance, zeros included.
• The type block is =COUNTIF(‘Input data’!$E$2:$E$21,$B12), =SUMIF(‘Input data’!$E$2:$E$21,$B12,’Input data’!$G$2:$G$21) and the matching AVERAGEIF, with a share of =IFERROR(C12/COUNT(‘Input data’!$G$2:$G$21),0).
• Under the monthly table, =SUM(C19:C22) and =AVERAGE(C19:C22) — 148 and 37 as shipped.
• Nothing watches the two dates. They ship as ISO text, so wrap them in DATEVALUE and add the alerts in spare columns: =IF(AND(H2<>””,DATEVALUE(H2)<=TODAY()+30),”Renewal due”,””) for expiry, and =IF(AND(I2<>””,DATEVALUE(I2)<=TODAY()),”Destroy”,””) for retention.
• The ranges stop at row 21; widen $E$21 and $G$21 as the list grows.

Examples

• Row 2 — M-2026-001 · Kim ○○ · 1994-03-11 · 010-2481-**07 · Term (12 months) · joined 2026-01-06 · 74 credits · expires 2027-01-05 · destroy by 2029-01-05.
• The eight shipped members hold 246 credits between them, so the average cell reads 31 (30.75 before the cell’s rounding) and the max reads 96.
• Each of the four types holds two rows, so every share cell shows 25.0%.
• The two Trial rows carry short retention dates — 2026-12-21 and 2027-01-07 — because a trial consent is short.

Common mistakes

• Writing a type as “12M” or “Monthly”, which counts in no summary row.
• Leaving Credits left blank for a member who has used everything up, which drops them from the row count as well.
• Copying the retention date from another row instead of deriving it from that member’s consent.
• Adding a resident registration number, ID scan or card number column to make check-in faster.