Gym Attendance Sheet in Excel: Free Layout and Formulas

A workable gym attendance sheet in Excel or Google Sheets has three tabs: a Members sheet (one row per member with plan, start date and an end date from =EDATE(start,months)-1), a Log sheet (one row per visit: date, member ID, time), and a monthly grid that marks who came on which day using COUNTIFS. Add conditional formatting to colour expired and expiring members, and you have a free system that runs well for a small gym, as long as someone types every visit.

Below is the exact layout, the formulas, and the point where a sheet starts costing you more than it saves. The function behaviour is from Microsoft and Google's own help pages, linked as we go.

The three-tab layout

Use a short member ID (M001, M002…) as the link between tabs. Names get misspelt; IDs don't.

Tab 1: Members

Put headings in row 1 and the first member in row 2. In cell N1, put the first day of the current month: =DATE(YEAR(TODAY()),MONTH(TODAY()),1).

Fill row 2's formulas down the column. A few notes on why they're written this way:

• End date. EDATE adds whole months to a date and returns a serial number, so format column G as a date. A non-integer month count is truncated, so 1.5 months becomes 1. The -1 makes a plan starting 5 October end on 4 November, not 5 November. If your gym counts the end date differently, drop the -1. Google Sheets' EDATE behaves the same way.
• Visits this month. COUNTIFS counts rows that meet every condition: this member's ID, a date on or after the 1st, and before the 1st of next month. Criteria with a cell reference are joined with &, like ">="&$N$1. Every range must be the same size, which is why all three say 2 to 20000. The Google Sheets version has the same rule.
• Last visit. MAXIFS returns the latest date for that ID. It needs Excel 2019 or later (or Microsoft 365), and it returns 0 if the member has never visited, hence the IF(K2=0,...) in column L. Google Sheets also returns 0 when nothing matches.

Use fixed ranges like $A$2:$A$20000 rather than whole columns. When the log approaches row 20,000, raise the number in every formula.

Tab 2: Log

Three columns, nothing else:

Rules that keep it clean:

1. One row per visit. Never edit old rows to "update" a member.
2. Dates as real dates, not text like "5 Oct". Format column A as a date.
3. Member ID only; let the Members tab hold names. If staff want to see the name, add a lookup in column D, but don't type names.
4. Type the time when the member walks in. A time typed at the end of the shift makes column C useless.

Tab 3: Monthly grid

This recreates the familiar paper register, built from the log instead of typed twice.

• A2 down: member IDs (=Members!A2, filled down). B2 down: names (=Members!B2).
• C1: the first of the month, for example =DATE(2026,10,1). D1: =C1+1, filled right to AG1 for 31 days.
• C2: =IF(COUNTIFS(Log!$B$2:$B$20000,$A2,Log!$A$2:$A$20000,C$1)>0,1,""), filled across and down.
• AH2: =SUM(C2:AG2) for each member's visit count.
• Below the last member, a daily total: =COUNTIFS(Log!$A$2:$A$20000,C$1), filled right.

The $ signs matter: $A2 keeps the ID column fixed as you fill right, and C$1 keeps the date row fixed as you fill down. For a new month, change C1 and the whole grid redraws.

Colour the members who need a call

Conditional formatting turns the Members tab into a to-do list.

In Excel: select A2:L500, go to Home > Conditional Formatting > New Rule, and choose "Use a formula to determine which cells to format". Microsoft's guide notes the formula must start with = and return TRUE or FALSE.

In Google Sheets: select the range, go to Format > Conditional formatting, and pick "Custom formula is" (Google's guide).

Add three rules:

Each formula is written for row 2 with $ before the column letter, so the whole row colours. Pick your own inactivity threshold; 10 days is only an example.

One catch. Google's help page says conditional formatting formulas can only refer to cells on the same sheet, and you need INDIRECT to reach another. That's why days left (H) and days since visit (L) live on the Members tab: the rules only read from that tab.

Sort or filter by column I each morning, and the amber rows are your renewal calls for the day. For what to say, see membership renewal reminders.

What the sheet tells you each month

• Active members: =COUNTIFS(Members!I2:I500,"Active")+COUNTIFS(Members!I2:I500,"Expiring")
• Expired this month, not yet renewed: =COUNTIFS(Members!G2:G500,">="&Members!N1,Members!G2:G500,"<"&EDATE(Members!N1,1),Members!H2:H500,"<0") (when a member renews, update their start date, so they drop out of this count)
• Average visits per active member: sum of column J for active rows ÷ active members

Say 20 of your 200 members expire and don't renew in a month: that's 10% churn. Run your own numbers through the gym churn calculator to see what it costs in a year.

Where a spreadsheet breaks down

File size is not the problem. An Excel worksheet holds 1,048,576 rows, and Google Sheets allows up to 20 million cells per file. A hypothetical gym with 150 check-ins a day adds about 4,500 log rows a month and under 55,000 a year.

The problems are human:

• Every visit is typed. At 6:30 pm with ten people at the desk, typing IDs creates a queue, and the person typing skips some. Missing rows make your inactive list wrong in both directions.
• Nobody is checking the door. A sheet records whoever staff type in. It doesn't stop an expired member walking past.
• Versions drift. A file emailed or sent on WhatsApp becomes three files. Use one shared Google Sheet, or one laptop, never copies.
• It doesn't send anything. The amber rows only help if someone opens the sheet and messages them.
• It holds personal data. Names and mobile numbers in a shared file are member data you're responsible for; restrict who has edit access.

A good rule of thumb: once typing check-ins takes more than a few minutes in your busiest hour, or the inactive list stops matching what you see on the floor, the log is the part to automate. Paper register vs Excel vs software compares the options, and how QR code check-in works explains the most common next step, where members scan in and the log fills itself. MyGymGate does that with any Android phone as the scanner, and produces the same expiring and inactive lists, for ₹399 a month.

Quick checklist

• Three tabs: Members, Log, Grid
• Member IDs link every tab; names typed only once
• End date =EDATE(start,months)-1, formatted as a date
• COUNTIFS and MAXIFS with fixed, equal-size ranges
• Days left and days since visit on the Members tab (for Google Sheets formatting)
• Red, amber and grey rules on the member rows
• Log typed at entry, with real dates and times
• One shared file, edit access for staff who need it only
• Check amber rows every morning

Useful links

• COUNTIFS function (Microsoft) and COUNTIFS (Google Sheets)
• EDATE function (Microsoft) and EDATE (Google Sheets)
• MAXIFS function (Microsoft)
• Conditional formatting in Excel (Microsoft)
• Conditional formatting in Google Sheets (Google)
• Excel specifications and limits (Microsoft)