Build a Subscription Tracker in Excel

A subscription tracker in Excel shows every recurring payment in one place: what each service costs, when it renews and how much you spend per month. You need one table and four formulas: EDATE for the renewal date, a division for the monthly cost, SUMIF for category totals and TODAY for the days left. This guide builds it step by step, and the same skills show up in office tasks like tracking software licences or supplier contracts.

Step 1: Set up the table#

Start with one row per subscription and one column per fact. Type these headers in row 1 and fill in your services:

The key design choice is column D. Instead of typing "Monthly" or "Yearly", store the billing cycle as a number of months: 1 for monthly, 3 for quarterly, 12 for yearly. One formula can then handle every plan.

Two more tips:

• Type real dates in column E, such as 12/10/2026 or 2026-10-12, so Excel stores them as dates rather than text. Dates sit on the right of the cell by default; text sits on the left.
• Turn the range into a table with Ctrl+T (Mac: ⌘T). New rows then pick up the formulas automatically.

Step 2: Find the next renewal date with EDATE#

EDATE adds a number of months to a date. In F1 type "Next renewal", and in F2:

=EDATE(E2,D2)

Fill it down (Windows: Ctrl+D; Mac: ⌘D). Streaming renews on 12 Nov 2026, Cloud storage on 1 Mar 2027 and the News site on 15 Jan 2027. If you see a number like 46338 instead of a date, format the column as a date with Ctrl+1 (Mac: ⌘1).

EDATE is safer than adding 30 or 365 days. It keeps the same day of the month and handles short months: a plan paid on 31 January renews on 28 February (29 in a leap year). The EDATE function guide has more examples, and EDATE vs EOMONTH explains when you want the end of the month instead.

When you pay, just update the Last paid date and the renewal moves forward by itself.

Optional: roll the renewal date forward automatically#

If you'd rather enter the start date once and never update it, this formula finds the next renewal after today:

=EDATE(E2,D2*(INT(DATEDIF(E2,TODAY(),"m")/D2)+1))

DATEDIF counts the whole months since the start date, INT rounds that down to whole billing cycles, and EDATE adds one more cycle. On 2 November 2026 it gives the same dates as Step 2. It returns #NUM! if the start date is in the future, so use it only for plans that have already started.

Step 3: Turn every plan into a monthly cost#

Yearly and monthly prices can't be added directly. Divide each cost by its billing cycle. In G1 type "Monthly cost", and in G2:

=C2/D2

Monthly plans stay the same. Cloud storage becomes 8.33 per month (99.99 ÷ 12) and the News site becomes 10.00. Now add the column:

=SUM(G2:G7)

The result is about 103.30 per month. Multiply by 12 for the yearly figure: about 1,239.63. Seeing the yearly number is often the moment people cancel something.

Step 4: Total by category with SUMIF#

SUMIF adds the numbers whose row matches one condition. List the categories in J2:J5 (Entertainment, Work, Health, Reading) and in K2 type:

=SUMIF($B$2:$B$7,J2,$G$2:$G$7)

Fill down. Entertainment comes to 26.98, Work to 21.32, Health to 45.00 and Reading to 10.00. The $ signs lock the ranges so they don't move as you fill. The SUMIF function guide explains each argument.

To see what is due soon, SUMIFS adds the full cost of every renewal in the next 30 days:

=SUMIFS(C2:C7,F2:F7,">="&TODAY(),F2:F7,"<="&TODAY()+30)

On 2 November 2026 that returns 84.97: Streaming, Music, Gym and Design app all renew before 2 December. The & joins the operator to the date. Writing ">=TODAY()" inside the quotes would not work.

Step 5: Flag renewals that are due soon#

A Days left column and one colour rule turn the table into a reminder. In H1 type "Days left", and in H2:

=F2-TODAY()

Format the column as a number, not a date. On 2 November 2026, the Gym shows 3. TODAY updates every time the file recalculates, so the count goes down each day.

Then select A2:H7, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter:

=$H2<=7

Pick an orange fill. Any service that renews within a week is now coloured across the whole row. The $ before H keeps every cell checking the Days left column.

Ideas to extend your tracker#

Once the basics work, a few small columns make the tracker more useful:

• Status (Active or Cancelled). Use SUMIFS with "Active" so cancelled services drop out of the totals.
• Payment method, so you know which card to update when it expires.
• Notes, such as the cancellation deadline or a shared login owner.
• Annual cost with =G2*12, if you think in yearly budgets.

The same structure works for other trackers. Recurring payments with due dates fit a bill tracker, day-to-day spending fits an expense tracker, and stock levels fit an inventory tracker. For a business view of income against costs, see how to build a profit and loss statement.

Common problems and fixes#

These are the issues that come up most when building a subscription tracker:

1. EDATE shows a number. The formula is right; the cell is formatted as General. Apply a date format.
2. #VALUE! in the renewal column. The Last paid date is stored as text. Retype it, or check that it sits on the right of the cell.
3. The monthly total looks too high. A yearly plan has 1 in the Months column instead of 12.
4. SUMIF returns 0 for a category. The category name is spelled differently, or has an extra space. Use a drop-down list in column B to keep names consistent.

Practise it#

Start with the exercise on this page: one EDATE formula gives the next renewal. Then practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet, where you work out renewals and month-ends, and practise SUMIF to build category totals. Each formula you type is checked instantly.