Guest Author - Chris Curtis
Start the year off with a New Year’s resolution to keep your checking account balance in check. Without going out and spending the dollars to buy Quicken and then learning how to use the program, you can accomplish the simplified goal by using Excel. Excel is powerful enough to allow you to enter in your transactions, maintain your running account balance, and be the sort and filter expense types at the end of the year to analyze your spending and prepare for tax reporting.
Follow the instructions below to develop your worksheet.
• Cell A1 – Type in the name of the account for which you want to keep the checkbook register.
• Beginning in cell A3 type across the column headings as follows:
Date, Check #, Payable to, Deposit, Withdrawals, Balance, Expense Category, as shown below.
• In cell F4, type the following formula: =SUM(D4,-E4)
• In cell F5, type the following formula: =SUM(F4, D5,-E5)
• Make cell F5 the active cell. Use the fill handle to copy the formula down as many rows as you feel are necessary. (Copy down to at least row 50; you can always copy more rows later.)
You are now ready to make entries into your check register. You will use the Payable to: column to enter in both to whom your checks were written and from whom you have received money. With multiple check deposits, you might consider entering each check on a separate line in the worksheet if you want to be able to track how much money you have received from each source.
I rarely use checks so in the Check # column I use the following notations for withdrawal entries that are not checks: ATM, Debit, Phone Pmt, Online Pmt etc.
As you enter each amount paid out, you want to classify the expense in the Expense Category column. Below is a list of common expense categories that you might consider. When you are entering in credit card payments or loan payments, you might consider entering the principal payment and the interest portion of the payment on separate lines so that you can later track how much interest you paid out in the year.
Credit Card Pmt
At the end of the month and/or end of quarter and/or end of year, you can now sort, filter and create pivot tables from the data you have collected in your check register according to your own personal needs. Using an electronic check register can help you keep your financial picture organized and it will make preparing for your tax return a breeze.