How to Use Excel to Balance a Checkbook
You can avoid some of the grief of balancing your checkbook if you use Excel as a checkbook register. Have you ever agonized over not being able to get the checkbook to balance? Usually our checking accounts don't balance because we've left out entries or because we've made math mistakes in our recording. Using Excel to balance your checkbook won't help you remember entries but it can help with the math.
Other People Are Reading
Things You'll Need
- A computer that runs the Microsoft Office Excel program
- A checking account
- An accurate checking account balance
- Knowledge and ability to use Microsoft Excel
Instructions
-
-
1
Print this article to use as instructions and open the Excel program. Look at your checkbook register from your checking account. It has a row of labels on the first line at the top of the register. Open an Excel workbook on your computer and enter a row of labels like the ones in your register. Normally the first label is the "date." The second label may be "check number" and then a "description" column. Use the terms from your register to label the columns in Excel. The last label should be the "balance" column. Once the columns are labeled, you begin to make entrys in your Excel checkbook register.
-
2
On the second row (let's use row 4) of your Excel checkbook register, enter your current checkbook balance in the balance column. This figure has to be accurate or you will carry forward errors.
-
-
3
Enter your first transaction on the row below the row that has your balance (row 5). Make this entry in your Excel checkbook register according to your most recent transaction. It's important to always record every transaction that you make. For example, if you use your debit card or an automatic teller machine or make a deposit, they all need to be recorded.
-
4
This step is where you can really benefit from using Excel as a checkbook register. By entering a "Sum" formula in the balance column Excel will automatically do the math for you. The formula has to be written to add and subtract debits and credits as you normally would in your checkbook register. The beauty here is you ONLY HAVE TO WRITE THE FORMULA ONE TIME AND I'M GONG TO GIVE YOU AN EXAMPLE OF HOW TO WRITE IT.
-
5
For our example lets label column "D" for fees or withdrawals, column "E" to check off items, column "F" for deposits or credits and column "G" for your balance. Lets enter our current balance in cell "G4".
-
6
Now we can write the formula to automate your Excel checkbook register. To write the formula click on cell G5. This should be the balance of your first transaction. Click on formula on the Excel toolbar. It looks almost like the upper case letter E. Choose Sum from the drop down options. This will start the formula in the cell G5 for you. It should look like this, =Sum(. You complete the formula by entering G4-D5+F5) and pressing enter. This should do the math for the balance column. For example if your current balance is $142.54 and your withdrawal is $12.60 your balance should be $129.94.
-
7
Click on the cell G5 and drag the formula down to the next row by left clicking and holding the cell anchor on the bottom right of the cell. This should change the cell references so that you don't have to keep writing the formula. This should allow you to use Excel as a checkbook register.
-
1
