With all the subscription offerings available (Netflix, HBO, Disney, CBS, Apple, Google, Amazon, Office 365 etc), here’s a way to manage them all from Excel. Along the way we’ll show you some interesting Excel features.
We were inspired by Brian X Chen in The New York Times, writing about how easy is it to sign up for a service then forget to cancel – How Much Are We Paying for Our Subscription Services? A Lot
In the article there’s mention of an Excel spreadsheet to manage a family’s subscriptions. That sent the Office-Watch.com nerds to their worksheets to make one with lots of options and charts.
It’s a perfect job for Excel because most budgeting programs don’t have the flexibility to handle subscriptions.
Save the spreadsheet to OneDrive, Dropbox etc. so family members can view and edit the one worksheet.
All your subscriptions in one list
This worksheet lets you list all subscriptions with:
Payment frequency (Monthly, Quarterly, Yearly). Most subscriptions are either monthly or yearly. We added quarterly to extend the example IFS() in the Annual Price column.
This is a pull-down list, go to Data | Data Validation to control the selections.
Who pays choose who signed up/pays in the family.
This is a pull-down list, go to Data | Data Validation to change or add names.
How it’s paid Visa, Mastercard etc.
Text fields, Excel’s auto-complete will help keep the names consistent.
Cancellation date so you don’t accidently slip into paying another month
It’s another text column because the options can vary depending on the payment frequency.
That’s all the cells you have to enter, except Notes at the end. The other columns in blue text are calculated for you:
Price including taxes (for our US readers and others with state and local taxes)
* set your tax rate in cell M2 (right of the main table)
Multiplies the after-tax price to show how much you’ll pay over a year. You can see how that ‘low monthly fee’ adds up over a year and lets you compare
Notes no worksheet is complete without a place for extra comments.
Which service is the most expensive?
Sort by the Annual Cost column.
Who is paying for each subscription?
Choose the ‘Who Pays’ filter
Which credit card is charged?
Pull down the Paid by selector
Annual Cost charts
The Annual Cost tab has two charts, both use the same data but with different Excel charts.
One is the familiar pie chart (the 3D variation). On the right is the relatively new Treemap chart (under Hierarchy).
How to download
Download the Subscription Manager workbook from here (v1.1 34kb). Right-click on the link and choose ‘Save link as …’ or ‘Save Target As ..’ depending on your browser. Save to your computer then open in Excel 365/2019 for Windows or Mac, also Excel Online.
It’s a standard .xlsx Excel workbook that will only work in Excel 365/2019 and Excel Online only (the limiting factor is the introduction of the IFS() function). There’s now a Subscription Manager Excel 2007-2016 version available.
Important: See Starting with the Excel Subscriptions Manager for details on the changes to be made to make the workbook work for you.
The inevitable disclaimer: We’ve checked and rechecked the download for viruses and other nasties. However, you should never take anyone’s word for that and check for yourself. Windows Defender will automatically check for you. Don’t run with scissors. Leave the seat down.
Enhanced Subscriptions Manager
PivotTables and PivotCharts from scratch now has the new enhanced Subscriptions Manager with an extra chapter to explain the workbook and the tricks we’ve used to make it.