Key Takeaway
Six columns: person and package, total including bonus, used, remaining, expires, last session.
Only the used column needs maintaining by hand. Remaining runs on a formula, and conditional formatting colors the expiry date for you.
In This Article
A spreadsheet is the first tool most people reach for, because you can add columns as you go. That is also the trap. The first version ends up with a dozen columns, and two months later nobody is filling them in. Six columns are all you need.
This piece covers why each one earns its place and how to set up the formula and the coloring, and it ends with a template you can copy. Want the wider picture first? What lesson credit management is puts five methods on one table.
The six columns, one at a time
-
Column A: Person and package
One person can hold two packages at once, so write both. "Amy Wang / Facial care 20" tells you which one at a glance.
-
Column B: Total (incl. bonus)
Buy 10 get 1 free means you write 11. Give the bonus its own column and eight times out of ten you forget to add it in.
-
Column C: Used
Add one after each session. This is the only number in the six you keep updating by hand.
-
Column D: Remaining
Let a formula subtract the two columns above, never type it. The moment you type it, the sheet starts drifting from what actually happened.
-
Column E: Expires
Pair it with conditional formatting so anything under two weeks turns red.
-
Column F: Last session
When you reconcile, count back from this column. Much quicker than reading a whole chat thread.
Two settings: subtract on its own, go red before expiry
Remaining sits in column D, and the formula is the total minus what has been used. Row 3 reads =B3-C3, and dragging it down applies it to the whole column. The official Google Sheets help page covers the basics of writing a formula.
The expiry date sits in column E. Select that column, open conditional formatting, choose custom formula, and enter =E3-TODAY()<14 with a red fill. Anything with under two weeks left now flags itself, so you never have to scan the dates from the top again. If you would rather leave rows with no expiry date alone instead of turning them red, use =AND($E3<>"", $E3-TODAY()<14).
Both settings take about five minutes. After that the only thing you touch is the used column, and every other number moves on its own.
Just copy the template
I built these six columns into a template with the formula and the expiry coloring already set. One click to copy it to your own Drive. Delete the sample rows, drop your own in, and you are running.
Building it yourself is fine too. Keep the order above, so the three numbers you check most sit on the left and you are not swiping sideways on your phone.
What changes if you build it in Notion
If Notion is already where you keep things, the same six columns work as a database. The official Notion intro to databases lists every property type.
The trade runs both ways. Notion takes longer to set up and gives you freer grouping and filtering, like one filter for everything expiring this month. But if the other person does not use Notion, sharing means asking them to pick up a new tool first. That one goes to the spreadsheet.
The one thing this sheet cannot do
It remembers numbers, not whether the two of you agreed. Who confirmed which session, and when, needs another checkbox column you tick by hand, and on a busy day it gets skipped.
Tracking your own sessions, that is not a problem. Reconciling with a coach or a shop, the confirmation trail is the part most likely to start an argument. To see whether your current setup still holds, run it past the three tests in what lesson credit management is.
FAQ
Should bonus sessions get their own column?
Will the spreadsheet warn me when a package is about to expire?
If two of us share one sheet, can my numbers get overwritten?
We turn the course-tracking questions people ask us into articles. Course Kit is an app for tracking prepaid session packages, where the manager and the member open it to the same numbers.
