Short answer: two tabs — a member list and a daily check-in log, tied together by a member ID — plus three formulas that tell you who has gone quiet. Below is the layout filled in with real rows, not just column headers with nothing under them.
I want to say upfront that this genuinely works. A lot of "why you need software" posts treat a spreadsheet as a strawman. It isn't one. What follows is a sheet you can build in twenty minutes and use for real, plus the honest point where it starts costing you more than it saves.
What should a gym attendance sheet actually track?
Most attendance sheets fail for one reason: they only have a name and a date. That tells you someone came, and nothing else. The moment you want to know how often, or who has stopped coming, you're back to reading the whole sheet by eye.
Six columns fix that, and none of them are optional.
- Date — the day of the visit, always the same format (DD-MM-YYYY), never typed freehand or you'll end up with three different formats by March
- Member ID — a permanent code (M-001, M-002…) that never gets reused, even after someone leaves. This is what makes the formulas below work
- Name — for the humans reading the sheet. The ID is for the formulas
- Time in — even a rough time helps you see your real rush hours later
- Marked by — whoever was at the desk, useful when a row looks wrong and you need to ask someone
- Notes — mostly blank, but where "phone forgotten, marked by name" or "guest of M-014" lives
Daily log or monthly grid — which layout fits your gym?
There are two honest ways to lay this out, and most gyms that build their own sheet pick the wrong one first.
| Layout | How it works | Best for | Where it struggles |
|---|---|---|---|
| Daily log | One row per visit — a member who comes 20 times a month has 20 rows | Any gym that wants real formulas. COUNTIFS and MAXIFS both need this shape | The sheet gets long fast — a 150-member gym adds 300–400 rows a month |
| Monthly grid | One row per member, one column per day, a tick or a time in each cell | A quick visual — the whole month's pattern for one member at a glance | Formulas across 30 columns are clumsy, and it resets every month, so trends across months disappear |
Use the daily log if you want the formulas below. Use the grid only if all you need is a wall chart nobody calculates anything from.
The rest of this post builds the daily log, because that's the version that earns its keep. Here's what it looks like filled in — five real rows from a fictional gym in Nagpur, on a normal Tuesday.
The template, filled in
| Date | Member ID | Name | Time in | Marked by | Notes |
|---|---|---|---|---|---|
| 12-08-2026 | M-014 | Rohit Kale | 06:42 | Sneha | |
| 12-08-2026 | M-031 | Ayesha Sheikh | 07:15 | Sneha | |
| 12-08-2026 | M-009 | Vivek Rao | 18:05 | Sneha | Phone forgotten, marked by name |
| 12-08-2026 | M-055 | Priya Nair | 18:20 | Sneha | Guest: sister, one-time |
| 12-08-2026 | M-002 | Arjun Deshpande | 19:02 | Manoj |
Powerline Fitness, Nagpur. Copy the header row into your own sheet and start adding rows from tomorrow morning — you don't need last month's data to make this useful.
Setting it up in the next twenty minutes
- Open a new Google Sheet and name it something you'll actually find later — "Gym Attendance" beats "Sheet14"
- Tab 1, Members — Member ID, Name, Phone, Plan (skip this tab if you already keep a member list elsewhere)
- Tab 2, Log — the six columns above, starting from column A
- Tab 3, Summary — one row per member ID, with the three formulas below sitting next to each one
- Freeze the header row on the Log tab (View → Freeze → 1 row) so it doesn't scroll away as the list grows
- Bookmark it on the front-desk phone or tablet so opening it is one tap, not a search
The three formulas that make it useful
A log with nothing calculated off it is just a longer version of the paper register. These three formulas, sitting on the Summary tab and reading from your Log tab, are what turn it into something you'd actually check. Assume the Log tab has Date in column A and Member ID in column B.
- Visits this month for one member: =COUNTIFS(Log!B:B,"M-014",Log!A:A,">="&DATE(2026,8,1))
- Days since their last visit: =TODAY()-MAXIFS(Log!A:A,Log!B:B,"M-014")
- A flag once it passes 10 days: =IF(TODAY()-MAXIFS(Log!A:A,Log!B:B,"M-014")>10,"Check in on them","")
Put those three next to every member ID on your Members tab — the setup in our free spreadsheet guide works well as the base, and this attendance log is the piece that guide doesn't cover — and you have a quiet list without ever opening the log itself. Filter the flag column for anything that isn't blank once a week, and that's your call list. Not a sales pitch, just a two-line WhatsApp asking if everything's alright.
Excel or Google Sheets?
Use Google Sheets. The formulas above work identically in both, so this isn't a functionality question. It's that Excel on a desktop means one file, on one machine, that someone has to remember to save and back up — and gym attendance gets marked from wherever the desk is, often a phone. Google Sheets autosaves, opens on the phone your staff already carry, and there's exactly one copy of it, rather than three files called attendance_final.xlsx.
If Excel is what you searched to find this post, that's fine — everything above works saved as a real .xlsx file too. Just make sure it lives in one place everyone at the desk can reach, not on whichever laptop happened to be open that day.
Where this stops being enough
I'd rather tell you this now than have you find out in month four. A spreadsheet has one structural weakness: it only works if a human remembers to type into it, every visit, during the exact ten minutes your desk is busiest.
In practice that means gaps. Someone gets missed at 7pm when four members walk in at once. A staff member fills in "yesterday's visits" from memory the next morning, and memory is wrong more often than anyone likes to admit. And the flag formula above only helps if someone actually opens the sheet and looks — it doesn't tell you anything on its own, it waits to be asked.
Below roughly 60–80 members, that's a manageable trade for something that costs nothing. Past that, the time it takes to log every visit properly starts eating a real chunk of someone's day, and the gaps get more expensive because you can't eyeball a 300-row month the way you can a 40-row one. That's the point where a QR code at the door starts paying for itself — the member scans, the row writes itself, and the quiet list builds automatically instead of waiting for someone to filter a column.
Common questions
What should a gym attendance register include?
At minimum: date, a permanent member ID, name, and time in. The member ID is the column people skip and the one that matters most — without it, you can't reliably count visits or find someone's last visit date, because names get misspelled and duplicated in a way IDs don't.
Can I use Google Sheets instead of Excel for gym attendance?
Yes, and it's genuinely the better choice for a gym. The same formulas work in both. Google Sheets autosaves, opens on a phone at the front desk, and keeps one single copy instead of several versions of the same file floating around on different computers.
How long should a gym keep attendance records?
There's no legal minimum for a private gym in India, but keeping at least 12 months is worth it for two reasons: you can show a member their own visit history if they dispute a freeze or a renewal, and you can compare this month against the same month last year instead of guessing whether a slow August is normal.
Is a manual attendance sheet enough for a small gym?
For a gym under about 60–80 members with one or two people at the desk, yes — a well-built sheet like the one above will genuinely do the job. Past that member count, or once more than one or two staff are marking attendance, gaps and duplicate entries start creeping in faster than anyone can catch them by eye.
How do I find members who haven't visited recently in Excel or Sheets?
Use MAXIFS to find each member's last visit date from the log, then subtract it from TODAY() to get days since their last visit. Sort or filter that column, and anything above 10–14 days is your list to reach out to — the exact formula is in the template above.
One honest note before you go build this: the sheet only does its job if somebody actually opens the summary tab. Set a recurring ten-minute slot — Monday morning is fine — to filter the flag column and send two or three messages. That habit is worth more than any formula in it.