More on Finance - the bill paying spreadsheet

In December of last year, I wrote an article entitled " A protocol for keeping safe in an age where virtually nothing is safe."  A portion of that article dealt with finance, not something I would ordinarily address.  But as I stated in that article, I was forced to look more carefully at several things, bill paying was just one of them.  Since then I have tried to do two things, 1) create a spreadsheet that is relatively self-explanatory and easy to use and 2) create a template spreadsheet that I can give to others.

In this article, I will delve more deeply into the creation of this spreadsheet (I opted for a spreadsheet over a relational database because I found more people felt at ease with a spreadsheet).  As I went along I discovered that I needed to have one tab for each bill to be paid and one summary tab, which should give a quick synopsis of all the tabs.  

I wanted to be able to print this out into a binder (like the one I mentioned in the article referenced above).  So it was important to have each bill on a separate page with as much information about the bill as possible.  I decided to put all the login information on the summary page, this would include all information for multi-factor authentication, password managers, PayPal, etc.  I also realized that having a way to readily see when and if a bill has been paid, should be part of the summary.  I started to develop this spreadsheet so that it would be easily used by anyone.  

The summary page has the following components:

• Basic instructions
• Name of each bill - which links to the home cell of the tab (corresponding to the name of the tab)
• The method by which the bill is paid
• Any password or login information needed to pay the bill
• Link to the description of the bill
• Formula and conditional formatting that shows which bills have been paid and when
• A Legend
• Two action/VBA buttons that will create a new tab (one for an annual tab, another for a monthly tab)
• All the information for general logins like paypal.com and a password manager

Each subsequent tab houses one bill with all requisite contact information.  The bill tabs all have a description section (a description of what the bill represents, which is not always obvious), and some action buttons to return to the home cell of the tab and to the summary page.

When a bill tab's information is filled in for any month, the paid date for that bill will appear on the summary page along with turning the cell on the summary page:

• Green background
• Dark green lettering
• Dark borders around the cell

This conditional formatting for the summary page makes it extremely easy to see what still needs to be paid and what has already been paid.  No more forgetting to pay a bill or paying something twice (I've done both).

Each bill's tab lists the bill name, account number, contact information, a description of the bill, columns for the month, due day, paid date, posted (to your account) date, amount, how it is paid, category and check/confirmation number.

The last tab is for lists.  I have prepopulated it with a categories list.  It is specifically for Lookup tables (that is also the name of the tab).  I previously wrote an article on creating dropdown lists in excel.  This is what this is for - I have used it here in the Categories on each tab, but it can be used elsewhere.  If you do plan on creating other lists, I suggest creating a named range for that list, look at the categories list to see it done there.  A named range makes everything else a lot easier to understand.

• Download your free copy of my "Spreadsheet Template" here:  Pay-Bills.xltm

I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts...

Please do not forget to press the "Thumb's Up" button if you think this article was helpful and valuable for EE members.

It also provides me with positive feedback. Thank you!