Track your subscriptions with Google Sheets

[nerdslife_team]

Keeping track of multiple subscription services can be a real headache. While the platforms themselves offer some tracking, they’re often limited. And paid tracking apps? That just adds to the cost. But, good news - you can easily manage your subscriptions using Google Sheets, which is free.

Setting up your subscription tracker

1. Open Google Sheets in your browser and log into your Google account.
2. Select “Blank spreadsheet” to create a new sheet for your tracker.
   
   
3. Click on “Untitled spreadsheet” and name your sheet, something like “Subscription Tracker” is a good start.
   
   
4. Next, set up your sheet with column headers. Think along the lines of “Subscription Name,” “Category,” “Monthly Cost,” “Annual Cost,” “Lifetime Cost,” and “Action”.
   
   
5. For columns like “Category” and “Frequency,” add dropdown lists. To select all the cells in a column (excluding the header), click the column header, then press and hold Ctrl while clicking the header again.
   
   
6. Click “Insert” in the top menu.
   
   
7. Choose “Drop-down” from the list. This adds dropdowns to the entire column and a panel opens on the right.
   
   
8. Add your dropdown items in the right-side panel. For categories, you could use things like “Entertainment”, “Software”, “Utilities”, etc. Use “Add another item” for more entries.
   
   
9. You can color-code each item by clicking to the left of the item name.
   
   
10. Repeat this for the “Frequency” column, adding items like “Monthly”, “Annually,” or “Weekly”. Then fill in the details of your subscriptions. Don’t include currency symbols when typing amounts.
    
    
11. To format the “Date Subscribed” column, select all the entries in it and click “Format”.
    
    
12. Go to “Number” and select “Date” to format the entries.
    
    
13. Format the “Amount” column similarly. Select all the entries, go to “Format”, then “Number”, and pick “Currency” or “Currency rounded”. If you use a different currency than the one set by default, you can choose “Custom currency”.
    
    
14. Finally, create dropdowns for the “Action” column with options like “Renew,” “Cancel,” and “Upgrade.”
    
    

Tracking your subscriptions

The monthly cost calculation depends on the frequency of your subscription. You can multiply by four, divide by 12, or input as is.

1. To get the monthly cost, use this formula: =IF(C2="Monthly",E2,IF(C2="Annually",E2/12,IF(C2="Weekly",E2*4,"")))
   
   
2. Calculate the annual cost by multiplying the monthly cost by 12: =IF(C2="Monthly",E2*12,IF(C2="Annually",E2,IF(C2="Weekly",E2*52,"")))
   
   
3. For lifetime cost calculation, use this formula: =IFS(C2 = "Weekly", F2 * INT((TODAY()-D2)/7), C2 = "Monthly", (DATEDIF(D2,TODAY(), "m") * F2), C2 = "Annually", (H2 * DATEDIF(D2, TODAY(),"y"))).
   
   
4. In the last “Action” column, select the appropriate action from the dropdowns.
   
   

At this point, your subscription tracking spreadsheet is functional, but we can add another layer of utility using conditional formatting.

Use conditional formatting

You can use conditional formatting to know which subscriptions require immediate action.

1. Add two more columns: “Next Due Date,” and “Days to Action”.
   
   
2. Go to the “Format” menu and select “Conditional formatting”.
   
   
3. In the panel that appears, add the range of cells where you want the conditional formatting to be applied, for example A2:K100. Then, click on “Format cells if” dropdown.
   
   
4. Select “Custom formula is”.
   
   
5. In “Value or Formula” enter: =AND(ISNUMBER($K2), $K2 <= 28). This will check if there’s any number in the “Days to Action” column and if it’s less than or equal to 28.
   
   

Note: You can create similar formatting rules to check if the number of days to action is less than or equal to one week. Also, consider using fill colors to easily identify columns that contain formulas.

Things to keep in mind

• Once your tracker is set up, just update the amount, and Google Sheets will automatically update other relevant information.
• You can create Pivot tables to summarize the data.
• While pivot charts aren’t available directly in Google Sheets, you can export your sheet to Microsoft Excel to create them.
• If building a tracker from scratch seems like too much, Google Sheets provides a premade template for subscription tracking.
• If you use Google Workspace, you can save your tracker to the Template Gallery.