mygemsSay hi

Why Excel crashes on large files — and what to do instead

Not responding, frozen on scroll, dead on save. The cause is rarely the row count — it is memory, recalculation and formatting. Here is how to tell which one.

By uos ·

Excel very rarely crashes because a file has too many rows. It crashes because of what is on those rows.

A 900,000-row sheet of plain values opens fine on a laptop from 2015. A 40,000-row sheet with conditional formatting applied to whole columns, a few thousand VLOOKUPs and a pivot cache will hang the same machine solidly. The row count is what you notice; it is almost never the cause.

So the useful question is not "how do I make Excel handle big files" — it's which of the five causes is this one, because they have different fixes and four of them take under a minute.

The five causes, in the order they're likely

1. It ran out of memory (and you may be on 32-bit)

Excel loads the entire workbook into RAM, plus the calculation chain, plus undo history, plus a pivot cache per pivot table — often several times the file's size on disk.

Check your build first: File → Account → About Excel. If the top line says 32-bit, Excel is capped at about 2GB of memory no matter how much the machine has, and this is your answer. Microsoft shipped 32-bit as the default for years and plenty of installs never got moved. Reinstalling as 64-bit is the single highest-value fix on this page for anyone it applies to.

The tell for a genuine memory problem: "Excel cannot complete this task with available resources", or a crash specifically on save or on opening a second workbook.

2. Volatile formulas are recalculating everything, constantly

Some functions recalculate on every change anywhere in the workbook, not just when their inputs change. NOW(), TODAY(), RAND(), OFFSET(), INDIRECT(), INFO() and CELL() are the volatile ones, and each drags everything that depends on it along.

INDIRECT() is the usual culprit in a slow workbook, and OFFSET() the usual culprit in one that has become unusable — a few hundred of either across a large sheet is enough for every keystroke to trigger a full recalculation.

Diagnose it in ten seconds: Formulas → Calculation Options → Manual. If the workbook becomes responsive immediately, recalculation was the problem, not size.

The fixes, in order of preference: replace OFFSET/INDIRECT with INDEX, replace VLOOKUP over a full column with INDEX/MATCH or XLOOKUP over a bounded range, and never reference A:A when A1:A50000 will do — a full-column reference is a million-cell scan per formula.

3. Formatting applied to entire columns

Select column A, apply conditional formatting or a fill colour, and Excel may store formatting state for a million cells. Do it on a dozen columns and the file balloons — 40MB workbooks holding 5,000 rows of actual data are common, and they open like they hold a million.

Two tells:

  • Ctrl+End jumps somewhere absurd — row 900,000, column BZ — when your data stops at row 5,000. That's the used range, and Excel allocates against it.
  • The file is enormous relative to its contents.

The fix: select every row below your data to the bottom, delete rows (not contents), same for the columns to the right, save, close, reopen. Ctrl+End should now land on the real last cell. Then re-apply conditional formatting to the actual range rather than to whole columns.

4. A pivot cache per pivot table

Every pivot table stores its own copy of the source data unless it explicitly shares a cache with another. Five pivot tables over a 200,000-row source is a million rows of hidden duplication inside the file.

The fix: build additional pivot tables by copying an existing one rather than starting fresh from the source — copies share the cache. And in PivotTable Options, turn off "Save source data with file" where you can.

5. It genuinely is the row count

If the file is a CSV of several million rows, none of the above applies — Excel holds 1,048,576 rows per sheet and that's structural. It won't crash, exactly; it will load what fits and stop, which is worse. How to open a large CSV file that Excel won't open is the specific guide for that, and how to open a 1GB CSV file compares what handles it instead.

Working out which one you have

Two minutes, in this order:

  1. File → Account → About Excel. 32-bit? Stop here; that's the fix.
  2. Ctrl+End. Lands far past your data? Cause 3.
  3. Formulas → Calculation Options → Manual. Responsive now? Cause 2.
  4. Check the file size against its contents. A 30MB file holding 8,000 rows is cause 3 or 4.
  5. None of the above, and the file is millions of rows? Cause 5 — Excel is the wrong tool, not a broken one.

Worth ruling out separately: add-ins. Start Excel with excel /safe. If the workbook behaves, an add-in was doing it, and none of the above matters.

What to do instead

If it's a data file rather than a document. The pattern that breaks people is using a spreadsheet as a database — hundreds of thousands of rows, formula columns computed across all of them, a pivot on top. That workload wants a query engine:

duckdb -c "SELECT region, SUM(amount) FROM 'export.csv' GROUP BY region"

DuckDB answers in about a second what Excel takes two minutes and a hang to do, and the syntax is easier than a nested SUMIFS. There's a fuller walkthrough in running SQL queries on a CSV without a database.

If you need to stay in Excel. Power Query (Data → Get Data) processes the full file and lands only the summary on a sheet, so millions of rows never occupy a worksheet. The Data Model handles more rows than a sheet can, and lets pivot tables run over it directly.

If you just need to read the thing. Opening a data file in something that treats it as data rather than as a document skips all five causes at once:

CSV File Viewer - Smart CSViPhone & iPad · Android

Reads the file rather than importing it — no recalculation, no formatting state, no pivot caches. SQL or plain-language questions against the values as written.

What it does

Two habits that prevent most of this

Keep values and presentation apart. One sheet of raw data with no formatting and no formulas, one sheet of analysis referencing it. Most catastrophic workbooks are one sheet doing all three jobs.

Reference ranges, not columns. A1:A50000 instead of A:A, everywhere. It is the single habit with the largest effect on whether a workbook stays usable as it grows.

Questions

Why does Excel keep saying 'not responding' on a large file?
Usually recalculation rather than size. Volatile functions — OFFSET, INDIRECT, NOW, TODAY, RAND — recalculate on every change anywhere in the workbook, and full-column references like A:A make each formula scan a million cells. Set Formulas → Calculation Options → Manual; if it becomes responsive at once, that was the cause.
Is my Excel 32-bit, and does it matter?
Check File → Account → About Excel. A 32-bit build is capped near 2GB of memory regardless of how much RAM the machine has, so it fails on workbooks a 64-bit build handles easily. Microsoft defaulted to 32-bit for years, so plenty of installs are still on it — reinstalling as 64-bit is the biggest single fix for anyone it applies to.
Why is my Excel file so large when it has so little data in it?
Almost always formatting applied to entire columns, which makes Excel store state for a million cells per column. Press Ctrl+End: if it jumps far past your real data, that is the inflated used range. Delete the empty rows and columns beyond your data, save, close and reopen.
Does Excel crash because of the 1,048,576-row limit?
No — that limit does not cause a crash. Excel loads the rows that fit and stops, often without a clear warning, which is more dangerous than crashing because the totals you then read are quietly wrong. Crashes and hangs come from memory, recalculation, formatting or pivot caches instead.
How do I stop pivot tables from bloating a workbook?
Each pivot table keeps its own copy of the source data unless it shares a cache. Create new pivot tables by copying an existing one rather than starting again from the source, and switch off 'Save source data with file' in PivotTable Options where the workflow allows it.
What should I use instead of Excel for large data files?
For questions about the data, DuckDB queries a CSV in place in about a second with no import step. To stay in Excel, Power Query and the Data Model both process far more rows than a worksheet can hold while landing only the summary on a sheet. To simply read a large file, use a viewer that treats it as data rather than as a document.

uos — Builds CSV Editor and Smart CSV Viewer, and has spent more time than intended working out which part of a workbook was doing the killing.

The apps in this guide

All guides