Checkbook registers are a royal pain, and the vast majority of us have one. They're constantly at risk of being off due to human error (in writing, reading, and arithmetic). What else do many of us have that could greatly reduce the pain inflicted on us by the checkbook register you ask? Why Microsoft Excel, of course. By far, the coolest application in the Microsoft Office suite of products (Word is a close second).
It used to be that when my checking account statement arrived in the mail, I dreaded balancing the checkbook because I was always off by at least a few cents (if not a few dollars) due to my poor penmanship. I've been using a spreadsheet for about six months, and my checking account has balanced every time. One thing that did take some getting used to, however, was not making a register entry when I made a purchase with a check at a store. It's not too hard to remember to hold on to the receipt and make an entry when I get home though.
Disclaimer aside, if you still want to make an Excel register of your own, here are the instructions. Set yours up however you wish, but make certain to adjust your balance formula accordingly. It has come to my attention that the balance formula presented below should also work in StarOffice/OpenOffice.org's spreadsheet program1. I've never used that, so the instructions that follow are written with an Excel audience in mind.
- Bold Row 1.
- Enter "Check #" in Cell A1
- Enter "Date" in Cell B1
- Enter "Description" in Cell C1
- Enter "Payment/Debt" in Cell D1
- Enter "Deposit/Credit" in Cell E1
- Enter "Balance" in Cell F1
- Format Column D for currency:
- Select Column F by clicking the column header
- Right click, select "Format Cells"
- Click the "Number" tab
- Select "Currency" in the number list box
- Select "$" in the symbol drop down
- Repeat for Columns E and F
- Adjust column widths as necessary
- Enter your initial balance in Cell F2
- Enter the following formula in Cell F3:
=IF(D3>0,F2-D3,IF(E3>0,F2+E3,IF(D3<0,"PAYMENT ERROR",IF(E3<0,"DEPOSIT ERROR",""))))
- Extend the formula to the desired row (I went to the bottom of the spreadsheet):
- Select Cell F3
- Click and hold on the small box in the lower-right corner of the cell
- Drag the cursor to the desired row
- Release the cursor
- Voila! You have a checkbook register in Excel!
For those of you that don't write a lot of Excel formulas, here's the formula in pseudo-code:
if(payment > 0)
balance = balance - payment
else if(deposit > 0) {
balance = balance + deposit
else if(payment < 0) {
print "PAYMENT ERROR"
else if(deposit < 0) {
print "DEPOSIT ERROR"
As a test, to make sure everything (most especially the balance formula, has been entered correctly, I present you with an ASCII representation of an example spreadsheet with an initial balance of $100.50, a deposit of $49.50, and a debt of $75.25.
+---+---------+-----------+--------------+--------------+----------------+---------+
| | A | B | C | D | E | F |
+---+---------+-----------+--------------+--------------+----------------+---------+
| 1 | Check # | Date | Description | Payment/Debt | Deposit/Credit | Balance |
+---+---------+-----------+--------------+--------------+----------------+---------+
| 2 | | | | | | $100.50 |
+---+---------+-----------+--------------+--------------+----------------+---------+
| 3 | 1 | 2/19/2005 | Test Deposit | | $49.50 | $150.00 |
+---+---------+-----------+--------------+--------------+----------------+---------+
| 4 | 2 | 2/19/2005 | Test Debt | $75.25 | | $74.75 |
+---+---------+-----------+--------------+--------------+----------------+---------+
1 - 2005.02.20 at 16:50: generic-man says re Excel Checkbook Register: It's worth noting that the formulas you describe should work in StarOffice/OpenOffice.org's spreadsheet program which is otherwise a good substitute for Excel (good, but not great).