How do you build a subscription tracker in a spreadsheet?
The eight columns worth having, the formulas for normalised monthly cost and next renewal, and the one thing a spreadsheet fundamentally cannot do.
Answer
Eight columns: Name, Amount, Currency, Cycle, Every, Next Renewal, Status, Category. Derive monthly cost with a formula rather than typing it, and derive the next renewal with EDATE rather than maintaining it by hand. The limit is structural — a spreadsheet cannot notify you, so the renewal you needed warning about arrives unannounced.
Leutrim Miftaraj
Founder, SubTracker · Updated September 5, 2026
The short answer
A spreadsheet is genuinely the right tool for a stable list of under fifteen subscriptions. It stops being the right tool at the point where you need to be told something before it happens, because that is the one thing it cannot do.
The Columns
Eight. Adding more is how a tracker becomes a chore and stops being updated.
| Column | Type | Note |
|---|---|---|
| Name | Text | Provider as it appears on the statement, which is not always the brand |
| Amount | Number | In the currency charged. Format as number, not currency, so formulas behave |
| Currency | Text | Three-letter code. Skip only if genuinely everything is one currency |
| Cycle | Dropdown | Day / Week / Month / Year / Once |
| Every | Number | Multiplier. 1 for monthly, 3 for quarterly, 18 for every 18 months |
| Next Renewal | Date | Derived, not typed — see below |
| Status | Dropdown | Active / Paused / Cancelled / Trial |
| Category | Dropdown | Six to eight values, defined once |
Cycle plus Every rather than a single "Frequency" column. Same reasoning as in a database: "Quarterly" as a text value cannot be arithmetic on, and the moment you have something billed every two months the single column has no answer. Two columns make every downstream formula a one-liner.
Data validation on Cycle, Status and Category. Two minutes of setup that prevents "Streaming", "streaming" and "Streaming " from becoming three categories in the pivot table. This is the single highest-value thing you can do to a subscription spreadsheet.
The Formulas
Assume row 2, columns A–H in the order above.
Normalised monthly cost. The figure that makes an annual plan comparable to a monthly one:
=IF(D2="Once", 0,
IF(D2="Year", B2/(12*E2),
IF(D2="Month", B2/E2,
IF(D2="Week", B2*52/(12*E2),
IF(D2="Day", B2*365/(12*E2), 0)))))`Once` normalising to zero is deliberate. A one-time payment is not recurring spend, and including it produces a monthly figure the user cannot reconcile.
Next renewal from a start date. Keep the original start date in a column and derive forward, rather than editing the renewal date every month:
=IF(D2="Month", EDATE(I2, E2*CEILING(DATEDIF(I2,TODAY(),"M")/E2 + 0.0001)),
IF(D2="Year", EDATE(I2, 12*E2*CEILING(DATEDIF(I2,TODAY(),"Y")/E2 + 0.0001)),
I2))Slightly ugly, and it means the renewal date is never stale. A hand-maintained date column is wrong within two months, and a tracker with wrong dates is worse than none because it is trusted.
Annual total, active only:
=SUMPRODUCT((G2:G100="Active")*(J2:J100))*12where column J holds the normalised monthly figure.
Days until renewal, with conditional formatting. `=F2-TODAY()` and a rule highlighting anything under 7. This is the closest a spreadsheet gets to a reminder, and it only works on days you happen to open the file.
Multi-Currency Without Making It Wrong
Add a small rates block on a second sheet — currency code and rate to your display currency — and reference it:
=J2 * VLOOKUP(C2, Rates!A:B, 2, FALSE)Two rules keep this honest. Update the rates block by hand and date it, so anyone reading the total knows how old the conversion is. And never overwrite column B with a converted figure — the amount you are charged is the fact, the conversion is a view of it. Overwriting means the original is gone and the number drifts silently as rates move.
Google Sheets has `GOOGLEFINANCE` for live rates, which is convenient and makes historical totals change on their own. Whether that is a feature depends on whether you are looking at what you spent or what it would cost today.
What a Spreadsheet Cannot Do
Worth being precise about, because everything above works well.
It cannot notify you. This is the whole limitation and it is not a small one. The purpose of tracking subscriptions is to act before a renewal, and a spreadsheet requires you to open it on the right day. The annual $140 charge you needed thirty days' warning about arrives on schedule and the file was last opened in March.
It cannot be shared without becoming stale. Two people editing one sheet works until one of them stops, which happens at about week six.
It has no history. Overwrite an amount when a price rises and the previous figure is gone. You lose the ability to notice the increase at all, which is one of the more valuable things a tracker does.
It does not survive its author. A spreadsheet in one person's Drive is a single point of failure for the household's record of what it pays for.
The partial workaround for the first point: create calendar events for renewal dates with alerts at the right lead time — seven days for monthly, thirty for annual. It works, it survives you changing tools, and it is twenty manual events to create and maintain, which is where most people stop.
When to Stop Using One
Three signals, and any one of them is sufficient.
More than about fifteen subscriptions. The maintenance cost of hand-editing dates crosses the value of the tool.
More than one person needs it. Shared editing degrades quickly and there is no notion of who owns what.
You have already missed a renewal you meant to cancel. This is the decisive one. The spreadsheet did its job — the row was there and correct — and it still cost you money, because the job it cannot do is the one that mattered.
If none of those apply, a spreadsheet is a genuinely good answer and you should not be talked out of it.
What SubTracker Does With This
For context, since the decisions above come from a running system rather than from a design exercise.
SubTracker is a Next.js and PostgreSQL application (Supabase) that tracks subscriptions manually — there is no bank connection, so every row originates from a form or a CSV import. That constraint shapes the schema: there is no "detected" state, no confidence score, no merchant-matching table. Every row is an assertion the user made.
The free plan tracks unlimited subscriptions and includes CSV import and a live calendar feed. Reminders and price-hike alerts are Plus, at $5.90/month. Family is $9.90/month and adds a shared workspace for up to ten people, which is where the ownership columns below stop being theoretical.
Frequently asked questions
What columns should a subscription tracker spreadsheet have?+
Name, Amount, Currency, Cycle, Every, Next Renewal, Status and Category. Splitting the billing interval into a unit and a multiplier is what allows every other formula to be arithmetic rather than a lookup, and it handles quarterly or every-eighteen-months without special cases.
How do you calculate monthly cost for an annual subscription in Excel?+
Divide the amount by twelve times the multiplier, and treat one-time payments as zero rather than dividing them. Deriving the figure with a formula rather than typing it keeps the total correct when an amount is edited.
Can a spreadsheet remind you before a subscription renews?+
Not on its own. Conditional formatting highlights upcoming renewals only when you open the file, which is not the day you need it. Creating calendar events with the right lead time is the usual workaround, at the cost of maintaining them by hand.
Is a spreadsheet good enough for tracking subscriptions?+
For a stable list of under about fifteen, kept by one person, yes. It stops being sufficient when the list grows, when a second person needs it, or the first time a renewal you meant to cancel goes through despite being correctly recorded.