Bank reconciliation template in Excel

Bank reconciliation template in Excel

No business escapes this scene: the bank statement arrives, someone compares it with what the accounting records say, and the two balances do not match. The gap may be large or just a few coins, but until it is explained nobody can be sure how much money is really in the account. There is almost always a concrete reason: a check that was written and the bank has not cleared yet, a deposit made on the last day of the month that the bank credited the next day, a credit note that was never recorded in the books, or a fee the bank charged that nobody wrote down. The problem is not that a difference exists, it is not knowing where it comes from.

This bank reconciliation template puts both balances in a single file, side by side, and leaves in writing every item that explains the distance between them. It works for any business with a bank account, with one or with a hundred movements a month, and it can be used by the accountant, the administrative assistant or the owner who reviews the accounts on the weekend.

The file is in Excel, with the formulas already set up: the bank block and the books block with their automatic totals, the space for the items on each side, and a final line that tells whether the reconciliation matches or whether something still needs to be reviewed. It is worth reading it once, from top to bottom, before using it, because the order of the blocks is what teaches the logic of the match.

What reconciling means and who this format is for

Reconciling means explaining, with documents in hand, why the balance shown on the bank statement is not equal to the balance shown in the accounting records. It is not about deciding who is right, and it is not about moving a number until it matches: it is about finding and writing down every item that is already recorded on one side and not yet on the other. Once all the items are identified, both balances land on the same figure and the difference is zero.

Both sides are talking about the same thing from different points of view. The bank records what has already gone through the account: the checks that were actually cleared, the deposits it received, the debits for fees and the credits for interest or loans. The accounting records what the business decided and booked, even if the movement has not reached the bank yet. That is why a check written on the 28th appears in the books that same day and does not appear on the statement until the supplier cashes it, sometimes weeks later.

The format fits a small business where one person handles both the cash and the bank, and it also fits a mid-sized company with several accounts and a dedicated assistant. It is essential when checks are used, when customer deposits are credited late, when the bank charges fees, and when there are loans or savings accounts earning interest. It is done once a month, at closing, and no month is skipped: an old reconciliation is an accumulated problem.

What the template includes

The file brings the blocks in the order in which they are read, from top to bottom. There are no formulas to fight with: the cells that add and subtract are already programmed, and only the values and the items are typed in.

The file also includes an instructions sheet with the step by step and the meaning of each block, so that anyone who opens the file can do the reconciliation without depending on the person who built it.

The columns on the sheet

The whole reconciliation rests on three columns, which is why it pays to understand them before typing anything. The sheet does not ask for more data: it asks for the right data.

The calculated columns —totals per block, reconciled balance and difference— feed themselves from the three above. The reconciled balance per the bank takes the statement, adds the pending bank items and subtracts the outstanding checks. The reconciled balance per the books takes the book balance and adds or subtracts the adjustments that were missing. The difference left between those two lines is what tells you whether the reconciliation is finished or whether an item is still to be found.

The example with numbers: how the match is explained

Take an account with a single pending check and a single pending deposit. The statement closes at 12,400,000. During the month the business deposited 800,000 on the last working day and the bank credited it the next day, so it does not appear on the statement yet. It also wrote a check for 500,000 that the supplier has not cashed. On the books side the assistant recorded 12,500,000 but left out a credit note of 200,000 that the bank credited during the month.

What matters in this example is not the figure but the method. Both sides arrived at the same number along different paths, and every step was written down and backed: the deposit with its slip, the check with its number, the note with its document. The day someone asks why the bank and the books showed different balances, the answer is on the sheet, with a name and an amount.

When the difference is not zero, the result is not touched: the item is looked for. The usual causes are an item written twice, a mistyped figure, a bank fee nobody had noticed, or a check from a previous month that expired and has to be reversed. An invented adjustment is never added to close the gap, because that hides the error instead of correcting it, and the mismatch comes back the following month, larger.

How to use the template, step by step

1. Download the file and save one copy per month, named after the account and the period, for example reconciliation-2026-09.
2. Type the closing balance of the bank statement into the first block and the balance per the books into the accounting block.
3. List the bank items: deposits in transit, pending collections and credit notes not yet credited, each with its date, reference and amount.
4. List the book items: outstanding checks, debit notes not yet deducted and bank fees that are still missing from the records.
5. Check that both reconciled balances land on the same figure and look at the difference line: zero is reconciled, any other value asks you to review items.
6. Post the missing adjustments in the accounting records, save the file with its supporting documents and keep the signature of who prepared and who reviewed the reconciliation.

Tips and common mistakes

• Every month, not at year end: old items get forgotten and by then nobody remembers what they were.
• Reconcile against the final statement, not against the app screen, which shows movements in transit that have not settled yet.
• Always write the reference: check number, deposit slip or note number. An amount without a reference is an amount you will have to investigate again.
• Do not delete items that cleared: mark them as reconciled and leave them on the sheet, because they are the history of the movement.
• Never force the match with an invented adjustment so the difference reads zero: if it does not match, an item is missing, and that item has to be found.
• One sheet per account: mixing two accounts in a single reconciliation makes it impossible to follow any item.

When to move to inventory and accounting software

The template works very well while the business has one account, few checks and a manageable volume of movements. When accounts multiply, when checks start crossing with electronic payments, when several deposits arrive every day, or when accounting and inventory live in different files, manual reconciliation begins to take more time than it should and the risk grows that a movement stays unreviewed.

At that point it makes sense to lean on a system. Kardex Tauro keeps inventory, sales, purchases and receivables on the same database, so the movements that reach the reconciliation already come with a document and a date, and there is no need to rebuild them on a sheet. Reconciliation then stops being detective work and becomes a review: the two balances are compared, the pending items are checked and the month is closed. If the business is still small, this template is the best starting point; take the step to software when the volume no longer lets you review it calmly.

Reconciling is not a chore for the accountant: it is the only way to know that the money shown in the books really exists in the bank. With this template the match is explained through items, with documents and with the name of whoever reviewed it, and the difference stops being a mystery and becomes a short list of things to do.