Excel Subscriptions Manager

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

excel subscriptions manager microsoft office 34471 - Excel Subscriptions Manager

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:

Price

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)

Annual Cost

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.

excel subscriptions manager microsoft office 34433 - Excel Subscriptions Manager

Who is paying for each subscription?

Choose the ‘Who Pays’ filter

excel subscriptions manager microsoft office 34434 - Excel Subscriptions Manager

Which credit card is charged?

Pull down the Paid by selector

excel subscriptions manager microsoft office 34435 - Excel Subscriptions Manager

Charts

Annual Cost charts

The Annual Cost tab has two charts, both use the same data but with different Excel charts.

excel subscriptions manager microsoft office 34436 - Excel Subscriptions Manager

One is the familiar pie chart (the 3D variation). On the right is the relatively new Treemap chart (under Hierarchy).

How to download

The download is available free to all Office Watch and Office for Mere Mortals subscribers (also free).

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.

Starting with the Excel Subscriptions Manager

More about the Excel Subscriptions Manager

Excel – Nested IFs and alternatives

SUMIFS Excel SUM with filters galore

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address