How to Balance Your Checkbook Using Microsoft Excel 2010

Create a Register Template
1. Open a new spreadsheet in Excel 2010. Leave cell A1 empty, and label the various columns you will need beginning in cell B1. Common examples of column headers to add to your spreadsheet include date, check number and transaction information. The last three column headers should be debit, credit and balance.
2. Apply any desired formatting to your register. This includes changing font size and adding a colored background to your column headers so they stand out more. You should also adjust the size of the columns to compensate for the information that you will enter into them. Those that will be used for transaction or memo information should be considerably wider to allow you to enter more detailed notes.
3. Enter data into your spreadsheet to test its functionality. Do not use real data, but instead create generic information so you can make sure that the columns you created in Step 2 are adequate for the information that you need to enter.
4. Create a formula in your spreadsheet so Excel can keep track of your account balance automatically. Make note of the first balance cell on your spreadsheet. If Column G is marked as your balance, then your first balance cell would be G2. This cell will remain untouched, as it will be where you enter your beginning balance. To create the formula with a balance column at G, credit column at F and a debit column at E, enter the formula =SUM(G3-E3 F3) into cell G2. This instructs Excel to add any credit and subtract any debits on that line from your total balance.
5. Move your cursor to cell G3 and enter the following formula: =IF(AND((ISBLANK(E3)),(ISBLANK(F3))),'',SUM(G3-E3 F7)). Ensure that cell G3 is still active and press the auto fill button with your mouse. Drag your curser down the balance columns in your spreadsheet until you reach the 100th line. You can now enter debit and credit information and Excel will add or subtract it form your balance automatically on all lines between 2 and 100.
Enter Account Information
6. Reconcile your checkbook by subtracting any transactions that you have completed since your last statement date. If you have access to online banking, you can also use that to help you determine your current balance by comparing those transactions that have cleared to those that are still pending.
7. Enter your beginning balance on the first cell of your spreadsheet. In the above example you would enter your beginning balance into cell G2.
8. Enter all credit and debit transactions that you complete into the appropriate column so your balance is accurate at all times.