Balance Your Checkbook with Excel: A Step-by-Step Guide

Balance a Checkbook Using Excel

How to Balance a Checkbook Using Excel. Excel is a spreadsheet application that can add, subtract and use formulas to manage data. Here are the steps for balancing a checkbook using this software.

Step 1

Open the Excel program from your start menu or by double clicking a shortcut on your desktop.

Step 2

Label your headings on the top row and leave open columns between your headings. A1 should be labeled "Method;" B1 should be blank; C1 should be "Date;" D1 should be blank; E1 should be "Description;" F1 should be blank; G1 should be "Debit;" H1 should be blank; I1 should be "Credit:" J1 should be blank; K1 should be "Balance;" L1 should be blank; and M1 should be "Cleared."

Step 3

Change your blank column widths to separate the data you will insert later. Click on the first blank column (B), hold the "Ctrl" button down and click on the other blank columns, (D, F, H, J; L). They will be highlighted in black. Right click your mouse on any black column. A drop down bar will open, click on "Column Width." Change to "2" and click "OK."

Step 4

Change your other Column Widths that will hold data to the size you desire. The most noticeable change will be the "Description" column. Change this to a Column Width of "27" so it can hold enough text to record your information.

Step 5

Format cells to hold currency. Click on "G," hold down the "Ctrl" button and click on "I" and "K." Right click on one of the black highlighted columns to see the drop down bar. Select "Format Cells." On the "Number" tab, select "Currency" and choose your decimal places and dollar sign. This will make your form consistent.

Step 6

Insert your starting balance. On the first row, you want to insert only your starting balance in the "K2" cell. This will be the number all your debits and credits will be added or subtracted from.

Step 7

Insert your data beginning with Row 3. Check #'s, ATM, Deposit and other methods will be entered in Column A. Insert the date of the transaction (you may format this column by right clicking on "C," "Format Cells" and selecting the date format you prefer). Enter the description and amount in the appropriate columns.

Step 8

Create a running balance. Click on cell "K3." On the toolbar, click on the Auto Sum button which appears as a Greek letter "E." A dotted, moving block will appear on "K2," and you will see a bar under the toolbars with =SUM(K2). Insert your command after the K2: =SUM(K2-G3+I3) and click "Enter." You have formatted your cell data.

Step 9

Format the "Balance" column to update as you enter data. Click on the K3 cell, hold down the "Ctrl" button and click the letter "C" on the keyboard. This copies the format of that cell. Click on the K4 cell, hold down the "Ctrl" button and click the letter "V" on the keyboard. This pastes the format into that cell. Repeat the paste process as far down as you prefer.

Step 10

Reconcile your Excel spreadsheet to your monthly bank statement. Put an "R" in the Cleared column to indicate that an entry matches your bank statement and has been added or subtracted to your balance.

Step 11

Verify your balance. Your bank statement may be different from your Excel balance. Certain transactions may not have cleared the bank that you have recorded. Take your Excel balance, and add or subtract any amounts that do not have an "R" beside them to your Excel balance. This total should match your bank statement balance.

Tip

Set up the spreadsheet on the front end, and reconciling will be easy. Enter your receipts at least weekly for an accurate balance. Back up your Electronic Checkbook every three months by saving to a CD. You can name your sheets at the bottom of the spreadsheet to organize your checkbook by months of the year.