Building Excel-Native Reports for Microsoft Dynamics 365 Business Central: A Guide for CFOs and Controllers

Excel-native reporting for Microsoft Dynamics 365 Business Central means analyzing your data inside a live Excel workbook that queries Business Central directly through an add-in: you refresh the same report with one click, the numbers update in place with formulas and formatting intact, and nothing is ever exported. It differs from opening a list in Excel or exporting a financial statement, both of which produce a snapshot that is stale as soon as the next transaction posts. This guide explains what qualifies as Excel-native, how the connection works, and how CFOs and controllers move their first reports across.

What is Excel-native reporting for Business Central?

Ask a group of controllers how to set up Excel-based reports from Microsoft Dynamics 365 Business Central and you will get different answers. One might mean exporting a static list to a spreadsheet, another might point to the built-in financial report templates, and a third might mean something else entirely: a live, refreshable connection between Excel and their ERP that never requires an export in the first place.

That third definition is the ideal, and it is worth distinguishing from the other two before you build your next report. Excel-native reporting means the query lives in the workbook and the add-in pulls current figures from Business Central every time you refresh. Instead of exporting a snapshot and rebuilding it every month, you refresh the same report and the numbers update in place. Your Business Central data remains untouched throughout.

Why does the distinction matter?

Business Central already offers several ways to get data into Excel. You can open a list page in Excel, export a financial statement using Create New Excel Template, or build reports on OData feeds through Power Query. Microsoft's own documentation on integrating financial reports with Excel covers this workflow well, and for a single monthly statement it works fine.

The trouble starts at scale. Every one of these methods produces a snapshot. The moment a new transaction posts, that snapshot is stale, and someone has to rerun the export, reopen the workbook and reapply the formatting by hand. Limelight Software's 2026 FP&A Statistics Report found that nearly all finance professionals still rely on spreadsheets for planning, yet 82 percent say they are making decisions on data that is already out of date by the time they look at it. That gap between believing your numbers are current and actually having current numbers is exactly what Excel-native reporting closes.

[[TABLE_START]]

Method | Live or snapshot | Formulas survive refresh | Permissions | IT involvement

Open list page in Excel | Snapshot | No, new sheet each time | Business Central user | None

Create New Excel Template (financial reports) | Snapshot | Partly, template based | Business Central user | None

OData feed through Power Query | Refreshable copy | Yes, but query maintained separately | Web service setup | Some

Power BI or BI platform | Scheduled model refresh | Not Excel formulas | Separate security model | Data model and licences

Excel-native add-in (Exsion Reporting) | Live query on refresh | Yes, structure stays put | Inherited from Business Central | About 5 minutes to install

[[TABLE_END]]

What should you look for in Excel-native reports?

Three things separate an Excel-native reporting tool from a one-time export.

The connection is live, not static. You are not waiting on a scheduled batch job or a data warehouse refresh. When you press refresh, Excel queries Business Central directly and pulls whatever is posted as of that moment. With Exsion Reporting, 700,000 G/L Entry rows load in under five seconds and Excel stays responsive while it runs.

Your formulas and layout survive the refresh. A standard export drops raw data into a new sheet, meaning every pivot table, conditional format and function has to be reapplied. In an Excel-native setup the report structure stays put and only the underlying figures change.

Security is retained at the user level. Because the connection authenticates against your existing Business Central credentials, a controller only sees what their permissions allow. Nobody needs a separate export process to keep sensitive entities or companies out of the wrong hands, and there is no second security model to maintain.

This differs from opening a Business Central list in Excel, which is a one-time snapshot, and from a full business intelligence platform, which usually requires heavy IT involvement to define models or update reports. Excel-native reporting sits between the two: live data, but inside the spreadsheet environment finance teams already know. Exsion's own guide to choosing a Business Central Excel reporting tool walks through the same evaluation from a finance team's perspective, and the Power BI or Excel comparison covers when a BI platform is the better choice.

How does the Business Central connection work?

Setting one up is simpler than most controllers expect. The Excel add-in installs from AppSource through Extension Management in about five minutes, alongside your existing Microsoft 365 licence, and authenticates with your Business Central credentials. From there, the available tables and fields show up directly in the Excel ribbon: any Business Central table, including extension tables and flowfields such as Net Change on G/L Account (table 15). You pick the tables you need and build the layout the way you already build any Excel report. No data warehouse, no Azure SQL replica, no VM and no gateway are involved.

Once the structure is in place, refreshing takes seconds. Pull this month's numbers from G/L Entry (table 17) next to last month's, the budget from G/L Budget Entry (table 96) and the same period last year, and every comparison updates together with a single click. Filters use Business Central's own syntax, so 01-01-26..31-03-26 on Posting Date or 4000..4999|6100 on the account work exactly as they do in Business Central. The query definition is written into the workbook as a readable grid, so any licensed colleague can see and edit which table, companies, fields and filters produced a figure.

If a number looks off, Show Details opens Business Central filtered to the exact account and date range behind the cell, and a double-click in a pivot shows the underlying rows without leaving the workbook. That kind of traceability is worth building into your process before month-end, not during it. Exsion walks through this exact workflow in its guide to building management reports in Excel in seven steps, the drill-down guide covers the audit trail, and a companion post covers running general ledger checks the same way.

What changes in a finance team's day-to-day?

The most immediate change is where time goes. Data errors and reconciliation problems consume a real chunk of the month for most finance teams, and a lot of that time traces back to manual handling rather than the underlying accounting itself. When the connection to Business Central is live, that gathering step mostly disappears. What is left is the part of the job that requires judgment: explaining a variance, flagging an account that looks wrong, or deciding what leadership needs to see next. AASK, an Exsion customer, saved over 20 hours per month this way.

There is a second, quieter benefit. Because Excel-native reports pull from the same permissioned data every time, month-over-month comparisons stay consistent. Nobody is reconciling one analyst's version of the export against another's; everyone is looking at the same live source, through their own worksheet.

For organizations running more than one Business Central company, this also opens up group reporting without extra tooling. Tick the companies in the grid and the result gets a company column, so reports that pull from multiple entities, currencies or environments sit in a single workbook rather than a stack of separate files someone has to merge by hand. Where the group needs intercompany eliminations and currency translation, Exsion Corporate tends to come in; N+P Group reports across 45 entities this way.

How do you get started with Excel-native reporting?

You do not need to replace your existing templates to move to Excel-native reporting. Most teams start by converting one or two high-value reports, usually the ones rebuilt most often, such as a monthly P&L or a receivables aging report built on Cust. Ledger Entry (table 21), and expand from there once the workflow proves out.

Exsion Reporting is built for this kind of gradual adoption: connect to Business Central or Dynamics NAV and swap static data for a live feed without relearning Excel. The 30-day trial is the full product, the licence key arrives by email within about a minute, and plans and setup details are on the pricing page.

For teams evaluating this shift, the most useful next step is converting the single report that eats the most time each month and seeing how the refresh actually feels in practice, before making any wider decision about tooling.

Frequently asked questions

[[FAQ_START]]

What makes a report Excel-native instead of just an export to Excel? | A live, refreshable connection to Business Central that updates in place, not a static snapshot you have to rebuild each time. The query lives in the workbook and runs against Business Central every time you refresh.

Do I lose my formulas and formatting when I refresh? | No. In an Excel-native setup the report structure stays intact and only the underlying figures update, so pivots, conditional formats and functions carry over from month to month.

Will I see data I do not have permission to access? | No. The connection authenticates against your existing Business Central credentials, so you only see what your permissions allow, and there is no separate security model to maintain.

Do I need to rebuild all my reports at once to switch? | No. Most teams start with one or two high-value reports, like a monthly P&L, and expand from there once the workflow proves out.

Can this handle reporting across multiple Business Central companies? | Yes. Tick the companies in the grid and data from multiple entities, currencies or environments sits in a single workbook with a company column, instead of separate files merged by hand. Full consolidation with eliminations is handled by Exsion Corporate.

[[FAQ_END]]