I Track My Subscriptions With Google Sheets—Here’s How

With the rise in subscription-based software and business models, it is important to have a system for managing and tracking the services you pay for. Fortunately, Google Sheets is the only tool you need to log and manage these subscriptions.

Why I Use Google Sheets for Tracking Subscriptions

There are several ways to track your monthly subscriptions, including mobile app stores (Google Play or App Store) and paid subscription tracker apps. However, app stores can only track payments made within the platform, and paid tracker apps are usually just another subscription to your monthly bill.

Google Sheets, on the other hand, is completely free, lightweight, extensible, and cross-platform. It is also easy to use and supports formulas, filters, and conditional formatting for summing up costs, highlighting upcoming renewals, and organizing subscriptions by category. It supports collaboration, which can be helpful for couples and family members, and spreadsheets can be easily shared or exported in various formats.

How to Set Up a Subscription Tracker in Google Sheets

Creating a subscription tracker is not as easy as installing an app and entering your current subscriptions. It does take some time, especially if you are not working with a template.

For this reason, I have created a subscription tracker spreadsheet with column headers for the name of the subscription, the category it belongs to, the billing frequency and amount charged, the date subscribed, monthly cost, annual cost, lifetime cost, and action to take (e.g. cancel or renew).

The Category and Frequency columns have dropdown fields for subscription information. Formulas based on frequency, amount, and the date the payments started automatically calculate the subscription’s monthly, annual, and lifetime costs.

The formula for the monthly cost calculation is below:

        =IFS(C2="Annually",F2/12,C2 = "Weekly", F2*4, C2 = "Monthly", F2)
    

The monthly cost is calculated based on the specified frequency of the subscription. I either divide the billed amount by 12, multiply it by 4, or insert it as is into the cell.

For the annual cost of the subscription, I multiply the monthly cost by 12. To calculate the lifetime cost, I use a formula that finds the number of weeks, months, or years since the first payment and multiplies it with the billed amount:

        =IFS(C2 = "Weekly", F2 * INT((TODAY()-D2)/7), C2 = "Monthly", (DATEDIF(D2,TODAY(), "m") * F2), C2 = "Annually", (H2 * DATEDIF(D2, TODAY(),"y")))
    

The final column, Action, is where you can specify whether to renew, cancel, or even upgrade the subscription

Getting More Out of Your Subscription Tracker

If you only have a few subscriptions to track and all you need is a place to lay them out and view them all at once, the above template will work nicely. But to get more out of your subscription tracker, you will need advanced Google Sheets features like conditional formatting and pivot tables.

Conditional formatting

Conditional formatting can be used to highlight subscriptions that are due soon. To monitor upcoming subscriptions, I have added two extra columns to the spreadsheet: Next Due Date and Days to Action. Days to Action is a calculated column that automatically subtracts the current date from the renewal date using the formula below:

        =IF(E2="","", E2-TODAY())
    

Once those columns are set up, I can then apply conditional formatting to highlight subscriptions due in less than a month.

To apply conditional formatting to a spreadsheet, navigate to Format > Conditional Formatting. Enter the range of cells to format in the Apply to range text box. Under Format rules, click the first dropdown box (Format cells if…) and choose Custom formula is.

Next, enter the following formula in the Value or formula input field:

        =AND(ISNUMBER($K2), $K2 28)
    

The above formula checks if the Days to Action column contains a number and whether that number is less than or equal to 28. You can create another formatting rule that checks whether the number of days to the particular action is less than or equal to a week.

Pivot Tables

Pivot tables are a very powerful and versatile tool in Google Sheets and Excel, and they can come in handy for analyzing and summarizing your subscriptions. You can use pivot tables to identify the most expensive subscriptions and the total monthly amount spent per category. This can be helpful for budgeting, identifying unnecessary subscriptions, and even negotiating with providers that allow it.

To create a pivot table, navigate to Insert > Pivot table. Enter a data range and select where to place the pivot table, either on the existing worksheet or a new sheet. With the empty pivot table created, you can select the rows, columns, and values to add to the pivot table. For example, add subscription names to the Rows field and annual costs to the Values field to display each subscription’s annual cost.

Unfortunately, there are no pivot charts in Google Sheets. But, you can export your spreadsheet and open it up in Excel to create automatically updated charts for your data. Excel also offers more customization options for pivot tables than Google Sheets.

You can view the Subscription tracker template on Google Sheets. To make a copy, go to File > Make a copy. You can use the template in its current form or add new columns or categories. You can drag down the small blue circle (fill icon) in the lower-right of the cell to apply a formula to the rest of the column (for columns with formulas).

There you have it—a quick and easy way to manage your service subscriptions in Google Sheets. For a challenge, try using Apps Script to set up automatic email reminders. You can ask Gemini to generate the code, add it to your spreadsheet, and configure a trigger to run when a renewal is due in a specific number of days. Also, consider cancelling those subscriptions that no longer provide the same value as before.