An Excel checkbook register eliminates some of the stress involved in managing a checking account. It does some of the math for you, which makes it for many people a handy and useful tool. Although you could download a check register template from the Microsoft Office website, creating one from scratch isn’t difficult. To build a simple register that calculates your checking account balance automatically, you need only a basic structure, some formatting and a few formulas.
Create the Basic Structure
A seven-column spreadsheet will work for a simple checkbook register. Open Excel, click the Save button and save the register to a convenient location in your system. In the first row of the spreadsheet, starting in column A, add these labels in all capital letters: code, date, payee, description, payment, deposit and balance. Resize the payee and description columns so they can include more text. To do this, place your cursor in the header section between columns C and D. When the cursor’s appearance changes to two arrows, left-click and drag until the column-width tool tip displays 30.00. Repeat this for the header section between columns D and E.
Format the Register
Correct formatting is vital to make sure the information you enter displays correctly. To format the date column, click the column B header to select the entire column and then right-click anywhere in column B and select “format cells” to open the formatting window. Open the number tab, select the date option and choose how you want to display dates. To format columns E through G to display dollar amounts, left-click in the header for column E and drag through to column G. Click the drop-down box in the number section of the home tab and select the accounting option.
Add a Balance Calculation Formula
Enter your opening balance in cell G2 and then move to cell G3. In this cell, enter this formula: =IF(ISBLANK(B3),"",SUM(G2+E3-F3). This formula tells Excel to either add or subtract a dollar amount from the opening balance after you enter a transaction date. Use Excel’s autofill feature to carry the formula down the entire column. To complete this task, click in cell G3 and position the pointer in the lower-right corner of the cell. When the cursor changes to a simple cross shape, left-click and drag as far down as you want to go.
Use the Register
Enter a check number or another designator such as ATM, DEP or EFT in the code column to identify the transaction type. Enter the date, the payee and a brief description of the transaction. If the transaction is payment, enter it using a negative sign. For example, if you’re recording a $20 ATM withdrawal, enter it as -20. The register will automatically subtract this amount and display the correct balance. If you’re recording a $20 deposit, enter it as 20 to tell Excel to add the amount.
- Jupiterimages/Stockbyte/Getty Images