Richard C

Posted on Oct 26 Edited on Oct 28 AI-assisted

How to find every subscription you're paying for in 15 minutes (a simple Google Sheets audit)

#googlesheets #productivity #personalfinance #spreadsheets

The hard part of a subscription audit isn't the math. It's finding the charges. Small recurring payments are spread across cards, app stores and PayPal, and some bill only once a year. This is a 15-minute hunt to list every one of them, then a few formulas to see what they add up to.

Set a timer and work through the steps in order.

Minutes 0–3: gather your statements

Download or open the last 2–3 months of statements for every bank account and credit or debit card you use. One month isn't enough, because quarterly and weekly charges are easy to miss in a single month.

If you can, also skim your card's transactions from the last 12 months for anything with "annual" or a round yearly amount. Yearly renewals are the ones people forget most.

Minutes 3–8: search for recurring merchants

Rather than reading every line, search each statement (Ctrl+F / Cmd+F in the PDF or online banking) for words that usually show up on subscription charges:

subscription, membership, monthly, annual, renew
.com, digital, premium, plus, pro
the same merchant name appearing in more than one month

Any merchant that appears at the same amount every month (or every week) goes on your list. Note the amount exactly as charged and how often it repeats.

Minutes 8–12: check the places statements hide

Some subscriptions show up on your statement under a single umbrella name, so check the source too:

Apple: Settings → your name → Subscriptions.
Google Play: Play Store → profile → Payments & subscriptions → Subscriptions.
PayPal: Settings → Payments → Manage automatic payments.
Email: search your inbox for receipt, your subscription, renewal, free trial and invoice. This catches trials that turned into paid plans.

Add anything new to the list.

Minutes 12–15: log it and do the math

Open a blank Google Sheet and put these headers in row 1:

A	B	C	D	E
Name	Amount	Cycle	Monthly cost	Keep/Cancel

Example data (made-up subscriptions for illustration, not real charges):

Row	A: Name	B: Amount	C: Cycle	E: Keep/Cancel
2	Streaming service	15.49	Monthly	Keep
3	Music app	10.99	Monthly	Keep
4	Cloud storage	29.99	Yearly	Keep
5	Fitness app	4.99	Weekly	Cancel
6	News site	79.00	Yearly	Cancel
7	Meal kit (after trial)	12.00	Monthly	Cancel

Turn every charge into a monthly cost (column D)

D2: =IF(C2="Yearly",B2/12,IF(C2="Weekly",B2*52/12,B2))      (fill down to D7)

Yearly charges are divided by 12, weekly charges are multiplied by 52 and divided by 12, and anything else is treated as monthly. With the example data, column D reads:

Row	Monthly cost
2	15.49
3	10.99
4	2.50 (29.99 / 12)
5	21.62 (4.99 × 52 / 12)
6	6.58 (79 / 12)
7	12.00

Totals

D8:  =SUM(D2:D7)                          → 69.19   total per month
D9:  =SUMIF(E2:E7,"Cancel",D2:D7)         → 40.21   saved per month if you cancel
D10: =D9*12                               → 482.48  saved per year

Notice the weekly fitness app: $4.99 looks small, but it works out to about $21.62 a month. Converting everything to one cycle is what makes charges like that stand out.

After the 15 minutes

Cancelling is a separate job. For each row marked Cancel, cancel through the same place you found it (the app store, PayPal or the merchant's account page), then check your next statement to confirm the charge stopped. Your actual savings depend on what you cancel and when each plan renews.

Get Subscription Audit Lite: a free ($0+) .xlsx audit sheet for Google Sheets or Excel. It has 30 rows, Monthly/Yearly cycles, Keep/Cancel/Review dropdowns, and totals for monthly cost, yearly cost and potential yearly savings.

By Richard C · AIL Studio

Related from AIL Studio

DEV Community