How to Open a Very Large CSV in Excel (Row Limit Workarounds)

To open a very large CSV in Excel, load it with Power Query to the Data Model instead of a worksheet — the Data Model is not bound by the 1,048,576-row worksheet limit. A plain double-click or Data > From Text/CSV > Load truncates anything past row 1,048,576 with no warning worth noticing, silently discarding your data. If your file is merely big (not enormous), splitting it first is simpler. (For normal-sized files, the free CSV to Excel converter handles the whole job.)

First, know your file's size: open it in a text editor that shows line counts (VS Code, Notepad++), or check with wc -l on Mac/Linux. Under ~1M rows, standard import works. Over it, you need one of the methods below.

Method 1: Power Query to the Data Model (Best for Analysis)

1. Data > Get Data > From File > From Text/CSV. Select the file.
2. Click Transform Data (not Load).
3. Do any needed cleaning, then Home > Close & Load To.
4. In the dialog, choose Only Create Connection and tick Add this data to the Data Model.
5. Build pivot tables and charts against the Data Model (Insert > PivotTable > From Data Model).

The Data Model (Power Pivot engine) handles tens of millions of rows on a decent machine. You analyze with pivot tables exactly as usual — the only difference is the data never sits in worksheet cells. This is the correct answer for "I need to analyze 5 million rows in Excel."

Memory is the real limit here, not rows: 64-bit Excel with 16 GB RAM handles most real-world large files. If Excel crawls, aggregate in Power Query first (Group By) so the model holds summaries, not raw rows.

Method 2: Split the File, Then Import Pieces

When you need the raw rows in worksheets (for editing, not just analysis):

1. Split the CSV into chunks under 1M rows each. Options:

• Python: read and write every N rows to part-01.csv, part-02.csv, etc.
• PowerShell: Get-Content big.csv -ReadCount 500000 | ... batched writes.
• The splitting guide: splitting a CSV into multiple Excel sheets covers chunking by column values too.

1. Import each chunk to its own sheet (or its own workbook).
2. Keep the header row in every chunk — most splitters do this automatically; verify.

Splitting is honest and simple, but cross-chunk analysis (a pivot over all parts) needs the pieces recombined — which is exactly what the Data Model approach avoids. Split for editing; model for analysis.

Method 3: Import Only What You Need

Often the "large file" problem is really a "I only need 5% of it" problem:

• Filter at import: in Power Query, filter rows (date range, region, status) before loading. A 3M-row log becomes a 40K-row worksheet.
• Remove columns: drop the 30 columns you never use; fewer columns means more rows fit in memory and everything runs faster.
• Aggregate at import: Group By in Power Query turns millions of transactions into thousands of summary rows — usually what the report wanted anyway.

Performance Tips for Big Imports

• Use 64-bit Excel. 32-bit Excel caps memory around 2 GB and chokes on large models. (File > Account > About Excel shows the bitness.)
• Set column types in Power Query before loading. Letting Excel guess types on millions of rows is slow and error-prone — explicit types are faster and correct.
• Disable "Load to worksheet" when you only need the model. The connection-only query is lighter.
• Avoid volatile formulas (OFFSET, INDIRECT, TODAY in every row) on large Tables — they recalculate constantly. Use Power Query computed columns or the Data Model's DAX instead.
• Save as .xlsb (binary workbook) for large files: smaller on disk and faster to open than .xlsx.

What NOT to Do

• Do not open a 2M-row CSV by double-clicking. Excel truncates at 1,048,576 rows. The truncation warning is easy to miss, and you will analyze half your data believing it is all of it.
• Do not "fix" it by deleting columns to fit. You are still missing rows — the file is too long, not too wide.
• Do not convert to .xlsx as an intermediate step for oversized files. The .xlsx has the same row limit; converting does not create capacity.

Sampling Before the Full Import

For a file you have never seen, import a sample first — it reveals the schema, the encoding, and the surprises before you commit to the full load:

1. Peek at the head. head -n 20 big.csv (Mac/Linux) or open in VS Code shows the header and first rows. Check: delimiter, quoting, encoding hints, whether the first row really is headers.
2. Count rows and columns. wc -l for rows; the header's delimiter count for columns. Compare against what you expect — a 50-column header on a "10-column export" means the file changed.
3. Import 1,000 rows into a scratch sheet. In Power Query, use Keep Rows > Keep Top Rows = 1000, then examine types: which columns came in as text vs number vs date, where the nulls are, whether any column is entirely empty.
4. Decide the strategy from the sample: full worksheet load, Data Model, split, or filter-first. The sample tells you which method the file deserves.

Sampling also catches the classic trap: a file that starts clean and degrades 800,000 rows in (a format change mid-file, a corrupted section). For critical imports, sample the tail too (tail -n 20) — head and tail together catch most structural breaks.

Schema drift detection. Save the sample's column list (headers + types) next to the project. Each new delivery gets its headers compared against the saved schema — a diff means the source changed, and you investigate before the full import, not after the dashboard breaks. Power Query can automate the comparison; even a manual eyeball-check beats nothing.

FAQ

What is Excel's row limit for CSV files?

1,048,576 rows per worksheet — a hard limit of the grid, applying to CSV imports, .xlsx files, everything. The Data Model (Power Pivot) is not bound by it.

How do I know if my CSV was truncated?

Compare row counts: count lines in the source (wc -l, VS Code line count) against rows in the sheet. If the sheet has exactly 1,048,576 rows and the file has more lines, it was truncated.

Can Power Query handle 10 million rows?

It can process them (filtering, aggregating) on a capable machine, especially 64-bit Excel with enough RAM. Loading 10M raw rows into the grid is impossible; loading them into the Data Model or aggregating first is the intended path.

Is Google Sheets better for huge CSVs?

Sheets caps at 10M cells total — often worse than Excel's Data Model for row-heavy files. For genuinely big data, a database (even free SQLite/DuckDB) beats every spreadsheet.

Will splitting a CSV lose data?

Not if done right: keep the header in every chunk, split on row boundaries (never mid-row), and verify each chunk's row count sums to the original. A quick wc -l check per chunk takes seconds.

Final Checklist

• Source row count known before importing — no blind double-clicks.
• Over 1M rows: Data Model via Power Query, or split into <1M-row chunks.
• Column types set explicitly at import; unneeded columns removed.
• 64-bit Excel confirmed; file saved as .xlsb if large.
• Row counts verified after import: nothing truncated, nothing missing.

If all five checks pass, your large CSV is fully inside Excel — every row accounted for, analysis running at full speed.