Construct a Subscription Tracker in Excel with Renewal Alerts

This complete information will provide you with sensible steps to construct a subscription tracker to trace all of your subscriptions in a single place utilizing MS Excel On-line and ship you electronic mail alerts earlier than renewal. It is a crucial instrument, particularly in in the present day’s world, the place we regularly pay for pointless instruments and companies and infrequently overlook about them till a renewal charge is charged to us. This information will assist you to mechanically calculate renewal dates for any billing frequency(Weekly/Month-to-month/Yearly), and ship electronic mail renewal alerts, whereas on the similar time monitoring spending patterns throughout classes and over a time frame.

Step 1: Design The Column Construction

Open Excel On-line and determine the column construction primarily based on the small print we need to monitor, and primarily based on that, fill within the header rows. I’ve used the construction under:

A1: Service - Subscription identify (Netflix, Spotify) 
B1: Class - Sort (Leisure, Productiveness) 
C1: Price - Month-to-month/annual worth 
D1: Forex - USD, EUR, and so forth. 
E1: Frequency - Month-to-month, Quarterly, Yearly 
F1: Begin Date - First billing date 
G1: Standing - Energetic, Cancelled, Paused 
H1: Auto-Renew - Sure/No 
I1: Reminder Days - Alert timing (7, 14, 30) 
J1: Subsequent Renewal - Calculated renewal date 
K1: Alert Date - When to ship reminder 
L1: Days Till - Countdown to renewal 
M1: Month-to-month Equal - Normalized month-to-month price

Step 2: Fill in Pattern Knowledge

Insert a pattern of assorted subscription companies. We will add totally different merchandise primarily based on numerous classes. Additionally embrace each lively and cancelled subscriptions to check the formulation. Go away Columns J, Ok, L, and M empty for now, as they are going to be crammed mechanically with the assistance of formulation within the upcoming steps.

Step 3: Constructing Sensible Calculation Formulation

Now, we’ll add formulation to mechanically calculate renewal dates and alerts. These formulation deal with totally different billing cycles and exclude cancelled subscriptions.

1. Subsequent Renewal Date System (Column J):

Click on cell J2 and enter this components:

IF(G2="Cancelled","",IF(E2="Month-to-month",IF(DAY(F2)>DAY(TODAY()),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(F2)),DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(F2))),IF(E2="Quarterly",DATE(YEAR(TODAY()),MONTH(F2)+3,DAY(F2)),IF(E2="Semi-Annual",DATE(YEAR(TODAY()),MONTH(F2)+6,DAY(F2)),DATE(YEAR(TODAY())+1,MONTH(F2),DAY(F2))))))

This returns clean for cancelled subscriptions and calculates the following month-to-month renewal mechanically primarily based on the beginning date.

2. Alert Date System (Column Ok)

Click on cell K2 and enter this components: =IF(G2="Cancelled","",IF(J2="","",J2-I2))

This subtracts reminder days from the renewal date to find out when alerts ought to set off.

3. Days Till Renewal (Column L)

Click on cell L2 and enter this components: =IF(G2="Cancelled","",IF(J2="","",J2-TODAY()))

This creates a countdown displaying days remaining till renewal.

4. Month-to-month Equal(Column M)

Click on cell M2 and enter this components:

=IF(G2="Cancelled",0,IF(E2="Month-to-month",C2,IF(E2="Quarterly",C2/3,IF(E2="Semi-Annual",C2/6,C2/12))))

This normalizes all prices to month-to-month quantities for price range planning.

Step 4: Construct A Abstract Dashboard

Add a abstract dashboard beginning at row 24 to trace key metrics:

1. Complete Month-to-month Equal (In USD): Add This System to calculate month-to-month prices:

=SUMIFS(M:M,G:G,"Energetic")

2. Energetic Subscriptions: Add This System to calculate lively subscriptions:

=COUNTIF(G:G,"Energetic")

3. Renewals In Subsequent 30 Days: Add This System to calculate Upcoming Renewals:

=SUMPRODUCT((G2:G50="Energetic")*(L2:L500)*(L2:L50""))

4. Estimated Upcoming Price (In USD): Add This System to calculate Upcoming prices:

=SUMPRODUCT((G2:G50="Energetic")*(L2:L500)*(L2:L50"")*(C2:C50))

Step 5: Creating Visible Alerts

Now arrange conditional formatting to spotlight pressing renewals:

1. Choose column L (Days Till Renewal)
2. Go to House  ->  Conditional Formatting -> Spotlight Cell Guidelines
3. Select lower than and equal to 4, and select crimson formatting for pressing alerts
4. Repeat step 2 and add one other rule with yellow formatting for reasonable warnings for cell values between 5 and seven
5. Repeat step 2 and add one other rule with inexperienced formatting for delicate warnings for cell values 8 and above

Now, subscriptions will mechanically spotlight in several colours primarily based on their pending renewal days.

Step 6: Arrange Automated Electronic mail Alerts

Since Excel On-line doesn’t have built-in electronic mail automation, we’ll use Google Apps Script to create highly effective automated electronic mail alerts.

Steps for Setting Up Google Apps Script:

1. Add File: Add your Excel file to Google Sheets.
2. Entry Apps Script: In your Google Sheet, go to Extensions -> Apps Script
3. Delete Default Code: Take away the present code
4. Create New Mission: The challenge will auto-save as “Untitled challenge”
5. Rename Mission: Click on “Untitled challenge” and rename to “Subscription Electronic mail Alerts”

Paste this entire script into the Code.gs file:

operate checkSubscriptionRenewals() {

 const sheet = SpreadsheetApp.getActiveSheet();

 const information = sheet.getDataRange().getValues();

 const headers = information[0].map(h => h.toString().trim().toLowerCase());

 // Discover column indices

 const serviceCol = headers.indexOf('service');

 const costCol = headers.indexOf('price');

 const statusCol = headers.indexOf('standing');

 const daysCol = headers.indexOf('days till renewal');

 const renewalCol = headers.indexOf('subsequent renewal');

 if ([serviceCol, costCol, statusCol, daysCol, renewalCol].consists of(-1)) {

   throw new Error("A number of required headers not discovered within the sheet.");

 }

 let alertMessages = [];

 // Verify every row (skip header)

 for (let i = 1; i  0) {

     alertMessages.push(

       `• ${row[serviceCol]}: $${row[costCol]} renews in ${days} days (${row[renewalCol]})`

     );

   }

 }

 // Ship electronic mail if there are alerts

 if (alertMessages.size > 0) {

   const topic="Subscription Renewal Alerts";

   const physique = `The next subscriptions are renewing quickly:nn${alertMessages.be part of('n')}nnCheck your subscription tracker for particulars.`;

   GmailApp.sendEmail('[email protected]', topic, physique);

   Logger.log('Alert electronic mail despatched!');

 } else {

   Logger.log('No upcoming renewals.');

 }

}

Output:

Learn extra: Excel for Knowledge Evaluation

Greatest Practices for Upkeep

Listed here are a few of the pointers you’ll be able to observe to maintain the method operating:

• Weekly Assessment: Verify upcoming renewals and take motion on undesirable companies
• Month-to-month Updates: Add new subscriptions and replace cancelled ones
• Quarterly Evaluation: Assessment spending patterns and negotiate higher charges
• Annual Audit: Complete assessment of all subscriptions for optimization

Conclusion

With numerous subscription companies, it’s troublesome to maintain monitor of all of your recurring bills. Nonetheless, now with the assistance of this Excel tracker, you will be on prime of your issues and assist lower your expenses. With the assistance of this information, now you can:

1. Construct a complete subscription tracker in Excel On-line with superior formulation
2. Automate electronic mail alerts utilizing Google Apps Script
3. Deal with sophisticated subscriptions with totally different billing cycles, in addition to cancelled subscriptions
4. Preserve management over recurring bills by way of systematic monitoring and alerts

With this method, you’ll by no means lose monitor of subscription prices once more and may make knowledgeable selections for less than these subscriptions that present actual worth.

You’ll be able to entry the subscription tracker Excel file from right here: Excel On-line

Ceaselessly Requested Questions

Sohan Sethi is a Knowledge Analytics Supervisor and Content material creator captivated with utilizing information, know-how, and storytelling to create significant affect. With expertise in healthcare, analytics, and workflow automation, he has constructed instruments and platforms that streamline processes for 1000’s of customers, combining technical experience in SQL, Python, Excel, SharePoint, and Energy Platform with a people-first method to problem-solving.
Past his skilled profession, Sohan is devoted to empowering job seekers and worldwide college students by way of free assets, mentorship, and thought management. He has given a TEDx speak and featured in a number of media platforms together with Enterprise Insider, CNBC, Fox Information, and so forth. He repeatedly shares methods on job search, profession progress, and adapting to challenges. His work has impressed professionals worldwide to unlock alternatives and attain their targets.