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
The Exclusive Downloads page link is in every issue of both email newsletters.
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.