Starting with the 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

Here’s what to do once you’ve downloaded our Excel Subscriptions Manager to organize the many subscriptions now available.

After opening there’s a few things to do first:

Currency prefix

Change the currency if  $ doesn’t apply e.g. change to  STG ₤, Euro € etc.

We’ve made that easy with two Excel styles CurrCalc (the blue calculated cells) and CurrEnter (the Price column)

starting with the excel subscriptions manager microsoft office 34427 - Starting with the Excel Subscriptions Manager

Tax Rate

For countries like USA and Canada with state/local taxes on top of the listed price we’ve included a tax calculation.

Change the Tax Rate at cell B21  (below the main table) to whatever applies in your area.

For places with an integrated VAT/GST type 0%   e.g.  UK, Australia and NZ.  To tidy things up, hide the columns Taxable? and Price with tax. Don’t delete the columns because that will break some formulas.

Individual services can be changed to non-taxable in the Taxable?  column.

Who Pays

This column is for a couple or family with several people paying for services.  You might discover that two people are paying for the same service!

Change the ‘Who Pay’s selector to the names in your family.  Data | Data Tools | Data Validation | Settings.  Under Source change the comma separated list.

starting with the excel subscriptions manager microsoft office 34428 - Starting with the Excel Subscriptions Manager

If it’s just you, enter one name and maybe even hide the column since it doesn’t apply.

Edit the subscriptions

Add/Remove rows in the table to suit you.

Deleting rows is the best way to manage the table. Select a row in the table, right-click and choose ‘Delete’.

img 5e3a448f534f2 - Starting with the Excel Subscriptions Manager

Rows/Services can be added later. Select a row, right-click and choose Insert.  Inside an Excel Table, all the formatting and formulas will be copied to the new row.

We know some novice Excel users are cautious about deleting/inserting rows and prefer to erase or blank-out cells in the existing table.  Do that by selecting the Service name in the first row and pressing Delete to remove the name.  The count of services in the total row is based upon the service names.  All that said, it’s FAR better to delete unwanted rows from an Excel table … try it.

The example workbook includes many common services (US prices) but you should check for price changes or other variations. Maybe you pay yearly, not monthly or have some special deal.

This workbook was inspired by the growth of online subscriptions. It could be extended to any regular payments with a known value like mobile phone, Internet connection, cable TV, rates etc.

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.

 

Excel Subscriptions Manager

More about the Excel Subscriptions Manager

Subscriptions Manager for Excel 2007-2016

Excel – Nested IFs and alternatives

SUMIFS Excel SUM with filters galore

subs profile e1563205311409 - Starting with the Excel Subscriptions Manager
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