Excel crashes when opening a large file: why, and what to do
You export a report, double-click the CSV, and Excel spends four minutes at “Not Responding” before either dying or opening a file that is quietly missing most of its rows. Here is what is actually happening and what to do about it.
Why it happens
Three separate limits, and they fail in different ways.
The row limit. A worksheet stops at 1,048,576 rows and 16,384 columns. That number is fixed and no version of Excel raises it. If your CSV has more rows, Excel opens the first 1,048,576 and tells you the file was truncated. The rest of your data is still in the file. It is just not on your screen, which is the dangerous failure, because a truncated file looks like a working file.
Memory. Excel loads the entire file into RAM, and then some. A 2 GB CSV does not become a 2 GB workbook. Text becomes objects, every cell gets formatting and formula metadata, and the working set commonly runs three to ten times the file size. A 2 GB file on a 16 GB laptop is how you get a freeze rather than an error.
Single-threaded recalculation. Once the file is open, every sort, filter and formula recalculates on mostly one core. This is why a file that opened after five minutes still takes thirty seconds to sort a column.
Excel error messages, decoded
| What you see | What it means |
|---|---|
| “File not loaded completely” | You are over 1,048,576 rows. Rows past the limit were dropped. |
| “Excel cannot complete this task with available resources” | Out of memory. Closing other workbooks may buy you one more try. |
| “This data set is too large for the Excel grid” | Power Query returned more than 1,048,576 rows. Load to the Data Model instead of a worksheet. |
| Excel keeps crashing on Windows 11 with no message | Almost always memory. Check the file size against your free RAM. |
| “Not Responding”, then nothing | Usually memory, sometimes a recalculation that has not finished. |
| The file opens but numbers are wrong | Long IDs converted to scientific notation, or leading zeros stripped. |
That last one deserves its own warning. Excel guesses types on import. Account numbers, phone numbers and postcodes get mangled silently, and no message is shown.
Five things that actually work
1. Check the row count before you open anything. On macOS or Linux:
wc -l bigfile.csvOn Windows PowerShell:
(Get-Content bigfile.csv -ReadCount 1000 | Measure-Object -Line).LinesIf the answer is under a million, your problem is memory or column count, not the row limit, and the fixes are different.
2. Split the file. Crude, effective, and the most common advice you will find. On macOS or Linux, split -l 1000000 bigfile.csv part_ gives you chunks Excel can open. The cost is that you now have nine files and no way to sort, filter, pivot or total across all of them. Every question you ask has to be asked nine times and added up by hand, and that is where the errors come from.
3. Power Query. Built into Excel, under Data, Get Data, From Text/CSV. It loads the file into the data model rather than the grid, so the row limit does not apply and you can aggregate over the whole thing. Good for a repeatable summary. Less good if you want to look at the rows, edit a cell, or do anything ad hoc, and it is still loading into memory on one machine.
4. Load it into a database. DuckDB, SQLite or Postgres will handle the file without complaint. duckdb -c "SELECT * FROM 'bigfile.csv' LIMIT 10" works on a 50 GB file. This is the right answer if you write SQL. If you do not, “learn SQL” is not a fix for “I need this report by Thursday”.
5. Use a tool built to stream the file. Some tools never load the whole file. They index it, read the window you are looking at, and push the work down to a query engine. That is the only approach where the file size stops mattering, and it is the one that keeps a spreadsheet interface instead of a query prompt.
A word on uploading it somewhere
Plenty of web tools will open a big CSV for you. Before you drag the file in, check what is in it. Payroll, claims, customer records and anything with a name and an email attached are usually covered by a policy that says they do not go to third-party servers, and “it was just to open the file” is not a defence anyone has won with. If the file has personal data in it, the tool needs to run on your machine.
What we built
BigXL is a desktop spreadsheet for exactly this file. It opens a 10 GB CSV in seconds because it never loads it: rows appear as you scroll, sorting and filtering run through an embedded query engine, and there is no row limit. You can ask it a question in plain English and see the SQL it ran. Everything runs on your machine, with no account and no network calls, so the file with the payroll in it never leaves the building.
Opening, sorting, filtering, charting and CSV export are free for any file size.