Gym Membership Management System in Excel: Plans, Renewals, Check-ins, Receipts and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: gym-membership-management-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Members, Check-ins, Receipt, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Raja Fitness, Pune, as of 17 October 2026 (made-up data). Four plans: Monthly (30 days, ₹1,500), Quarterly (90 days, ₹4,000), Half-yearly (180 days, ₹7,000) and Yearly (365 days, ₹12,000). Three trainers: Salman, Zoya and Amir.

Today 7 members are Active, 3 are Expiring and 2 have Expired. For a personal workout log with sets and personal bests, see the gym workout log; this page is the owner's side of the counter.

2. Build it sheet by sheet

Step 1. Setup: plans, trainers and the reminder window

1. In B3 type the as-of date (17-10-2026, or =TODAY() to keep it live) and in B5 the reminder window in days (7).
2. Type the plans in A8:C11 with Plan, Days, Fee. In D8 type =C8/B8 for the fee per day; it shows why the yearly plan is the best deal (₹32.9 a day against ₹50).
3. Type trainers in F8:F10 and payment modes in G8:G10. These lists feed the drop-downs on Members.

Step 2. Members: one row per membership

1. Headers in row 4: Member ID, Name, Plan, Trainer, Start, End, Fee, Discount, Paid, Balance, Days left, Status, Visits (last 30 days).
2. In F5 work out the end date: =E5+XLOOKUP(C5,Setup!$A$8:$A$11,Setup!$B$8:$B$11)-1. The −1 matters: a 30-day plan starting 1 October ends on 30 October.
3. In G5 bring in the fee with XLOOKUP and in J5 the balance =G5-H5-I5.
4. In K5 days left =F5-Setup!$B$3 and in L5 the status: =IF(F5<Setup!$B$3,"Expired",IF(F5-Setup!$B$3<=Setup!$B$5,"Expiring","Active")). Raja (G06) ends today, so 0 days left shows Expiring.
5. Colour L5:L16 with conditional formatting: red for Expired, amber for Expiring. A renewal is a new row, so the history stays.

Step 3. Check-ins: one row per visit

1. Headers: Date, Member ID, Name, In, Out, Minutes, Valid on that day?. C5 looks up the name with XLOOKUP(B5,…,"Check ID") so a mistyped ID shows at once.
2. In F5 minutes in the gym: =ROUND((E5-D5)*1440,0); see time calculations.
3. In G5 test the visit against the paid period with AND: Yes when the date is between that member's start and end.
4. Back on Members, M5 counts recent visits: =COUNTIFS('Check-ins'!$B$5:$B$600,A5,'Check-ins'!$A$5:$A$600,">"&(Setup!$B$3-30)).

Step 4. Receipt: print one member

1. On Receipt type a member ID in B4. Rows 6 to 14 use =XLOOKUP($B$4,Members!$A$5:$A$16,Members!$B$5:$B$16) and the same pattern for plan, dates, fee, paid and balance.
2. Set the print area to A1:D16. The example shows Ruhi Bagale on a Quarterly plan with ₹2,000 paid and ₹2,000 still due.

Step 5. Report: plans, trainers and the last 7 days

1. Plans in A5:A8 (=Setup!A8). B5 counts members =COUNTIF(Members!$C$5:$C$16,A5), C5 counts those not expired with COUNTIFS.
2. D5 fees billed after discount with SUMIFS, E5 collected, F5 balance =D5-E5.
3. Rows 11 to 13 repeat this per trainer, and rows 16 to 22 count check-ins per day for the last 7 days.

Step 6. Dashboard: who to call today

1. Six KPI cards in row 4: Active =COUNTIF(Members!$L$5:$L$16,"Active"), Expiring, Expired, Collected, Balance and visits per active member.
2. Chart 1 at A8: members by plan. Chart 2 at A25: check-ins over the last 7 days (dashboard charts).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Forgetting the −1 in the end date. Start + 30 gives the 31st day. Members get a free day and the Expiring list is a day late.
2. Overwriting a row on renewal. Add a new row for the new plan. Overwriting loses what was paid before and breaks the trainer report.
3. Typing names in Check-ins. Names repeat and get misspelt. Type the member ID and let XLOOKUP fill the name.
4. Leaving the as-of date fixed. If B3 stays on an old date, nobody ever shows Expired. Use =TODAY() in daily use.
5. Counting expired members as active. Use COUNTIFS with "<>Expired" or the status column, not a plain count of rows.

5. More real examples

Example 1: Yoga studio. Plans become 12-session cards. Replace days with sessions, count check-ins per card with COUNTIFS and flag "2 sessions left".

Example 2: Swimming pool. Add a Slot column (6 am, 7 am, 6 pm) on Check-ins and a slot report to see which hour is crowded.

Example 3: Personal training add-on. Add a PT fee column on Members and a trainer incentive column on Report, like the salon commission system.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate a membership end date in Excel?

Add the plan days to the start date and subtract 1: =E5+XLOOKUP(C5,Setup!$A$8:$A$11,Setup!$B$8:$B$11)-1. For calendar months use =EDATE(E5,3)-1 instead.

How do I highlight memberships expiring in 7 days?

Work out days left with =F5-Setup!$B$3, show Expiring when it is between 0 and the window, and colour that text amber with conditional formatting.

Should I charge GST on gym fees?

That depends on your registration and the current rate for your service. The template shows fees as charged; check the GST treatment with your accountant before adding a tax column.

How do I count a member's visits in the last 30 days?

COUNTIFS with two conditions: the member ID and a date later than as-of − 30, as in Members M5.

Can I use this for more than one branch?

Add a Branch column on Members and Check-ins and a branch block on Report with SUMIFS. Keep one file so the dashboard adds up.

7. Quick quiz

Ten questions on this page. Sign in to save your score.