Bank Reconciliation Tutorial Using Excel

Save

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.

References

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

Comments

Resources

You May Also Like

  • How to Reconcile a Bank Statement

    Monthly bank account reconciliations are vital for protecting both your money and your business. A typical statement lists transactions and the bank...

  • Excel Checking Reconciliation

    Microsoft Excel allows individuals and companies the ability to reconcile debit and credit transactions with spreadsheets. Use Excel spreadsheets to keep track...

  • 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...

Related Searches

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