Bank Reconciliation Tutorial Using Excel


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.


  • Photo Credit Jupiterimages/Comstock/Getty Images
Promoted By Zergnet



You May Also Like

  • Excel Checking Reconciliation

    Comments Video Transcript. In this clip, we're going to go over how to do an...a checking account reconciliation using Excel. This is...

  • How to Do Bank Reconciliation

    Excel Checking Reconciliation. Using Excel to reconcile your checkbook is simple and quick. ... How to Do Bank Reconciliation Using Tally Software.

  • How to Prepare a Company Bank Reconciliation

    Preparing a company bank reconciliation is similar to preparing a personal checkbook reconciliation, which involves using the bank statements, the details of...

  • Bank Reconciliation Examples

    Bank Reconciliation Examples. ... a spreadsheet or a software program. You will also need bank statements for the period you are reconciling.

  • How to Reconcile a Bank Statement

    Even if crunching numbers is not your greatest talent, doing so is a fact of modern life. If you have a checking...

Related Searches

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