DEV Community

Richard C
Richard C

Posted on

How to catch free trials before they turn into paid subscriptions (Google Sheets)

#googlesheets #productivity #spreadsheets #tutorial

By Richard C · AIL Studio

Most free trials ask for a card up front. If you forget the end date, the first charge arrives quietly. This post builds a small Google Sheets list that shows how many days each trial has left, turns the row amber and then red as the date gets close, and gives you a simple reminder routine.

It takes about 15 minutes. No scripts or add-ons. It works the same way in Excel (the SORT and FILTER formulas in section 5 need Excel 365 or Excel 2021).

1. The columns

Make a new sheet and put these headers in row 1:

A: Service   B: Started   C: Trial days   D: Ends on   E: Days left   F: Price after trial   G: Decision   H: Cancel link / notes
Enter fullscreen mode Exit fullscreen mode

You type columns A, B, C, F, G and H. Columns D and E are formulas.

Example data (made-up services and prices, for illustration only). Assume today is 2026-10-27:

Row  A: Service           B: Started   C: Trial days  F: Price after trial  G: Decision
2    Example Video App    2026-10-01   30             12.99                 Cancel
3    Example Notes Pro    2026-10-20   14             8.00                  Undecided
4    Example Music Plus   2026-10-24   7              10.99                 Keep
5    Example Cloud 2TB    2026-09-28   30             9.99                  Cancel
Enter fullscreen mode Exit fullscreen mode

Tip: when you sign up, copy the end date from the confirmation email if the service shows one. Some trials end on a fixed date rather than after a set number of days.

2. End date 

D2: =IF(OR(B2="",C2=""),"",B2+C2)
Enter fullscreen mode Exit fullscreen mode

Fill it down. Format column D as a date. For row 2 this gives 2026-10-31.

If the service told you an exact end date, type it straight into D and leave C empty. The formula is only a fallback.

3. Days left 

E2: =IF(D2="","",D2-TODAY())
Enter fullscreen mode Exit fullscreen mode

Format column E as a plain number, not a date. TODAY() updates every time the sheet opens, so the count goes down by itself.

With the example data on 2026-10-27:

Service             Ends on      Days left
Example Video App   2026-10-31   4
Example Notes Pro   2026-11-03   7
Example Music Plus  2026-10-31   4
Example Cloud 2TB   2026-10-28   1
Enter fullscreen mode Exit fullscreen mode

A negative number means the trial has already ended, so you have probably been charged.

4. Conditional formatting

Select E2:E100, then Format → Conditional formatting. Add three rules in this order:

Rule 1  Custom formula:  =AND(E2<>"",E2<0)     Fill: grey      (already ended)
Rule 2  Custom formula:  =AND(E2<>"",E2<=2)    Fill: red       (cancel today)
Rule 3  Custom formula:  =AND(E2<>"",E2<=7)    Fill: amber     (decide this week)
Enter fullscreen mode Exit fullscreen mode

Google Sheets applies the first rule that matches, so the order matters. The E2<>"" part stops empty rows from turning red.

Optional: to colour the whole row, select A2:H100 and use $E2 instead of E2 in each rule.

5. Only show trials you still need to act on

On a second tab, this pulls the trials that end within 7 days and are not marked "Keep":

A1: =SORT(FILTER(Sheet1!A2:G100, Sheet1!E2:E100<>"", Sheet1!E2:E100>=0, Sheet1!E2:E100<=7, Sheet1!G2:G100<>"Keep"), 5, TRUE)
Enter fullscreen mode Exit fullscreen mode

With the example data it lists Example Cloud 2TB (1 day), Example Video App (4 days) and Example Notes Pro (7 days), soonest first. If nothing matches, FILTER shows #N/A. Wrap it in IFERROR(..., "Nothing due this week") if you prefer a message.

6. What the trials would cost 

I1: Monthly cost if nothing is cancelled
I2: =SUM(F2:F100)
I3: Monthly cost after your planned cancels
I4: =SUMIF(G2:G100,"<>Cancel",F2:F100)
Enter fullscreen mode Exit fullscreen mode

With the example data, if nothing is cancelled that is 12.99 + 8.00 + 10.99 + 9.99 = 41.97 a month, or about 504 a year. After your planned cancels it is 8.00 + 10.99 = 18.99 a month, or about 228 a year. Seeing that number is often what gets the cancel done.

7. A reminder routine that actually fires

A sheet only helps if you open it. Two simple options:

  • Calendar event on sign-up. When you start a trial, add an all-day event two days before the end date, with the cancel link in the description. This is the most reliable option.
  • Weekly check. Pick one time a week (Sunday evening works) and open the second tab. Anything listed there needs a decision before next week.

Google Sheets also has Tools → Notification settings, but that tells you when the sheet is edited, not when a date gets close. Don't rely on it for trial reminders.

Honest limits

  • The days-left count is only as good as the end date you enter. Check the service's own account page if you are unsure.
  • Some services charge at the start of the last day, not the end. Cancelling with 2 days to spare is safer.
  • The sheet doesn't cancel anything. It just shows you what to do and when.

Wrap-up

That's the whole system: nine columns, five formulas, three colour rules and a weekly look. Swap the example rows for your own trials and you'll see them coming.

If you'd rather start from a finished file, the Subscription Tracker is a Google Sheets / Excel template (name your price, from $1). It has 100 rows, six billing cycles, a next renewal date with a days-left countdown for each subscription, and a dashboard with monthly and yearly totals, the renewals due in the next 7 and 30 days, and a spending-by-category chart.

By Richard C · AIL Studio

Top comments (0)