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 is a desktop spreadsheet workspace that runs entirely on your machine, and it holds millions of rows in one table — it has its own data engine rather than a worksheet grid, so there is no 1,048,576 line to cross. Import is not capped on any tier, including the free one, so you can point it at the file that would not open and see the row count.

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.

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 CSV viewer — it pages through the file rather than rendering all of it, so it opens files Excel will not, and the row count is at the top. Nothing is uploaded.