CSV too big for Excel: what actually works

You double-click the export, Excel grinds for a minute, then delivers the dreaded bar: “This data set is too large for the Excel grid… some data wasn’t loaded.” The file opens anyway, looking complete, at exactly 1,048,576 rows — and everything past that is silently missing.

That number is Excel’s hard limit: 1,048,576 rows by 16,384 columns (it’s 2²⁰ × 2¹⁴, a design constant, not a setting you can raise). Google Sheets fails differently but just as hard: its budget is 10 million cells per spreadsheet, so a 25-column file hits the wall around 400,000 rows. Neither tool tells you what you actually wanted to know: what now?

Here is every realistic option, with the trade-offs stated plainly.

First, ask what you actually need to do

“Open the file” usually means one of four narrower things, and they have different right answers:

• Look at it — check the columns, spot-check rows, confirm what the export contains.
• Filter or subset it — pull out one region, one date range, one customer.
• Clean or reshape it — dedupe, fix columns, convert format, split into parts.
• Analyze it — pivot tables, joins, aggregations, charts.

Only the last one genuinely needs a spreadsheet or database engine. The first three need a tool that can read millions of rows — a much lower bar than one that loads them into a live grid.

Option 1: a streaming viewer in your browser

A browser-based viewer that indexes the file instead of loading it — this site’s viewer is one — opens multi-gigabyte CSVs in seconds because it renders only the rows on screen and streams through the file for searches, filters and exports. For the look-at-it, filter-it and clean-it jobs it’s the shortest path: nothing to install, and a local-first one won’t upload your data.

Trade-offs: no formulas, no pivot tables — it’s a viewer and cleaner, not a spreadsheet. Files beyond ~4 GB are past what browser tools handle comfortably.

Option 2: Excel’s own Power Query (yes, Excel can do it)

Excel can process more than a million rows — it just can’t show them in the grid. Data → Get Data → From Text/CSV loads the file into Power Query; choose Load To → Only Create Connection (tick “Add to the Data Model”), and you can build pivot tables over the full file, however large. This is Microsoft’s intended answer, documented on their own “data set too large for the grid” help page.

Trade-offs: Windows/Microsoft 365-centric (Mac support is partial), a real learning curve, and you never see the raw rows — you see aggregates. For genuine analysis of a big file you already own Excel for, it’s the best answer on this list.

Option 3: a database (SQLite is enough)

If you’ll query the file repeatedly, import it once into SQLite: .mode csv then .import file.csv tablename in the sqlite3 shell, and every subsequent question is a SQL query that runs in milliseconds. No server, one file, free.

Trade-offs: comfort with a command line and SQL. Import quirks (encodings, embedded newlines) occasionally need fixing first — a cleaner pass through a viewer helps. For one-off inspection it’s overkill.

Option 4: heavy-duty text editors

EmEditor (paid) and a few others open multi-GB text files directly and even have CSV modes. Plain Notepad++ will open files up to a couple of GB, but as raw text — columns don’t align, and editing a 2 GB file in a text editor is an act of faith.

Trade-offs: cost (the good ones are paid), text-not-table semantics, and it’s easy to break a file’s CSV structure with a careless edit.

Option 5: split the file

When the destination itself is the limit — an importer that takes 10,000 rows, a colleague who needs Excel-sized pieces — the answer isn’t a bigger tool, it’s splitting the CSV into parts with the header repeated in each. A quote-aware splitter matters: naive line-based splitting cuts multi-line quoted fields in half and corrupts the parts.

Option 6: command-line tools (for the comfortable)

csvkit, xsv/qsv, miller, or plain PowerShell/awk chew through big CSVs happily and script beautifully. If you already live in a terminal you don’t need this article; if you don’t, learning one for a single file is the slow path.

What we’d actually do

• Just need to see/filter/clean/convert it: browser viewer. Seconds, no install, data stays local.
• Need pivots and analysis in Excel: Power Query with the Data Model.
• Will query it for weeks: SQLite.
• Need to hand pieces to other tools/people: split it.

The quiet danger to avoid

The worst outcome isn’t failing to open the file — it’s succeeding partially without noticing. Excel’s truncation warning is easy to dismiss, and a file cut at 1,048,576 rows looks complete. If a row count matters, count it with a real parser before and after whatever tool touches the file; a dataset that lands on exactly Excel’s limit is a dataset that went through Excel.

One more honesty note: file size and row count limit tools differently. A million short rows (~50 MB) troubles nothing on this list; 200,000 very wide rows (~1 GB) can strain tools that a taller-but-thinner file wouldn’t. When a tool advertises limits, check which dimension it means — for Excel it’s rows and columns, for Sheets it’s cells, for browser tools it’s mostly bytes.

Excel and Google Sheets limits checked against Microsoft and Google documentation, July 2026 — worth re-confirming in their current docs if your workflow depends on the exact numbers.