Excel Stops at 1,048,576 Rows: How to Open a Bigger CSV File

Excel has a hard ceiling of 1,048,576 rows, and it does not refuse a file that goes past it. It loads rows up to the ceiling, shows a warning banner, and if you save, everything past that point is gone from your copy. That is how order exports and log dumps lose data without anyone noticing.

The fix is to stop treating a big CSV like a spreadsheet. You can query it where it sits with DuckDB, stream it through pandas or Polars, cut it into Excel-sized pieces, or page through it in a browser viewer. I tested each approach on one file, orders_2025.csv: 1,200,000 orders, 7 columns, 71 MB, on an Apple M3 Pro with 18 GB of RAM on October 3, 2026. The full write-up with every timing lives on DevToolLab. This is the condensed version.

Count the Rows Before You Open Anything

Find out how many records you have first. The CSV Row Counter reads the file in chunks inside the browser, so even a multi-gigabyte export never has to fit in memory, and it tells you whether you are over Excel's limit and how many files a split would need. It counts parsed records rather than newline characters, so an address field with an embedded line break does not throw the total off.

To actually look at the data, the CSV Viewer shows 100 rows per page, with search, sort and filter that reach every row in the file. In Chrome, the 1.2-million-row export rendered its first page in about 7 seconds and the tab used around 440 MB. Nothing gets uploaded. That memory figure is also where the approach runs out: once files reach a few hundred megabytes, move to DuckDB.

Two Terminal Commands Give You the Shape

On macOS or Linux:

wc -l orders_2025.csv
head -n 3 orders_2025.csv

 1200001 orders_2025.csv
order_id,order_date,customer_name,city,state,zip,amount_usd
1000001,2025-09-01,"Susan Smith",Phoenix,AZ,85004,365.04
1000002,2025-10-06,"Susan Garcia",Los Angeles,CA,90012,161.47

That is a header line plus 1.2 million orders. Opened in Excel, you would get the header and the first 1,048,575 orders, and the last 151,425 would be cut. One caveat: wc -l counts newlines, not records, so quoted fields with line breaks inside them make it overcount.

DuckDB Runs SQL Against the File With No Import

DuckDB is an embedded SQL engine that runs inside your process and reads CSV files directly. There is no server and no load step. Version 1.5.6 came out on September 28, 2026, is MIT licensed, and ships as a single binary on GitHub releases or as pip install duckdb.

duckdb -c "SELECT state, count(*) AS orders, round(sum(amount_usd), 2) AS revenue_usd
FROM 'orders_2025.csv' GROUP BY state ORDER BY revenue_usd DESC LIMIT 5"

┌─────────┬────────┬─────────────┐
│  state  │ orders │ revenue_usd │
│ varchar │ int64  │   double    │
├─────────┼────────┼─────────────┤
│ NY      │ 168025 │  17516196.8 │
│ TX      │ 156144 │  16308814.9 │
│ CA      │ 143691 │ 14880720.49 │
│ IL      │ 107928 │ 11265794.74 │
│ GA      │  72201 │  7527632.02 │
└─────────┴────────┴─────────────┘

Over three runs, that aggregate across all 1.2 million rows took 0.08 to 0.20 seconds. DuckDB works out the delimiter, header and column types by itself and streams the file rather than holding it in RAM. On a 12-million-row, 712 MB copy, the same query took 0.37 seconds and peaked at 203 MB of memory.

It is also the cleanest way to carve out a subset small enough for Excel. Here are just the Texas orders:

duckdb -c "COPY (SELECT * FROM 'orders_2025.csv' WHERE state = 'TX') TO 'orders_tx.csv' (HEADER)"
wc -l orders_tx.csv

  156145 orders_tx.csv

pandas Chunks and Polars Lazy Scans

If the data is headed into Python anyway, pandas 3.0.6 can iterate over the file with chunksize instead of reading it all at once, and usecols keeps only the columns you need:

import pandas as pd

totals = pd.Series(dtype="float64")
for chunk in pd.read_csv("orders_2025.csv", usecols=["state", "amount_usd"], chunksize=250_000):
    totals = totals.add(chunk.groupby("state")["amount_usd"].sum(), fill_value=0)

print(totals.sort_values(ascending=False).head(3).round(2))

state
NY    17516196.80
TX    16308814.90
CA    14880720.49
dtype: float64

At 71 MB, though, chunking buys you nothing. A plain pd.read_csv loaded the whole file in 0.42 seconds with a 241 MB peak. The payoff arrives as the file gets close to your RAM: on the 712 MB copy, a full load peaked at 1,466 MB and took 4.14 seconds, while the chunked loop held at 171 MB and finished in 2.85.

Polars 1.44.2, also MIT licensed, takes the lazy route. pl.scan_csv builds a query plan and reads only what that plan needs:

import polars as pl

top = (
    pl.scan_csv("orders_2025.csv")
    .group_by("state")
    .agg(pl.len().alias("orders"), pl.col("amount_usd").sum().round(2).alias("revenue_usd"))
    .sort("revenue_usd", descending=True)
    .head(3)
    .collect(engine="streaming")
)
print(top)

On the 712 MB copy it was the quickest of the three, 0.22 to 0.26 seconds with the streaming engine, but memory peaked near 890 MB. If RAM is your tight resource rather than time, DuckDB or chunked pandas is the better pick. The original guide lays out the memory and timing numbers for all three side by side.

Splitting the File for Excel

When the file has to end up in Excel, break it into pieces under the limit and put the header back on each one:

tail -n +2 orders_2025.csv | split -l 1000000 - part_
for f in part_*; do
  { head -n 1 orders_2025.csv; cat "$f"; } > "orders_$f.csv" && rm "$f"
done
wc -l orders_part_*.csv

 1000001 orders_part_aa.csv
  200001 orders_part_ab.csv
 1200002 total

The problem with split -l is that it cuts on raw newlines, so a record with a line break inside quotes (multi-line notes, two-line addresses) gets torn in half. For files like that, let DuckDB do the split, because it actually parses the CSV. Cutting orders_2025.csv at July 1 produced files of 595,240 and 604,762 lines:

duckdb -c "COPY (SELECT * FROM 'orders_2025.csv' WHERE order_date < DATE '2025-07-01') TO 'orders_h1.csv' (HEADER)"

On Excel for Windows you may not have to split at all. Microsoft's guidance for data sets too large for the Excel grid sends the file through Power Query (Data, From Text/CSV, then Load To and PivotTable Report). The grid still caps out at 1,048,576 rows, but the PivotTable summarizes all of them. That path is documented for the Windows app, and I have not tested it myself.

Google Sheets is no escape hatch either. Its limit is 20 million cells or 100 MB per spreadsheet. At 8.4 million cells, orders_2025.csv squeezes in, but the same row count at 30 columns would not.

Errors You Will Probably Run Into

Excel's This data set is too large for the Excel grid warning means it stopped at row 1,048,576. Close the workbook without saving and use one of the methods above. If you really want to keep the partial load, Microsoft suggests File, Save a Copy, under a name that makes the truncation obvious.

pandas.errors.ParserError: Error tokenizing data. C error: Expected 7 fields in line 4, saw 8 means a row has an extra delimiter, usually an unquoted comma inside a value like Robert Lee, Jr.. DuckDB phrases it as CSV Error on Line: 500001 ... Expected Number of Columns: 7 Found: 8. To keep reading while still capturing the bad rows, use read_csv('orders.csv', store_rejects = true) and then query reject_errors, which returns the offending line, its number and TOO MANY COLUMNS. In pandas, on_bad_lines="skip" throws those rows away without a trace, so count them before you skip them. The CSV Row Counter also lists the record numbers whose field count does not match the header.

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 39: invalid continuation byte means the file was saved as Windows-1252, which is what Excel on Windows writes for a plain CSV, and 0xe9 is an é. DuckDB reports it as Invalid unicode (byte sequence mismatch) detected. This file is not utf-8 encoded. Pass encoding="cp1252" to pandas, or convert the file once with the CSV to UTF-8 Converter so every downstream tool can read it.

ZIP codes losing their leading zero is the quiet one. pandas guessed int64 for the zip column and turned Boston's 02108 into 2108. Read it as text with dtype={"zip": "string"}. DuckDB kept it as VARCHAR in this test because its sniffer spotted the leading zeros, but pin it with types = {'zip': 'VARCHAR'} when it matters.

If You Open It Every Week, Convert to Parquet

Reopening the same large CSV every week is wasted work. Convert it once to Parquet, a compressed columnar format that DuckDB, pandas and Polars all read:

duckdb -c "COPY (SELECT * FROM 'orders_2025.csv') TO 'orders_2025.parquet' (FORMAT parquet)"

That turned 71 MB of CSV into 15.3 MB of Parquet in 0.35 seconds, and the state query dropped from roughly 0.1 seconds to 0.02. And if the file holds customer data, check whether an online viewer processes it in your browser or posts it to a server before you hand it over.

Which Method to Pick

• You just want to see the rows: count them with the CSV Row Counter, then browse in the CSV Viewer as long as the file is under a few hundred megabytes.
• You need answers from the data: DuckDB. Every query in this test came back in under half a second, and it never loaded the file.
• You are already in Python: chunked pd.read_csv once the file approaches your RAM, Polars when speed matters most.
• It has to land in Excel: split with DuckDB COPY so quoted line breaks survive, or use Power Query on Windows.

Whichever you choose, never save the truncated workbook. The full version on DevToolLab has the rest of the error cases and the test setup.

References

• How to Open Large CSV Files (1M+ Rows) - the original article on DevToolLab, with every command, timing and error message
• DuckDB and its GitHub releases
• pandas
• Polars
• Microsoft: What to do if a data set is too large for the Excel grid
• Google Drive: Files you can store in Google Drive