Why Excel breaks at 1 million rows
Excel has a hard cap of 1,048,576 rows per worksheet (220), which is what fits in a single 20-bit row index. If you open a CSV with more rows, Excel silently truncates and shows you the first million lines. The remaining rows are not visible inside Excel and will not appear in any pivot, formula or chart you build on top.
The cap is the famous part. The slowdown is the part that actually wrecks the workflow. Let us walk through both.
The hard cap: 1,048,576 rows
Every modern Excel file format (XLSX, XLSM, XLSB) inherits the same grid limit: 1,048,576 rows × 16,384 columns per sheet. That number is 220, chosen because Excel internally indexes rows with a 20-bit integer. The limit is identical in modern Excel (365, 2021, 2019) and applies to every sheet in the workbook independently.
What happens when you open a CSV with more rows than that? Excel shows a dialog (‘File not loaded completely’) and proceeds with the first 1,048,576 lines. The rest of the file is on disk untouched, but you cannot see it in Excel until you split the file into smaller chunks.
The soft cap: it hurts way before 1M
Long before the row limit, Excel becomes painful. The calculation engine recomputes the dependency graph on every edit; volatile functions (TODAY, NOW, OFFSET, INDIRECT, RAND) trigger a full recalc; conditional formatting evaluates cell-by-cell; and the file has to be re-written to disk on every save. At 200k rows with a few formula columns, every click stalls for a second. At 500k rows it stalls for several seconds. At 900k rows you can clearly hear the fan.
XLSX is also a zipped XML format. Opening the file means unzipping the entire archive, parsing every cell into the calc grid, then offering it to you for editing. Past around 200k populated rows, the open-and-respond loop on most laptops is measured in seconds per click.
Why CSV is faster for large data
A CSV is plain text. You can read it line by line without holding the whole file in memory, which is exactly what every ETL tool, database loader and split utility does. The same file opened in Excel has to be fully parsed into a cell grid before you can do anything; opened as CSV by a streaming tool it can be processed in constant memory.
Practically, that means: if your data is past 100k rows, do not work with it as a single workbook. Split it before opening, or process it outside Excel and bring just the summary back in.csv.reader, a command-line splitter, or a browser-based tool that uses a Web Worker. The whole point is to avoid loading the file into a grid you do not need.
The practical workflow when your file is too big
When a CSV or XLSX is past what Excel can comfortably handle, the right move is to split it into smaller files that each fit. Whatever downstream tool needs the data (a database import, a BI tool, a colleague on an older laptop) is happier with five 200k-row files than one 1M-row file.
That is the workflow MessyMatch is built for. The CSV splitter streams the file line by line and writes partials of the row count or byte size you specify. The Excel splitter does the same for XLSX with the caveat that XLSX parsing is heavier. For very large XLSX, export to CSV first if you can, then split. The result is a folder of import-ready partials, each safely inside Excel limits, each independently shareable.
A short checklist
- Past 100k rows in XLSX: export to CSV before doing anything heavy.
- Past 500k rows in CSV: do not try to open in Excel at all. Use a CSV-aware tool.
- Need to import into a system with a row cap (HubSpot, Salesforce, Mailchimp): split into partials of the cap size, keep headers in every partial.
- Need to send the file: most providers cap attachments at 25 MB. Split by byte size, not row count, for that case.
- Need to compare two big files: do not VLOOKUP. Diff them with a tool that streams. See the next post on VLOOKUP vs XLOOKUP vs diffing.