How to Create a Simple Checkbook Register With Microsoft Excel

••• Jupiterimages/Stockbyte/Getty Images

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.


About the Author

Based in Green Bay, Wisc., Jackie Lohrey has been writing professionally since 2009. In addition to writing web content and training manuals for small business clients and nonprofit organizations, including ERA Realtors and the Bay Area Humane Society, Lohrey also works as a finance data analyst for a global business outsourcing company.

Photo Credits

  • Jupiterimages/Stockbyte/Getty Images