GAMES! GAMES! & MORE GAMES

Using Excel as a Checking Accout Register

Microsoft Excel is a great tool for keeping track of your checking account balances and transactions. Many people either still use the traditional checkbook register or a computer software program like Microsoft Money or Quicken QuickBooks. While the programs mentioned do provide more in the way of budgeting and charting, I find that using Excel allows for much customization and sorting of data.

                First of all, within or below this article I will include a sample Excel checkbook register (or a link to it) that you can use to experiment with or even use as your checkbook register. It would be best if you can access this file before reading further. I began this creation by simply building an excel sheet that looked like a checkbook register, complete with all or any columns you like. I have two columns for amounts, one for the transaction amount and one for the new balance. However, with Excel, you can insert a formula within the ‘balance’ column so that your new balance is automatically calculated. The formula would be the previous ‘balance’ cell, in my sample ‘F3’, plus the next transaction amount (‘E4’). The formula would be =(F3+E4). Please note that it will always be ‘+’, when you enter withdraws you will enter them as ‘-$50.00’ (note the negative ‘-‘). You will only need to enter this formula once then copy and paste it into the cells below. Excel will automatically adjust the formula for the placement of the cell(s) you paste it into.

                You may notice that in my examples I place a variety of codes such as 101, phone, online, EFT and R3. These are noting how the payment was made. A number is simply a check number.  The codes ‘phone’, ‘online’ and ‘EFT’ refer to payment by phone, an online payment and an Electronic Funds Transfer (or Automatic Transfer) respectively. The number preceded with the ‘R’ is a system I use to track debit card purchases using the receipts. When I make a purchase with a debit card, I keep the receipt until I can enter it into my Excel register. I then number it ‘R’, for receipt, and the next number in sequence. I then keep my receipts in an expandable file folder for future use. This may seem a bit extreme to some people but it has saved me some money had hassle before since I have proof of my transaction.

                Another great idea that can be used with the Excel register is sub-accounts. The second sheet in my example file shows this in action. It is any easy way to set aside money similar to an envelope system, without envelopes. Transaction ‘R3’ in my example is a deposit of $250. However it is divided among five categories. Note that the balances are kept separate at the bottom (use a simple ‘SUM’ function) and that when you use your account you should only use funds from the ‘General’ column. You need to have the discipline to view your ‘General’ balance as your actual checking account balance to prevent use of allocated funds. When you need to use funds from a specific ‘envelop’ simply deduct that transaction from the corresponding column rather than the ‘General’ column. An added perk to this feature is that the sub-account balances provide a cushion for your general account. If you accidentally use more than what is in your checking account (according to your general account balance) then you will likely not actually be overdrawn at the bank.

If saving money, or setting aside money for ‘envelopes’ is difficult for you, try this trick, round all your transactions up to the nearest dollar. For example, if you spend $4.32 at McDonald’s, put –$5.00 in the ‘General’ column and $.68 in the saving column (or column/sub-account of your choice). This makes your amount in the ‘Amount’ column the actual amount you spent, and therefore what your bank statement will show. This is done by a ‘SUM’ function in the ‘Amount’ column cells for all cells to the right of them. When you balance your checkbook use the numbers in the ‘Amount’ column in order to balance it correctly. This method actually builds up much faster than you might think and you do not miss it since it comes out in smaller amounts. It also plays into the idea that if you do not have money to save then you likely do not have money to spend on non-necessities like McDonalds.

There are so many options you can do with Excel for tracking your money. There are hundreds of formulas in Excel that you can work with. I hope that some of these ideas will help you keep better track of your finances, provide a free way to track you finances on a computer and help you set aside money for savings, date nights, college funds, vacations and so much more.

About the Author

Wesley Skiles is creator of www.2007microsoftoffice.com.

Excellent Skin Eggs-cellent Mask