Excel's row limit, and what to do when you hit it

The exact numbers

They are powers of two because a row is addressed by a fixed-width number in the file format. The ceiling is structural, not a setting: there is no option, registry key or edition of Excel that raises it, and buying more RAM does not move it.

What actually happens when the file is bigger

Excel does not refuse the file. It reads the first 1,048,576 rows, shows a message saying the file is not loaded completely, and leaves you with a worksheet that looks perfectly normal. That is the dangerous part: a truncated file is silent afterward. Nothing on screen says "there are 400,000 more rows you cannot see", and a total computed from it is simply wrong.

If you have opened a large CSV in Excel and a total looked low, check the row count before you check anything else.

Long before the hard limit, Excel gets slow

The practical ceiling is lower than the documented one. A workbook with 300,000 rows and a dozen formula columns recalculates on every edit, and volatile functions such as OFFSET, INDIRECT and NOW recalculate on every change anywhere in the workbook. Add a few VLOOKUP columns over the full range and a keystroke can take seconds. Most people meet that wall well before they meet 1,048,576.

Five ways past it

1. Load it into the Data Model instead of the grid

Power Query and Power Pivot load data into Excel's Data Model rather than onto a worksheet, and the Data Model is not bound by the worksheet row limit. You can then pivot over more rows than a sheet can hold. This is the best answer if you are staying in Excel: nothing new to buy, and the analysis you wanted usually works on aggregates anyway.

2. Aggregate before it reaches the sheet

Ten million transaction rows are rarely the thing you need. Ten thousand daily totals usually are. If the data comes from a database, do the GROUP BY there. If it comes as a CSV, aggregate it on the way in rather than after it has arrived.

3. Split the file

Crude, and sometimes exactly right — one file per month, per region, per account. It is the fastest route when the rows genuinely belong to different people or periods. Split a CSV in your browser, by row count or by a column's value, with the header repeated in every part.

4. Put it in a database

SQLite, DuckDB or Postgres will hold it without complaint and query it quickly. The cost is SQL, and a second place your data now lives. Right answer for engineers, a real step up in effort for everyone else.

5. Use a tool with no worksheet limit

TableDI 2 is a desktop app for file work you redo every period: it keeps your sources, rules and delivery as a job, so next month you drop in the new files and run it again. Everything runs on your own machine. Its engine is not a worksheet grid, so a file past 1,048,576 rows still filters and exports normally — on the free edition too. And when the big file is next month's export of the same thing, the checks you set up run on it again.

What the alternatives cap out at

Notice that Google Sheets is measured in cells. A 40-column sheet reaches ten million cells at 250,000 rows — considerably sooner than people expect when they move a large file there to escape Excel.

If you are looking at the "file not loaded completely" message right now

Three things, in order, none of which needs another program. Count the rows in the box above, so you know whether the file is 1.1 million rows or 30 million. Open it in the CSV viewer, which reads past Excel's limit in the browser and lets you search and sort the whole thing. If someone still needs it in Excel, cut it with the CSV splitter into files that fit a sheet each — or filter in the viewer and export only the slice they actually asked for, which is usually a few thousand rows.

Before you do anything, find out how many rows you actually have

On macOS or Linux, wc -l bigfile.csv answers it in a second. On any machine, drop the file into the reader at the top of this page — it pages through the file rather than rendering all of it, so it opens files Excel will not, and the row count is the first thing it prints. Nothing is uploaded: the file is read by JavaScript in this tab.

For a sense of scale, measured in Chrome on an M-series Mac: a 124.7 MB CSV of 1,200,000 rows × 8 columns — about 150,000 rows past what Excel will hold — is parsed in 0.58 seconds, and searching every column of it takes another 0.1. The ceiling here is your machine's memory, not a row number in a file format.