Bank Reconciliation Tutorial Using Excel

Learn how to use Excel to balance your checkbook.
Learn how to use Excel to balance your checkbook. (Image: Jupiterimages/Comstock/Getty Images)

Reconciling a checkbook can be a tedious, but necessary task, especially if you use debit or check cards and don’t request receipts. Check your paper or online bank statements regularly and create an electronic record in Microsoft Excel so that you can contest any charges you didn’t make and keep a record of your purchases and deposits. Having your bank reconciliation handy will be helpful at tax time and when you need to reference a specific fee, credit or debit transaction.

Log in to your online banking account and look for an option called “Internet banking” or “Online banking.” Click this button and then look for another option called “Download transaction data.” Press this button and click “Microsoft Excel” from the drop down list that appears. Select the account -- checking or savings -- that you want to download transaction data from and a start and end date from the appropriate fields.

Click the download button. Click “Save” from the dialogue box that appears. Save the data file to your computer desktop or to another location where you can easily find it. Log out of your online banking account and close your internet browser window. Double click on the file that you saved to your desktop or other location. Wait for the file to open in Microsoft Excel.

Open Microsoft Excel and create a new spreadsheet by clicking “File” then “New Workbook” from the top menu in Excel if the prior process does not work. Click “File” then “Import data.” Browse to the file you downloaded and click “OK.” Watch as data populates your blank spreadsheet.

Place your cursor on the lines delineating columns and manually drag them to make them wide enough so that you can see the data in each column. Place your cursor in the top left corner square on the spreadsheet. Click “Edit” and “Insert Row” to insert a row at the top of the spreadsheet.

Type a column heading to label each row with information from left to right, such as “date” “transaction type” and “amount.” If you keep receipts, create a column at the far right labeled “Reconciled” and place an “x” next to each item for which you have a receipt or record. After moving through all receipts, review any rows that do not have “x” marks in the far right column and research as necessary to make sure charges or deposits are accurate.

Save your file and add new sheets as necessary for each consecutive reconciliation, such as weekly, monthly or bi-monthly.

Enter this information manually by creating a new workbook, creating column headings, entering information from your paper bank statements and completing steps 6 through 8 if you do not have online banking.

Related Searches


Promoted By Zergnet


You May Also Like

Related Searches

Check It Out

4 Credit Myths That Are Absolutely False

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!