Written by Sherry Wells.

Sample Checkbook Details:

This is a very basic checkbook. The first thing to do is to enter an opening balance on the first row in Colum F. This balance can be 0 ; there just has to be a number in here to start so that the formulas in later rows will work.

Here are the columns & their explanations:

A Type Enter the type of transaction. I use the following but of course you can use anything you like.

AutoP = autopay, for anything that is automatically taken out of my checking account like a charitable contribution or bill.
E pay = anything I pay by phone or internet.
Trans = transfer to or from savings or any other account.
Debit = cash withdrawals, Debit card use or any other debit.
Dep = deposits.
B Date.
C Description.
D Debit amount. Amount to be subtracted. This column is used in the balance formula so has to be a debit.
E Deposits. Amount to be added. This column is used in the balance formula so has to be a deposit.
F Balance, this column has a formula, =F1-D2+E2 is in row 2. As you make entries, just put your cursor on this cell, hit cntl C to copy then move down one row & hit cntl V to paste. Excel automatically adjusts the row numbers so that the balance is always accurate for the row you are on. If you like, you can copy & paste this down as many rows as you like even if there are no entries in that row yet. In this case the balance will simply stay the same until you make an entry in that row.
G cleared, I just put an * in this column when things, mainly checks, have cleared. You can delete this column if you feel it is unnecessary. If you delete it, you will need to adjust the formula in column I.
H & I balance check, I use these to check my math. I enter the balance from the bank in Colum H. Colum I contains a formula, =H8-F8 (in row 8). This simply tells you if the bank balance matches your balance. You can copy this cell down to whatever row you want & Excel will automatically adjust the row numbers.

Go Back