General ledger checks in Excel without the hassle

A general ledger check in Business Central does not have to start with an export. You can run the whole check in Excel on live Business Central data: put this month next to last month, budget and last year, reconcile the customer, vendor and bank subledgers to their general ledger control accounts, flag every difference with conditional formatting, and drill from a suspicious cell straight to the posting. When a late entry comes in, you refresh instead of exporting again. In this post, Thomas Werkhoven, director of Exsion365 and still a controller at heart, shows how he checks his general ledger in Excel, without the hassle.

Where does the general ledger check get stuck?

Every controller does it: running through the general ledger before the close. You want to know whether entries sit on the right account, whether an account suddenly comes out much higher than last month, and whether anything is off. Not complicated work in itself, but it easily costs you a morning.

The problem is not the checking itself. It is the gathering. You export a ledger overview, paste it into Excel, pull up last month's figures and line them up. The moment an entry comes in, you can start again. So you spend more time setting the figures up than looking at what is actually going on.

In Business Central itself you can view ledger entries, but that means clicking from account to entries to the underlying postings, screen after screen. Putting two periods side by side or comparing several companies is not straightforward. That is why many controllers reach for Excel; the only downside of the old way is that the figures arrive there by hand and go stale quickly.

Which subledgers do you reconcile to the general ledger?

A ledger review has two layers: the figure review per account (does the balance make sense against last month, budget and last year?) and the subledger reconciliation (do the detail tables add up to the control accounts?). Business Central keeps both layers in separate tables, and a good check reads them side by side.

[[TABLE_START]]

Check | Business Central detail table | General ledger counterpart | What you compare

Receivables | Cust. Ledger Entry (21) and Detailed Cust. Ledg. Entry (379) | G/L Account (15), receivables account per customer posting group | Sum of remaining amounts per posting group versus account balance at date

Payables | Vendor Ledger Entry (25) | G/L Account (15), payables account per vendor posting group | Sum of open vendor entries versus account balance at date

Bank | Bank Account Ledger Entry (271) | G/L Entry (17) on the bank G/L account | Bank ledger balance versus G/L balance per bank account

Figure review | G/L Entry (17) and G/L Budget Entry (96) | G/L Account (15) net change per period | This month versus last month, budget and last year per account

[[TABLE_END]]

One Business Central detail matters here. The amounts on a customer ledger entry are flowfields that sum the Detailed Cust. Ledg. Entry table (379), where every application, discount and exchange rate adjustment lives. If you only pull table 21, a payment applied yesterday can be missing from your reconciliation. Pull 379 when you want the reconciliation to match to the cent.

The classic cause of a difference is a direct posting to a control account. Someone posts a journal line straight to the receivables account without a customer, the general ledger moves, the subledger does not, and the difference sits there until year-end. With the two tables next to each other in Excel, you see it the same day.

How do you run a general ledger check in Excel on live Business Central data?

With Exsion Reporting you pull the Business Central tables straight into Excel through the API, with your own Business Central permissions. No export step, no data warehouse, no Power BI licence. These are the steps I follow.

Step 1: pull G/L Entry per account and period

Start with G/L Entry (17), grouped by G/L account and posting date, filtered to the period you are closing. Exsion writes the query definition into the workbook as a readable grid (table, companies, fields, filters), so a colleague can see what is behind the numbers. Volume is not a concern: 700,000 G/L Entry rows load in under five seconds.

Step 2: put the periods side by side

Next to the current month I place last month, last year and the budget from G/L Budget Entry (96). If you prefer formulas over a data pull, Exsion offers 16 live financial functions, such as account balance and account name, that accept Business Central's own filter syntax: 01-01-26..31-03-26 for a date range, 4000..4999|6100 for an account range, as described in Microsoft's filter criteria guide. Keep live formulas under roughly 40,000 to 50,000 per workbook; above that a data pull into a pivot is faster.

Step 3: reconcile the subledgers

On a second sheet, pull Detailed Cust. Ledg. Entry (379) summed per customer posting group, Vendor Ledger Entry (25) summed per vendor posting group and Bank Account Ledger Entry (271) summed per bank account. Next to each total, place the balance at date of the matching control account from G/L Account (15). The difference column should be zero on every line. For the bank side, the bank reconciliation in Excel post walks through the same setup in more depth.

Step 4: let conditional formatting do the first pass

Conditional formatting flags any deviation that does not make sense straight away. I colour a cell when the month deviates more than a set percentage from last month or from the budget, and when a reconciliation difference is not zero. So I can see at a glance whether this month's depreciation is booked, whether an accrual is right, or whether an amount suddenly steps out of line.

Step 5: drill down to the entry

If something stands out, I drill down to the underlying posting, all the way to the invoice. Show Details opens Business Central filtered to the exact account and date range behind the cell, and a double-click on a pivot value shows the underlying rows in Excel. There is a six-step guide to drill-down if you want to set that up properly.

Step 6: refresh instead of re-export

If something changes in Business Central, I refresh with one click and everything is up to date again. The comparison, the conditional formatting and the reconciliation sheet all recalculate. When the check is finished, Formulas to Values turns the workbook into static numbers for the audit file.

How I check my general ledger in Exsion

The main check for me is the figure review. I put this month next to last month, the budget and last year. In Exsion I pick the new period, press refresh, and exactly those comparisons are ready. No export, no pasting.

After that I run through a few balance sheet and suspense accounts. Does the payroll tax I pay this month match last month's return? Do the bank balances reconcile? Are the receivables and payables control accounts equal to their subledgers? Because the workbook is already built, the whole routine takes minutes rather than a morning.

What does a general ledger check on live data give you?

A concrete example: a tax entry that ended up on the wrong general ledger account. I spotted it straight away in the overview, because the account differed from the month before. Without that comparison, I would only have found it once the figures were already out.

I also set up a few checks before the ledger check itself. Are all banks up to date, are all contracts invoiced? A simple marker, a smiley for instance, shows me whether my administration is in order before I start.

The biggest gain is in failure costs. You get the report right once, in the right order, instead of fixing things afterwards. You check instead of gather, and you keep time for the question that really matters: why does this differ?

[[TABLE_START]]

Part of the check | Export and paste | Live in Excel with Exsion

Gathering the figures | Export per account or period, paste, line up with last month | One refresh, comparisons already in place

Subledger reconciliation | Separate exports of customer, vendor and bank entries | Tables 379, 25 and 271 next to G/L Account 15 in one sheet

A late entry arrives | Start the export again | Refresh, differences recalculate

Finding the cause | Search in Business Central screen by screen | Drill down from the cell to the posting

Several companies | One export per company | Tick the companies, get a company column

[[TABLE_END]]

Does the check change at year-end?

The routine is the same at year-end, only the tolerance is smaller. Every reconciliation difference has to be explained or corrected before the auditor sees it, so the subledger sheet gets the most attention. Because the workbook already holds twelve months, the year-end check is mostly a matter of extending the date filter and pressing refresh.

Groups with several Business Central companies run the same workbook across all of them: tick the companies in the query grid, and the result gets a company column, so you check the control accounts of every entity in one view. If you consolidate as well, the guide on consolidating multiple companies in Business Central using Excel builds on exactly this setup.

Try it yourself

Want to see how this works for your own general ledger checks? Exsion installs from AppSource in about five minutes, and the 30-day full trial includes every function. We build your first report together, so you see straight away how it works for your setup. More than 20,000 users already work with their Business Central data this way. Pricing is public on the pricing page, and you can book a demo through the contact page.

Frequently asked questions

[[FAQ_START]]

What is a general ledger check in Business Central? | It is the review a controller runs before the close: every account is compared with last month, budget and last year, and the customer, vendor and bank subledgers are reconciled to their control accounts in the general ledger. In Excel on live data the whole check runs from one workbook.

Which Business Central tables do I need for a subledger reconciliation? | Use Detailed Cust. Ledg. Entry (379) for receivables, Vendor Ledger Entry (25) for payables and Bank Account Ledger Entry (271) for bank balances, and compare each total with the control account balance in G/L Account (15) or the entries in G/L Entry (17).

Why does my receivables subledger not match the general ledger? | The most common cause is a journal line posted directly to the receivables control account without a customer, so the general ledger moves while the subledger does not. Placing table 379 next to G/L Account 15 in Excel shows the difference per posting group.

Can I drill down from Excel to the posting in Business Central? | Yes. Show Details opens Business Central filtered to the exact account and date range behind a cell, and a double-click on a pivot value lists the underlying rows in Excel, as long as the subset stays under about a million rows.

Do I need Power BI or a data warehouse for this? | No. Exsion Reporting reads Business Central directly through the API with your own user permissions, so there is no data warehouse, no Azure SQL replica and no Power BI licence involved. It installs from AppSource in about five minutes.

[[FAQ_END]]