The Excel Row Limit Is Not Your Limit: 25 Million Business Central Rows in One Pivot

Excel has 1,048,576 rows. That number has not moved since Excel 2007 and it is not going to. For most Business Central reporting it is plenty. For a posted time entry table, a G/L entry table with ten years of history or an item ledger at a distributor, it is a wall.

The short answer

The Excel row limit applies to a worksheet, not to Excel. The Excel data model (the Power Pivot engine behind a PivotTable) is bound by memory and file size rather than by row count, which Microsoft confirms in its Excel specifications and limits. Exsion Reporting can write a Business Central query directly into that data model, with no rows on any worksheet. We have loaded 25 million Business Central rows into one PivotTable this way. The file stays small, the refresh is one click, and double-clicking a pivot cell still shows the underlying entries.

The rest of this article explains where the wall sits in Business Central, what people do today to get around it, and when a pivot is the right answer and when it is not.

Where the wall actually is in Business Central

Business Central stores almost everything at entry level. It does not keep a monthly summary table that a report can read from; the balance you see on a G/L account is a flowfield that sums the entries behind it every time you open the page. That design is good for auditability and bad for anyone who wants to put the whole history on one sheet.

The five tables that cause the trouble

A handful of tables carry most of the volume in a typical tenant.

[[TABLE_START]]

Table | Number | What fills it up

G/L Entry | 17 | Every posted journal line, invoice line and adjustment, for every company and every year since go-live

Cust. Ledger Entry | 21 | One entry per customer document (invoice, payment, credit memo) plus the detailed entries behind it in table 379

Item Ledger Entry | 32 | Every receipt, shipment, transfer and adjustment for every item and location

Value Entry | 5802 | Cost and revenue postings per item ledger entry, often several per movement

Job Ledger Entry | 169 | Every hour, expense and item posted to a project; the time and materials table

[[TABLE_END]]

A manufacturer with 20,000 SKUs and daily movements across a few warehouses will pass a million Item Ledger Entry rows in a couple of years. Value Entry grows faster still because one shipment can create several value entries once expected cost, actual cost and adjustments are posted. A group with eight companies and a decade of history in G/L Entry is well past the limit before anyone opens Excel.

Microsoft's own guidance on exporting Business Central data to Excel is built around Open in Excel and Edit in Excel, which are fine for a page of data and were never meant to be a reporting layer for tables this size.

A consulting firm and 1,500 time entries a week

In a recent demo we showed Exsion365 to a US consulting firm that bills time and materials. Every consultant logs time, roughly 1,500 entries a week across the team, and it has been piling up for years. Their finance lead described the report that slices those entries by resource, customer, project and ticket as one of the slowest things he runs in Jet Reports. Not because the query is complicated. Because the volume is.

That report needs entry-level detail because the dimensions it slices by live on the individual entry. Summarise the table before it reaches Excel and you lose the ability to answer the next question, which is always "which tickets on that project?"

How worksheet-based tools handle it today

Most Excel reporting tools for Business Central, Jet Reports included, put rows on a worksheet. In Jet the NL function returns a list of records into cells; the cells are the data. Once the result set passes a million rows there are three ways to cope, and every finance team we speak to has used at least two of them.

The first is to filter or summarise before load. Limit the date range to the current year, or group by account and period so the report returns totals rather than entries. This keeps the file small but it throws away the detail, and it means the historical comparison lives in a different report with a different filter.

The second is to split the report across files, one per year or one per company. This is how "Timesheets 2019 to 2023 FINAL v4" gets born. Each file is under the limit, nobody can see the whole picture in one place, and consolidating the pieces means another workbook with links to all of them.

The third is to keep pulling the whole thing to a sheet and accept the consequences: long refreshes, a workbook that cannot be emailed, and Excel occasionally giving up part way through.

The second cost: file size

Rows on a worksheet are expensive. A workbook holding one million rows of ledger data with a dozen columns typically comes in at 30 to 40 MB. That file will not go through most email gateways, takes minutes to open, and is slow to save. People start keeping it on one machine and sending screenshots of it instead, which defeats the purpose of having the data in Excel at all.

Three ways to report on a multi-million-row table

Here is how the three approaches compare on the things that matter to a controller.

[[TABLE_START]]

Approach | Detail kept | File size | Refresh effort | Who can drill

Filter or summarise before load | Partial; totals only, or one period at a time | Small | One refresh per report, but several reports to cover the history | Nobody; the detail was never loaded

Worksheet split across files | Full, but scattered across workbooks | 30 to 40 MB per million rows, per file | One refresh per file, then update the consolidating workbook | Anyone with the right file open, if it opens

Direct to PivotTable data model | Full, in one place, up to tens of millions of rows | Typically 6 to 7 MB for a workbook that would be 30 to 40 MB on a worksheet | One refresh for the whole set | Anyone who can open the file; double-click a cell to see the rows

[[TABLE_END]]

Direct to PivotTable: how it works in Exsion

Exsion Reporting connects to Business Central through the API and lets you build a query against any table and field, including extension tables and flowfields. When you run the query (Exsion calls it a download) you choose where the result goes. The usual choice is a worksheet. The other choice is direct to a PivotTable, which writes the result into the Excel data model and skips the worksheet entirely.

With no rows on a sheet, the worksheet row limit stops being relevant. The practical ceiling becomes the memory on the machine and the size of the file, and both are far higher. The largest set we have loaded this way is 25 million Business Central rows into a single pivot. A pivot-only workbook that would have been 30 to 40 MB as a worksheet typically comes in at 6 to 7 MB, because the data model compresses columnar data far better than cells do.

Building the query

The approach that works is to build the query wide. Include every field you might want to slice by later: posting date, document number, all the global and shortcut dimensions, resource, job number, job task, customer, unit of measure, quantity, cost and price. In the consulting firm example that means Job Ledger Entry (table 169) with resource, customer, job, job task and the ticket reference they store in a dimension. Adding a field costs almost nothing in the data model; going back to add one later means another refresh, so it is better to be generous up front.

The query definition is written into the workbook as a readable grid: table, companies, fields, filters and joins. If you need customer name next to the customer number, you add the Customer table, accept the join Exsion proposes on "Customer No." to "No.", and pick the field; how to join Business Central tables in Excel walks through that in detail. If you need multiple companies in one pivot, tick them in the grid and the result includes a company column.

Drill down still works

The common objection to pivots is that you lose sight of the rows. You do not. Double-click any value cell in the pivot and Excel's Show Details opens a new sheet with the entries behind that number. The one condition is that the subset has to fit on a worksheet, so it needs to be under about a million rows. Drilling into one project for one quarter is fine; drilling into the grand total of a 25 million row set is not, and Excel will tell you so.

If you need to go back to Business Central itself rather than to the rows, the drill down guide covers Show Details on Exsion query results and financial functions, which opens Business Central filtered to exactly the account and date range the cell used.

One file, one refresh, three audiences

The consulting firm's report ends up as a single workbook with one download and one pivot. The finance lead looks at hours by resource. The delivery manager drags job onto rows and looks at hours and cost by project and ticket. The CEO collapses everything to customer and looks at revenue and margin. Nobody rebuilds anything; they rearrange the same pivot. When the month closes, one person refreshes and everybody's view updates.

Because the pivot is named after the download, it refreshes automatically when the download refreshes. Put two or three downloads, their pivots and a couple of pivot charts on a summary tab and you have a management dashboard that updates with one click, which is the pattern described in how to build Business Central management reports in Excel.

What refresh looks like without a warehouse

Refresh is a button. Exsion reads Business Central directly through the API using the permissions of the signed-in user, so there is no data warehouse, no Azure SQL replica, no VM, no gateway and no nightly load to schedule or to explain when it fails. The data in the pivot is as current as the moment you clicked.

Speed holds up at volume because Exsion pulls sets rather than single values. One query returns every entry in scope in one request and Excel does the arithmetic. As a reference point, 700,000 rows from G/L Entry (table 17) land in under five seconds, and Excel stays responsive during a refresh. If slow reports are the reason you found this article, why Jet Reports gets slow and Excel freezes goes into the difference between a request per function and a request per query.

Licensing follows the same logic. Anyone with an Exsion licence can refresh. Anyone without one can open the workbook, move the pivot fields around, change filters and drill into detail; they simply cannot pull new data. For a finance team that means the two people who close the books hold licences and the twenty people who read the reports do not.

When to use a pivot and when to keep the worksheet

Direct to PivotTable is the right choice for large entry tables that you want to slice several ways, and for any file that travels by email or SharePoint. Time entries, item movements, value entries, multi-year G/L history and anything multi-company are the obvious candidates.

A worksheet is still the right choice when you need the rows as rows. Reconciliations that match entries against a bank file, lookups that pull a value into another sheet, or a working paper where you add a column of formulas and comments all want the data in cells. Under a million rows the worksheet output lands in seconds anyway, so the row limit only decides the question when you are past it.

Pivots also have a limit of their own that is worth knowing: they aggregate. If your question is "show me every entry that meets these criteria" rather than "total these entries by these dimensions", filter the query in the grid and send the result to a worksheet. Exsion's grid accepts Business Central's own filter syntax, so a posting date filter of 01-01-26..31-03-26 or an account filter of 4000..4999|6100 works the way it does inside Business Central.

For the wider comparison of tools and where each one fits, the pillar article on Jet Reports alternatives for Business Central puts the row limit next to the other reasons finance teams move.

Try it on your biggest table

Install Exsion Reporting from AppSource through Extension Management in Business Central, download the Excel add-in from the Exsion info page, add a connection in Excel and sign in. The whole install takes about five minutes; the trial licence arrives by email within a minute of requesting it. The 30-day trial is full-featured and the pricing is on the page rather than behind a sales call.

Then pick your largest entry table, build the query wide, choose PivotTable output and refresh. If your team is coming from Jet Reports, we will build that first report with you free of charge, starting from the tables and fields your existing Jet workbook already uses.

Frequently asked questions

[[FAQ_START]]

What is the Excel row limit and does it apply to PivotTables? | An Excel worksheet holds 1,048,576 rows by 16,384 columns. That limit applies to cells on a sheet, not to the Excel data model that powers a PivotTable. The data model is limited by available memory and file size, so a pivot can hold many millions of rows as long as the data is loaded into the model rather than onto a worksheet.

Which Business Central tables are most likely to exceed one million rows? | The entry tables: G/L Entry (17), Cust. Ledger Entry (21) and its detailed entries in table 379, Item Ledger Entry (32), Value Entry (5802) and Job Ledger Entry (169). Any company with several years of history, multiple companies in one tenant, or high transaction volume in inventory or projects will pass the limit in one or more of these.

How many Business Central rows can Exsion load into one PivotTable? | Exsion has loaded 25 million Business Central rows into a single PivotTable using the direct to PivotTable output. The practical ceiling is set by the memory on the machine rather than by Excel. A pivot-only workbook is typically 6 to 7 MB where the same data on a worksheet would be 30 to 40 MB per million rows.

Can I still see the individual entries behind a pivot number? | Yes. Double-click a value cell in the pivot and Excel's Show Details opens a sheet with the underlying rows. The subset has to fit on a worksheet, so it needs to be under about a million rows. Drilling into a project, a customer or a period is fine; drilling into the grand total of a very large set is not.

Do I need a data warehouse or Power BI to report on this much data? | No. Exsion reads Business Central directly through the API with the signed-in user's permissions. There is no Azure SQL replica, no VM, no gateway and no scheduled load. Refresh is a button in Excel and the data is current as of the moment you click it.

Does everyone who opens the workbook need an Exsion licence? | Only the people who refresh. Anyone without a licence can open a pivot-only workbook, rearrange the pivot, change filters and drill into detail. Refreshing with new data from Business Central requires a licence.

[[FAQ_END]]