Bank reconciliation in Excel, without manual work

It is the last working day of the month. Time for bank reconciliation. A bank app reads your statements, matches them and posts the entries, so most of it takes care of itself. What is left are the exceptions the app couldn’t match, and those are what eat up your time. Thomas Werkhoven, managing director of Exsion365 and still a controller at heart, handles them in Excel.

The short version: let the bank app or Business Central’s own reconciliation do the matching, then handle the exceptions in Excel on live Business Central data. With Exsion Reporting you pull the Bank Account Ledger Entry table (271), the Cust. Ledger Entry table (21), the Vendor Ledger Entry table (25) and the G/L Entry table (17) into one workbook, refresh with one click, and see in seconds which payment landed on which customer and which invoices are still open. No export, no clicking through Business Central, and in my own case about 90% less time on the exceptions.

Where it gets stuck

The app already does the heavy lifting. The hassle is the one payment booked on a customer but not on the right invoice. Before you can fix it, you first have to find it: which customer, which invoice and where it went wrong. In Business Central that means clicking from screen to screen until you lose track. That’s why so many controllers reach for Excel.

The typical exceptions are always the same handful. A customer pays two invoices with one transfer and the amount matches neither. A vendor takes a discount you did not expect. A payment arrives without a reference, or with the reference of last month’s invoice. A bank charge is posted straight to the bank ledger with no counterpart on a customer or vendor. Each one takes a minute to fix once you can see it, and ten minutes to find if you cannot.

How does Business Central handle bank reconciliation itself?

Business Central has a Bank Account Reconciliation page where you import statement lines, apply automatic matching and post the result against the Bank Account Ledger Entry table. Microsoft describes the process in its documentation on reconciling bank accounts separately. Automatic matching works on amount, date and text, and Copilot can now suggest matches where the standard rules fall short.

That covers the majority of lines. What it does not give you is a single view across the four tables that explain an exception. The bank side lives in table 271, the customer side in table 21, the vendor side in table 25 and the accounting effect in table 17. In the client you open each one separately, filter by hand, and hold the picture in your head. That is exactly the part that belongs in Excel.

How I handle it in Exsion

Like most of our customers, I use a bank app that reconciles automatically. What is left is that exception. I open the customer entry in Excel and see straight away how the payment was split and which invoices are still open. No clicking through Business Central, no export. In my case that saves about 90% of the time it used to take me. Fewer steps, fewer mistakes, and room for the real question: why did it not match?

The workbook behind that is simple. One query on Cust. Ledger Entry (21) filtered on Open = Yes, so I see every unapplied customer payment and every unpaid invoice with its remaining amount. A second query on Vendor Ledger Entry (25) with the same filter for the payables side. A third on Bank Account Ledger Entry (271) for the period, so I can compare what the bank app posted against what the statement says. Exsion writes the definition into the workbook as a readable grid: table, companies, fields, filters and joins, so any licensed colleague can open the file and see exactly what it pulls.

Joining the bank line to the customer entry

The useful trick is the join. Add Cust. Ledger Entry to the Bank Account Ledger Entry query and Exsion proposes the key; one outer join later, every bank line shows the customer entry it created, or an empty cell if it created none. The empty cells are the exceptions. For payments that were applied to the wrong invoice, the Detailed Cust. Ledg. Entry table (379) shows the application itself, so you can see which invoice absorbed the money and what the remaining amount is on each side.

Checking the G/L side

The last check is the G/L Entry table (17). A filter on the bank account’s G/L account and the statement period gives the total posted to the bank in the ledger. If it does not equal the closing balance on the statement, the difference is the size of your exception list before you have even opened it. For a one-line check I use the live account balance function with Business Central’s own filter syntax, for example a date filter of 01-01-26..31-01-26 on the bank G/L account, and refresh it alongside the detail.

Why not just export from Business Central?

You can. Every list page in Business Central has an Open in Excel action, and Microsoft documents it on the export data page. The problem is that an export is a snapshot. Fix one application in Business Central and the export is already wrong, so you export again, re-filter, and re-check. During a close that happens five or six times per bank account.

A live query does not have that problem. Fix the application, click refresh, and the exception disappears from the list. The Show Details drill-down works the other way round as well: from any cell in the workbook it opens Business Central filtered to the exact account and date range behind that number, so you go straight to the entry you need to correct. Nothing is copied, nothing goes stale, and the permissions are the ones you already have in Business Central.

What the month-end exception routine looks like

[[TABLE_START]]

Step | Business Central table | What you check | Typical time

1. Statement versus ledger | Bank Account Ledger Entry (271) | Posted bank entries against the statement closing balance | Under 1 minute

2. Unapplied receipts | Cust. Ledger Entry (21) | Open payments and open invoices per customer | 2 to 5 minutes

3. Unapplied payments | Vendor Ledger Entry (25) | Open payments and open invoices per vendor | 2 to 5 minutes

4. Wrong applications | Detailed Cust. Ledg. Entry (379) | Which invoice absorbed the payment and the remaining amount | 1 to 2 minutes per case

5. Balance proof | G/L Entry (17) | Bank G/L account balance for the period equals the statement | Under 1 minute

[[TABLE_END]]

The times are for the check itself, after a one-time setup of the workbook. Building the queries takes an afternoon the first time and never again; the same file works for every month and, if you tick more companies in the grid, for every entity in the group with a company column added to the output.

Does this scale beyond one bank account?

Yes, and that is where the approach pays off most. A group with several entities and several bank accounts per entity has the same exception types multiplied by the number of accounts. Exsion pulls all companies in one query, so one refresh gives the full exception list across the group, sorted by company and bank account. Row volume is not the constraint: 700,000 G/L Entry rows load in under five seconds, and Excel stays responsive while the refresh runs.

N+P Group, with 45 legal entities and their own bank accounts, uses exactly this pattern. The team downloads transactional data with Exsion and performs the reconciliation checks in Excel, and is moving the matching itself into Business Central so that Exsion becomes the review layer. You can read how N+P reports across 45 entities without a single export.

See it with your own figures

Want to see where that 10% in your books goes wrong? Book a meeting with an expert. Together we set up your bank reconciliation overview, so you see straight away how it works for the one payment that does not match. More than 20,000 users already work with their Business Central data this way.

If you would rather try it yourself first, Exsion Reporting installs from AppSource in about five minutes and comes with a 30-day full trial; the trial licence key arrives by email within about a minute. Pricing is public on the pricing page. For the wider general ledger checks that sit next to the bank reconciliation, see general ledger checks without the hassle.

Talk to an expert >>>

Frequently asked questions

[[FAQ_START]]

Which Business Central tables do I need for bank reconciliation exceptions? | Four tables cover almost every case: Bank Account Ledger Entry (271) for the bank side, Cust. Ledger Entry (21) and Vendor Ledger Entry (25) for open receivables and payables, and G/L Entry (17) for the balance proof. Detailed Cust. Ledg. Entry (379) shows how a payment was applied when you need to trace a wrong application.

Does Exsion replace the bank reconciliation in Business Central? | No. Matching and posting stay in Business Central or in your bank app. Exsion gives you a live Excel view of the exceptions those tools could not match, so you can find and fix them faster.

Do I need to export data from Business Central every time? | No. Exsion reads the tables live through the Business Central API, so you refresh the workbook with one click instead of exporting again after every correction.

How much time does this save on the exceptions? | In Thomas Werkhoven’s own close it saves about 90% of the time the exceptions used to take, because finding the entry no longer means clicking through several Business Central pages.

Can I use the same workbook for several companies? | Yes. Tick the companies in the query grid and the result gets a company column, so one refresh gives the exception list for every entity and bank account in the group.

[[FAQ_END]]