Here’s some more detail about the Excel Subscriptions Manager, how it’s put together, what you can change and extras to consider.
See also Starting with the Excel Subscriptions Manager about how to setup the spreadsheet.
The whole thing is an Excel Table cunningly called Subscriptions.
Formatting, especially alternating row shading is handled automatically.
Tables include the filter buttons to let you quickly sort and filter the rows.
Which cells to enter?
All the cells to type in are in black text and on the left of the table (except Notes).
We’ve kept it simple by just coloring the calculated columns (Price with Tax, Annual Cost) in blue text. Those colors are controlled by Cell Styles for consistency.
The color change has no significance to Excel, it’s just a reminder to us humans.
We’ve not done any protection to prevent unwanted changes to the worksheet. Adding protection is a good idea if you’re worried about the accidently messing up for formulas.
Pull down lists / Data Validation
Per …, Who Pays and Taxable columns are all pull-down lists. Setup those at Data | Data Validation | Settings.
Allow – List
Source – comma separated list of choices
In-cell dropdown – ON
Pull-downs ensure the cells have consistent entries. Consistency is important when formulas rely on the values.
For example, an Annual Cost cell will error badly if the Per … cell isn’t one of Monthly, Quarterly or Yearly. We could not find any services with Quarterly payments but left that choice in to properly demonstrate IFS() in the Annual Cost column.
Price with Tax won’t work unless the Taxable column is either Yes or No.
There are broadly two ways to populate a pull-down list; a fixed list defined at Data Validation or linking from there to a separate group of cells with the list. The choice depends on the situation, how often the list might change, ease of use and (honestly) the gut feeling or habit of the developer! We chose fixed lists defined in Data Validation because it’s simpler and avoids extra tables on other tabs.
The ‘Paid by’ column is intended to note which payment system was used; credit/debit card, direct debit, Paypal etc. Since that can change more often, we left that as text without a pull-down. You can type in entries with AutoComplete to help keep the list consistent.
Calculating Price with Tax
This seemingly simple calculation can be a trap for Excel newbies.
The Tax Rate cell looks like a percentage, but Excel experts know that under the displayed % is a fraction. For example, a cell that says 5% is really 0.05 when Excel uses that cell in calculations. When you type in a value with a % symbol, Excel converts it to a decimal fraction but displays it using the Percentage number format.
(BTW, the Tax Rate cell M2 is a named range called Tax_Rate to make formulas easier to read).
Calculating a price plus tax can be done in two ways. In the sample worksheet we’ve used the ‘1 + ‘ method e.g. [@Price]*(1+Tax_Rate) . The price is multiplied by the tax rate plus 1 (e.g. 5% tax is 1 + 0.05)
Another, longer, way to do the same thing is: [@Price]+( [@Price] * Tax_Rate)
Either formula is OK. We prefer the shorter version, but the latter common, if only because it’s the way it was taught in schools.
Annual Cost is a good example of how useful the IFS() function is. IFS was added in Office 365 and Office 2019 (Windows or Mac) plus Excel Online.
Before IFS, we had to manage messy nested IF’s, multiple cell formulas or even VBA, just to cope with more than one condition.
The total under Annual Cost is part of the Total Row feature in Excel Tables. Turn on the Total Row at Table Design | Table Style Options. Under ‘Annual Cost’ use the pull down list to choose a formula.
Filling Total Row blanks
Many of the total cells don’t apply (e.g a SUM of the Price column makes no sense because it’s a mix of payment periods). Instead of leaving them blank, add some other formulas plus a label.
Notes field tricks
Almost any table or list needs a Notes or Comments column. There’s always some special case or exception that doesn’t fit into the standard columns.
To let the text fit into available space, we make the font size a little smaller (e.g. 9pt vs the usual 11pt).
Wrap Text is on, any text doesn’t disappear off the end of the cell.
The entire table is set to vertical center formatting. If the notes wrap to more lines, the rest of the row will adjust elegantly. See that in the Office 365 row, compared to the others.
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. Most of the ebook works for many versions of Excel, however the Enhanced Subscriptions Manager is only for Excel 365, Windows or Mac.
We deliberately kept the worksheet simple leaving plenty of scope for additions.
One possibility is a specific ‘due date’ column for yearly subscriptions. Add to that some conditional formatting to highlight when there’s a month before renewal.
PivotTable with drill-down
At first look, a PivotTable might seem a waste for this small worksheet but the drill-down feature is handy.
Drill down shows you a summary of the table with the option to click down from the summary to see sub-totals and eventually the rows that make up that part.
PivotTables and PivotCharts from scratch includes help and examples of drill-down. Starting on page 29 of the 2nd edition with the ‘Drill Down 2.xlxs’ included worksheet.
Different Views with Dynamic Arrays
Dynamic Arrays let you show the same table in different ways without changing the original table.
Use functions like SORTBY() and FILTER() to make other tables/tabs showing different views.
Highlight selected row or column
Enhance any table with some color/formatting changes when a cell is selected. See Excel tricks to highlight selected row, column, heading and more