Excel's Row Limit: What Happens at 1,048,576 Rows — and How to Load a 3-Million-Row GL Anyway

Guide · Updated July 2026 · ~13 min read

A modern Excel worksheet holds 1,048,576 rows and 16,384 columns. That is the hard ceiling in .xlsx, .xlsm and .xlsb, it is per worksheet rather than per workbook, and no setting, licence or plan raises it. The old .xls format stops far earlier, at 65,536 rows.

Nobody meets that ceiling doing budgets. You meet it the day you export a full-year general ledger, three years of point-of-sale transactions, or a payment gateway's raw settlement file — and the export comes back at exactly 1,048,576 rows, which is the number a file returns when it has been cut off rather than finished.

This post covers the real limits, how to spot a truncated import before it reaches a reconciliation, and then the part the ceiling articles skip: a step-by-step route to load and summarise three million rows in Excel without a single one of them landing on the grid.

The numbers, and why they are those numbers

Both grid figures are powers of two: 2^20 rows and 2^14 columns. That is the whole explanation — the row number is a 20-bit field in the file format, so it is structural, not a throttle Microsoft could lift in an update.

Three myths worth correcting:

• .xlsb does not give you more rows. The binary format saves and opens faster and produces a smaller file, but it is the same 1,048,576-row grid.
• The limit is per worksheet, not per workbook. Two sheets give 2,097,152 rows of capacity across a boundary your formulas have to cross. Splitting a ledger over tabs makes a big file both big and awkward.
• 64-bit Excel does not raise it either. Bitness governs how much memory Excel can address, which matters enormously for the Data Model route below, but the grid is 1,048,576 rows in both.

Check your bitness now, because it decides which of the routes below is open to you: File > Account > About Excel, and read the end of the version string.

The trap: a truncated import looks exactly like a complete one

Open an oversized CSV directly and current Excel does warn you, once, with a dialog headed "File not loaded completely." (wording varies a little by version — as of 2026 that is what Microsoft 365 shows). Then you click OK, and that is the last you will ever hear about it. The workbook that opens has no banner, no flag, no marker cell. Save it, email it, and the recipient sees a perfectly ordinary ledger that happens to be missing the last two million transactions.

Worse, several common paths give no warning at all: a VBA Workbooks.Open truncates without a dialog when macros suppress alerts, and a scheduled script that opens, saves and closes the file logs a success. Build the check into the routine rather than trusting the dialog.

Four ways to detect it

1. Press Ctrl+End. It jumps to the last used cell. If that lands on row 1,048,576, treat the file as truncated until proved otherwise — a real export finishing on precisely the ceiling is a coincidence that essentially never happens.
2. Check the last row for a broken record. Truncation cuts mid-file, so the final row is frequently a partial line: a date with no amount, or a description running into the wrong column.
3. Reconcile the row count to a control total. Every accounting system will tell you how many transactions it exported, or the value of them. =COUNTA(A2:A1048576) against the source's record count, and =SUM(D:D) against the source's total debits, is thirty seconds that catches this every time.
4. Count the file's rows before opening it — the only method that works when the file is bigger than Excel.

Counting rows without opening the file

In PowerShell, streaming so it does not load the file into memory:

$n = 0
switch -File 'C:\data\gl-2025.csv' { default { $n++ } }
$n

Subtract one for the header row. Or from Command Prompt, the old reliable:

find /c /v "" "C:\data\gl-2025.csv"

If that comes back at three million and change, the grid was never going to hold it, and deleting columns will not help — columns are not the constraint.

The fix: Power Query into the Data Model

The Data Model is Excel's in-memory columnar engine, the thing Power Pivot sits on. It lives inside the workbook, it is not bound by the worksheet grid, and its documented ceiling is 1,999,999,997 rows per table — a figure Microsoft publishes in its data model specifications, worth re-checking against current docs before you plan around it. Data goes in through Power Query and comes out through a PivotTable. At no point does a row touch a cell.

The critical move is at step 3, and it is one button.

Step 1 — start the import. Data tab > Get Data > From File > From Text/CSV, pick the file, wait for the preview.

Step 2 — check the preview. The dialog shows the first 200 rows with a detected delimiter and encoding. If columns are misaligned, fix the Delimiter and File Origin dropdowns here, not later. Leave Data Type Detection on "Based on first 200 rows" for now.

Step 3 — click Transform Data, not Load. This is the whole trick. Load sends the result to a worksheet, which puts you straight back against the 1,048,576-row wall. Transform Data opens the Power Query Editor, where nothing is materialised. New to the editor, Power Query for beginners covers the interface properly.

Step 4 — cut the columns you do not need. In a columnar engine this is the biggest lever on memory, far bigger than filtering rows. Select the columns you want, right-click, Remove Other Columns. A GL export often ships a transaction GUID, an audit user, a memo field and a modified timestamp; if you are not grouping or filtering on them, drop them. The engine stores a dictionary of distinct values per column, so 3 million unique GUIDs cost far more than 3 million rows of a 40-value account code.

Step 5 — set data types explicitly. Click each column header's type icon and choose the real type: Date for dates, Decimal Number for debits and credits, Text for account codes (an account code stored as a number loses its leading zeros and stops matching your chart of accounts). Type errors surface here as a red "Error" cell rather than as a silently wrong total in month three.

Step 6 — filter if you genuinely can. Use the column dropdowns, e.g. restrict Date to the current financial year. One caveat the ceiling articles get wrong: query folding does not apply to a CSV. Folding is Power Query pushing filters back to a source that can execute them — SQL Server, ODBC, some OData feeds. A flat file has no engine to push work to, so every one of your 3 million rows is still read on every refresh. Filtering here saves memory in the model, not read time.

Step 7 — load to the model, not the sheet. On the Home tab, click the small arrow under Close & Load and choose Close & Load To…. In the dialog:

• Select Only Create Connection.
• Tick Add this data to the Data Model.
• Click OK.

Excel now streams the file into the model. Three million rows of typed GL takes roughly one to four minutes on a normal laptop; the status bar shows a rising row count. Nothing appears on any worksheet, which is the point.

If your Excel is 32-bit, this is where it may fail: a 32-bit process gets about 2 GB of virtual address space shared between Excel, the workbook, the model and every add-in, so the model's usable share is a fraction of that. Moving to 64-bit Excel is the fix, and it is a reinstall rather than a licence purchase.

Summarising three million rows with a PivotTable

Insert > PivotTable > From Data Model (older builds: Insert > PivotTable > Use an external data source > Choose Connection > Tables > This workbook's Data Model). The field list shows your query as a table.

Drag Account and Account Name to Rows, Date to Columns (Excel groups it into months automatically), and Debit and Credit to Values. That is a monthly trial balance over three million transactions, and it recalculates in about a second, because the aggregation happens in the model rather than in three million cells of formulas.

For anything beyond a straight sum, write a DAX measure. In the field list, right-click the table name and choose Add Measure:

Net Movement := SUM(GL[Debit]) - SUM(GL[Credit])
Transactions := COUNTROWS(GL)
Accounts Used := DISTINCTCOUNT(GL[Account])

DISTINCTCOUNT is worth singling out: a distinct count across three million rows is the classic query that makes a normal PivotTable crawl and a model PivotTable shrug.

For the Power Pivot window itself — to relate the GL to a chart-of-accounts or date table — use Data > Manage Data Model. If the Power Pivot tab is missing, enable it at File > Options > Add-ins > Manage: COM Add-ins > Go > Microsoft Power Pivot for Excel.

Refreshing it next month

Overwrite the CSV at the same path with next month's export, then Data > Refresh All. The query re-runs and the PivotTable follows.

Two realities to plan around. As of 2026 Excel's Data Model has no incremental refresh — that remains a Power BI feature — so every refresh re-reads the whole file and refresh time scales with the file, not with the new rows. And a workbook holding a loaded Data Model is large: expect a few hundred megabytes, and save as .xlsb if open times start to annoy. The PivotTable's own refresh quirks are covered in refreshing a PivotTable in Excel.

When splitting the file is the right answer

Splitting is right when several people work on different periods, when you hand slices to auditors, or when 32-bit Excel leaves you no model route. Split on a business boundary — month, entity, branch — never on an arbitrary row number, because a boundary you can name is a boundary you can reconcile. To cut a CSV into million-row parts, each keeping the header:

$src    = 'C:\data\gl-2025.csv'
$chunk  = 1000000
$header = Get-Content $src -TotalCount 1
$part   = 0
$buffer = [System.Collections.Generic.List[string]]::new()

Get-Content $src | Select-Object -Skip 1 | ForEach-Object {
    $buffer.Add($_)
    if ($buffer.Count -ge $chunk) {
        $part++
        Set-Content "C:\data\gl-part$part.csv" -Value (,$header + $buffer)
        $buffer.Clear()
    }
}
if ($buffer.Count) { $part++; Set-Content "C:\data\gl-part$part.csv" -Value (,$header + $buffer) }

Allow a few minutes on a 3-million-row file. To reassemble the pieces for reporting, do not copy-paste: drop them in one folder and use Data > Get Data > From File > From Folder, which appends every file and picks up new ones on refresh — the same pattern that rescues Zoho Books exports capped at 25,000 rows.

When Python is the honest answer

If the job is "read three million rows, aggregate them, give me a summary", and the summary is what you actually need in Excel, then loading the detail into Excel at all is ceremony. Pandas reads the file in chunks so memory stays flat regardless of size:

import pandas as pd

cols = ["Date", "Account", "AccountName", "Debit", "Credit"]
parts = []

for chunk in pd.read_csv("gl-2025.csv", usecols=cols, chunksize=500_000,
                         parse_dates=["Date"]):
    chunk["Period"] = chunk["Date"].dt.to_period("M")
    parts.append(chunk.groupby(["Period", "Account", "AccountName"],
                               as_index=False)[["Debit", "Credit"]].sum())

summary = (pd.concat(parts)
             .groupby(["Period", "Account", "AccountName"], as_index=False)[["Debit", "Credit"]].sum())

summary["Net"] = summary["Debit"] - summary["Credit"]
summary["Period"] = summary["Period"].astype(str)
summary.to_excel("gl-summary.xlsx", index=False)

The aggregate-then-concatenate-then-aggregate shape matters: each chunk is collapsed to account-month totals before it is kept, so peak memory is governed by chunksize, not by file size. A few thousand summary rows land in Excel, well inside the grid, and the detail stays in the file. Choose Python when the file is enormous, the transformation is complex, or it has to run unattended; choose Power Query when the audience must refresh it themselves without installing anything.

How HISAB 360 helps

Two of the three problems above are ones an add-in can take off your hands. HISAB 360 is a paid Excel add-in with an AI chat panel docked inside the workbook, and it runs a bundled Python runtime — pandas included — inside Excel, so the chunked aggregation above runs from a plain-English request against a file on your disk, writes the summary into a sheet, and needs no separate Python install. It also builds Power Query queries from a description: ask for the CSV loaded to the Data Model with types set and unused columns dropped, and it writes the query rather than handing you steps to click through.

The more useful answer is upstream. HISAB connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting and pulls the general ledger, invoices and bills into sheets directly, filtered to the period and accounts you asked for — so the three-million-row CSV never gets created, and neither does the manual export that produced it. Getting the ledger out cleanly is a job in itself; see the QuickBooks general ledger route for what it takes by hand.

Honest limits: Windows desktop Excel only, Microsoft 365 or 2016+, not Mac and not Excel on the web, and it is paid, with a 15-day trial and no card required. It cannot repeal the row limit either — nothing can. What it changes is how often you meet it.

The practical ceiling is lower than the hard one

Worth saying plainly: the grid stops being pleasant long before 1,048,576. Around 300,000 to 500,000 rows with live formulas — SUMIFS, VLOOKUP, array formulas across whole columns — you get multi-second recalculations, slow scrolling and a file that takes a minute to open. Volatile functions (OFFSET, INDIRECT, TODAY) make it worse, because they recalculate on every change anywhere in the workbook. If a working file is heading that way, move the detail to the Data Model before you hit the wall, not after — much the same conclusion automating finance work in Excel arrives at from the other direction.

Whatever route you take, keep the reconciliation. Row count and control total from the source, checked against what arrived, every time — because the failure mode here is not an error message, it is a number that is quietly too small.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.