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)
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.
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’.
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.
More about the Excel Subscriptions Manager
Subscriptions Manager for Excel 2007-2016
Excel – Nested IFs and alternatives
SUMIFS Excel SUM with filters galore
