Two sheets, not one
The most common mistake is one big sheet with a column per month. It gets wider every month, and nobody can tell what a member owes without scrolling. Use two sheets instead:
- Members: one row per member, the facts that rarely change.
- Payments: one row per invoice, added every month.
The Members sheet
| Column | Example | Why |
|---|---|---|
| Member ID | M-014 | Names repeat and change; IDs don't |
| Company / name | Rao Design Studio | As it should appear on invoices |
| Contact, phone, email | Priya, 98xxxxxx01, priya@… | Who gets the invoice |
| Space | Cabin 104 / Desk D-03 / Virtual office | What they rent |
| Seats | 4 | For occupancy |
| Monthly rent | 24,000 | Before tax |
| Deposit held | 48,000 | What you owe back on exit |
| Start date | 01-03-2026 | Billing and rent revisions |
| Agreement end | 31-01-2027 | Renewal reminders |
| Next rent revision | 01-03-2027 | Usually yearly |
| Status | Active / Notice / Left | Filter the active ones |
The Payments sheet
| Column | Example |
|---|---|
| Month | 2026-10 |
| Member ID | M-014 |
| Invoice no. | SPL/26-27/0142 |
| Amount due | 24,000 |
| Amount paid | 24,000 |
| Paid on | 04-10-2026 |
| Mode and reference | UPI · 4291xxxx1123 |
| Balance | formula |
The formulas that matter
Write months as text like 2026-10 so they sort correctly. Then:
- Balance on a row:
=D2-E2(due minus paid). - What a member owes in total, on the Members sheet:
=SUMIF(Payments!B:B, A2, Payments!H:H). - Rent collected this month:
=SUMIF(Payments!A:A, "2026-10", Payments!E:E). - Collection rate: collected ÷
=SUMIF(Payments!A:A, "2026-10", Payments!D:D). - Agreements ending in the next 60 days: filter Agreement end between
=TODAY()and=TODAY()+60, or add a helper column=IF(AND(I2>=TODAY(), I2<=TODAY()+60), "Renew", "").
Add conditional formatting so any balance above zero after the 7th turns amber. That one colour does more for collections than any other formula.
Habits that keep the sheet honest
- One person edits it. Everyone else reads.
- Record a payment the day it lands, with the bank or UPI reference, not at the end of the month.
- Never delete a row. Mark a member as Left and keep their history.
- Keep the invoice PDFs in one folder named by invoice number.
Signs it has outgrown you
- You spend more than half a day on the 1st making and sending invoices.
- Members pay and you can't tell who, because the UPI credit only shows a phone number.
- Two people keep their own copies and the numbers don't match.
- A renewal or a rent revision was missed because nobody looked at the date.
- You can't answer "how full are we next month?" without counting by hand.
That usually happens somewhere between 25 and 40 members, or as soon as you add a second floor.
Moving from Excel without losing anything
If you set the sheet up as above, moving is quick. Coworking OS reads your Excel or Google Sheets export, matches the columns for you (name, space, rent, deposit, dates), shows you what it found before anything is saved, and lets you undo the import. Each member's unpaid balance comes across as opening dues, so nothing owed is lost. After that, invoices, reminders, receipts and renewal alerts run on their dates, and the dashboard shows what used to take the formulas above. You can try it free for 30 days with your own sheet.