≡ Menu

Bank Reconciliation Template In Excel

The process of analyzing and adjusting any differences reflecting in the cash balance section shown on the bank’s statement with the amount reflecting in an account holder’s record, is vital for any organization. Majority of the accounting information systems have an established module to make the accounting process really easy.

What Is Bank Reconciliation Template?

(Example of Bank Reconciliation Template)


Download Free Bank Reconciliation Template

Banks send a statement that is helpful for accountants in matching and ensuring that every general ledger item has cleared the company’s account. This matching process will typically involve making allowances for any check that has been issued but hasn’t been presented and also for checks that have been deposited but hasn’t been cleared or credited yet. Also, if any discrepancy persists, finding a cause and getting the records into an agreement is a vital part of this process. This is a critical function especially when determining the cash amount currently available on hand.

Wondering how do you perform the above process in Excel? Firstly, any business or individual who writes out various checks for payments and also receives checks for an array of undertakings for their work, can undertake this process. Any account ledger has multiple entries. During the end of each month, a statement is issued by your financial organization.

As per the relevant process, you are required to compare and match all the check entries that have been written out by you. These entries are usually created for the purpose of writing out the checks that have been already cleared. In addition to these check entries, there are several other entries in the statement such as cash deposited, cash withdrawn, payments received etc that need to be correctly verified.

Benefits Of Using Excel Template For This Purpose!

In case there is any time lag between you writing out the checks and the recipient receiving the money due to the time taken by a financial system, there will be an obvious discrepancy. This type of a discrepancy will be even more obvious when you write out a check towards the end of the month and it gets cleared at the start of the new month. Several of such entries will only reflect in the statement generated in the following month.

Often a check could get misplaced or lost and this can be discovered while performing a thorough verification. If the check entry aren’t too many as in the case of a small business or an individual, the tally work can be carried out manually. In majority of other cases, undertaking this job manually can be fairly overwhelming and error prone. This is exactly why an excel template can be very useful.

Excel provides an easy way of performing the validation task. You can use it as a standard function and the process will become really quick and easy. An ideal way in which you can rapidly automate the entire tally process, is by using a ready template. Such type of a template will also easily calculate the typical average time taken for your check to get cleared over a specific period of time. Once the discrepancy has been discovered in a statement, you will be able to promptly take corrective actions by informing the bank immediately or by simply issuing a new check.

How To Do A Bank Reconciliation Template

Reconciling your checkbook can be a fairly tedious process. However, it is especially vital if you are using a debit or checking account card and do not request receipts. It is a good idea to always check your online statements and various other financial records on a regular basis. This in turn will help you to easily create an electronic record with the help of a MS Excel file. Having such type of a record will enable you to contest any wrongful charges that you did not incur. Also, you will be able to keep a record of every purchase and deposit that has been made by you.

You could create a template for any of the following purposes:
1. Daily bank reconciliation
2. Weekly bank reconciliation
3. Yearly bank reconciliation


1. Open MS excel and click on “File”. Create a “New Workbook” by selecting from the top option on the menu in Excel. You can also create a new spreadsheet by clicking on “File” and then choosing “Import data.” You will be able to browse to a specific file that you have downloaded by clicking “OK.” View as the data populates into your blank spreadsheet.

2. If you would like to explore another way of populating the data, just log in to your account for online banking and select the option called “Online banking” or “Internet banking.” Next, click on this option and look for another option titled “Download transaction data.” After locating it, just click on “Microsoft Excel” by selecting from the drop down menu list that appears. You can then select checking or savings account as applicable, for the purpose of downloading the transaction data. Select a start date and end date by using the appropriate fields.

3. Click in the download button and choose “Save”. Save the data file on your computer desktop or any other preferred location. Log out of the online account and close the internet browser window. Click on the saved file to open it in MS Excel.

4. Place your cursor on the top left corner of the spreadsheet and Click “Edit” and then “Insert Row” into the spreadsheet. Type column headings such as “amount” and “date” “transaction type” to label all the rows from left to right. If you have been keeping receipts, create columns at the far right and mark item for which you have receipts. Check if all the charges and deposits are accurate. Save file and add sheets if required, for any consecutive daily bank reconciliation, weekly bank reconciliation or yearly bank reconciliation.

5. You can also enter this information manually after creating a new workbook with column headings. Enter information from your statements and complete the above steps.

Having your template ready will serve as a handy option. Additionally, it will be really helpful when you need to track your earnings and expenses for the purpose of filing your income tax or when you require to track a specific fee amount or even for the purpose of tracking a specific debit or credit transaction.

{ 0 comments… add one }

Leave a Comment