Spreadsheets

Subscription tracker in Google Sheets with renewal alerts

Build a subscription tracker in Google Sheets that turns yearly plans into monthly cost and highlights renewals coming up in the next seven days.

Editorial illustration for Subscription tracker in Google Sheets with renewal alerts

A subscription tracker in Google Sheets is a simple table with eight columns: service, plan, billing cycle, amount, next renewal date, payment method, keep or cancel, and monthly cost. One formula converts yearly and quarterly plans into a comparable monthly number, and one conditional-formatting rule colours any renewal due in the next seven days.

Which columns do I need?

  • A: Service (video app, music, cloud storage, magazine)
  • B: Plan name
  • C: Billing cycle - Monthly, Quarterly or Yearly, from a dropdown
  • D: Amount charged each cycle, in rupees
  • E: Next renewal date
  • F: Payment method, such as UPI autopay, a particular card or wallet
  • G: Decision - Keep or Cancel
  • H: Monthly cost, calculated

Add the dropdowns through Data, then Data validation. For C, use a list of Monthly, Quarterly, Yearly; for G use Keep, Cancel. Format column E as a date and keep each entry exact, since the colour rule depends on it.

How do I convert yearly plans to a monthly cost?

Put this in H2 and fill it down:

=IF(C2="Yearly", D2/12, IF(C2="Quarterly", D2/3, D2))

Google Sheets also has IFS, so =D2/IFS(C2="Monthly",1, C2="Quarterly",3, C2="Yearly",12) does the same. The advantage is you can see every divisor in one place. Below the table, total it up with =SUM(H2:H100) for the monthly burden and =SUM(H2:H100)*12 for a year. To see what cancelling would save, use =SUMIFS($H$2:$H$100, $G$2:$G$100, "Cancel").

How do I highlight renewals in the next seven days?

  1. Select E2:E100.
  2. Open Format, then Conditional formatting.
  3. Under Format cells if, choose Custom formula is.
  4. Enter =AND($E2>=TODAY(), $E2<=TODAY()+7) and pick an amber fill.
  5. Add a second rule with =$E2<TODAY() and a red fill, so any date you forgot to roll forward stands out.

After a renewal goes through, advance the date. For a monthly plan, =EDATE(E2,1) gives the same day next month; for yearly use =EDATE(E2,12). Type the result in as a plain date rather than leaving the formula in E2, otherwise the formula will point at itself.

A worked example with made-up plans

ServiceCycleAmountMonthly cost
Video appMonthly₹299₹299
Music appYearly₹1,188₹99
Cloud storageMonthly₹130₹130
MagazineQuarterly₹600₹200
Gym appYearly₹3,600₹300

The monthly column adds to 299 + 99 + 130 + 200 + 300 = ₹1,028, and a year of that is ₹12,336. Notice that the gym app, which feels like a single yearly bill, is the biggest line once spread out. Marking it Cancel in column G would show up immediately in the SUMIFS saving.

Where do I find subscriptions I have forgotten?

Open the last two or three statements for the account or card you use online and scan for repeated merchant names at the same amount. If you import statements into SpendixAI, the charges are grouped by merchant and category, which makes a forgotten monthly debit easier to spot before you add it to the sheet.

What should I do with the Cancel column?

Mark a service Cancel when you cannot remember the last time you used it, or when a cheaper plan or a shared plan would do. Then set a calendar reminder for the day before its next renewal, because cancelling after the charge has gone through usually means paying for another full cycle. Yearly plans deserve the most attention: the money leaves in one go, and the reminder email is easy to miss.

Review habit

  • Once a month, set each row to Keep or Cancel and be honest about the last time you used it.
  • Before a free trial ends, put the end date in column E and write Trial in column B.
  • Check that the amount in column D still matches what was charged, since prices change.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports