While I put this spreadsheet together with the idea that it could function as a simple check register, the truth is that it will probably work as a register for most any type of basic bank account: savings, loan, credit card, or whatever. You could even use it to track your cash spending.
Tired of pulling out the calculator to figure your checking balance at the end of the week? Let Miscrsoft Office Excel do the calculation for you – just keep up with the amount of your purchases. You don’t have to create a drop-down list for the Transaction column from the Type of Transaction list. It just makes entering the type of transaction easier if you consistently have the same kinds of transactions.
Free Check Register spreadsheet has become awfully popular, as you might imagine. Create column headings and a “Type of Transaction” list as shown below.
Format the Withdrawal, Deposit, and Balance columns as Currency with two decimal places. (select the columns, then go to Format>Cells>Number tab, and select Currency)
Enter the current balance of your checking account in cell F2.
In cell F3, type the following formula: =F2+E3-D3. Auto Fill down the Balance column. Note: The Balance column will fill up with the most recent balance amount.
Starting with cell A3, highlight the cells in the Transaction column. You will now make a drop-down list for the Transaction column.
On the Data menu, click Validation, then click the Setting tab, if it is not already selected. Choose List in the Allow drop-down box and make sure the “Ignore blank” and “In-cell dropdown” boxes are checked. In the Source box, type the following: =$H$2:$H$6.
Click the Error Alert tab and make sure the ¡°Show error alert after invalid data is entered¡± box is unchecked (This will allow you to put in your own descriptions in the Transaction column if you wish¡ªi.e. your check number). Click OK. You have now created a drop-down list for the Transaction column from the Type of Transaction list. You can also create a table for your Description field, and use this same “drop down menu” method to facilitate common data entries.
Starting with row 3, you can now add your account information in your electronic checkbook register.
For practice, type in some numbers in the Withdrawal and Deposit columns to see how the register works.
Make sure you reconcile your checkbook register to your checking account every week, month, etc… Even though Excel, does the calculation for you, you may find that you forgot a transaction or typed in a wrong amount.
Related Post of Create a Simple Checkbook Register With Microsoft Office Excel