The manual process of bank reconciliation statements is usually not efficient and effective with multiple bank accounts since it takes a lot of time. It is trivial because it takes time to accomplish and it is not recommended for most businesses because of the enormous number of transactions. Manual reconciliation is a bank reconciliation process in which the reconciler individually matches each recorded transaction in the depositor’s book with the Bank statement.

Note: The accounting is not expected to buy a premium software for bank reconciliation because it is expensive, but they can, only if they have excess funds. Hence, Excel is enough for bank reconciliation purposes because it can do what a premium app can do. However, it is already simple to sort those items with one click in excel. To explain why it is not, the reconciliation process in Excel is still the same with paid software, that said the only difference is that a premium software automatically sorts reconciling items. Given the above functions, is it still recommended to buy an automated reconciliation software? It can also help calculate differences in the amount between the book and the bank. This is useful in tagging all book debits with all bank credits through a unique reference number, for example, an official receipt or a batch number.Īnother helpful function is the VLOOKUP function, and it is excellent for reconciling check disbursements because it can lookup values based on a check number. The SUMIF function is useful for reconciling bank deposits because it adds all rows in a column when the range column meets a certain criterion. Next, the unmatched rows are sorted to list the reconciling items. First, its advanced functions such as SUMIF, VLOOKUP, and COUNTIF are excellent since they can automatically match data from two or more sheets with hundreds and thousands of rows. MS Excel is a tool so powerful, because of its advanced functions.

The template illustrates the steps used for reconciling transactions between depositors’ records and bank statements.

The formulas in the template can assist in the reconciliation of bank accounts. The Bank credits sheet has data from a bank statement. This sheet has an excel formula to illustrate how deposits are reconciled. The book debits sheet has sample data from the bank account of the depositor.

This template for bank reconciliation excel comprises two sheets with formulas for automation.

Create free bank statement