Excel's 1,048,576 Row Limit: How to Work with Bigger Files

The limit: Excel can display a maximum of 1,048,576 rows (2^20) per sheet. Files with more rows open, but Excel silently truncates them — you see no warning, just missing data. For any file that exceeds this limit, you need a different approach.

---

First: Check if You're Actually Hitting the Limit

Open the file. Press Ctrl+End to jump to the last used cell. If it shows row 1,048,576, your file is truncated.

Alternatively: check the actual row count before opening in Excel. On Mac/Linux: wc -l yourfile.csv. On Windows: find /c /v "" yourfile.csv.

---

Option 1: Power Query (Process Without Displaying All Rows)

Power Query can import and process a large file without loading all rows into a worksheet. It streams the data, applies your transformations, and only loads the result — which is often much smaller than the source.

1. Data → Get Data → From File → From Text/CSV
2. Select your large file — Power Query opens a preview (it doesn't load everything)
3. Apply filters, aggregations, and transformations in the Query Editor
4. Close & Load — only the query result loads into the worksheet

See exactly what's wrong with your data — try Sohovi free — try Sohovi free.

Best for: Large files where you need to aggregate, filter, or summarize — and the result fits in Excel's row limit.

Limitation: Still constrained to 1M rows in the final output. And Power Query itself can slow down dramatically on files with tens of millions of rows.

---

Option 2: Split the File First

Split the large CSV into chunks under 1M rows each, then process each chunk in Excel:

# Mac/Linux: split into 900,000-row chunks
split -l 900000 large_file.csv chunk_

See How to Split a Large CSV into Smaller Files for the full approach including adding headers to each chunk.

Best for: Files you need to manually inspect or edit row-by-row.

---

Option 3: Use Python + Pandas

Python has no row limit. Process the file programmatically:

import pandas as pd

# Read large file in chunks (to manage memory)
chunk_size = 500_000
result_rows = []

for chunk in pd.read_csv('large_file.csv', chunksize=chunk_size):
    # Apply your filter/transformation to each chunk
    filtered = chunk[chunk['status'] == 'Active']
    result_rows.append(filtered)

result = pd.concat(result_rows)
result.to_csv('filtered_output.csv', index=False)

Best for: Repeatable processing of large files, especially filtering or aggregation.

---

Option 4: Load into a Database

SQL databases handle billions of rows without flinching:

1. Load the CSV into SQLite, PostgreSQL, or MySQL (see How to Bulk-Import a CSV into MySQL, PostgreSQL, and SQLite)
2. Run SQL queries against the table
3. Export your query results (which fit in Excel) back to CSV

-- Example: get summary stats from a billion-row table
SELECT status, COUNT(*), SUM(amount) 
FROM large_table 
GROUP BY status;

Best for: Files you'll query repeatedly. One-time effort to import; all subsequent analysis is fast SQL.

---

Option 5: Google BigQuery or Cloud Databases (No Local Limit)

For truly massive files (100M+ rows), cloud databases are purpose-built:

• Google BigQuery: load CSV directly from Cloud Storage, query with SQL
• Amazon Redshift, Snowflake, and similar: same pattern

Best for: Data team with cloud infrastructure who regularly works with large data.

---

What Not to Do

Don't ignore the truncation. Excel opens large files silently — you may not realize you're working with 1M rows when the actual file has 2M. Always verify row count.

Don't try to analyze truncated data. Any aggregate analysis (averages, sums, counts) on truncated data produces wrong results without any visible indication that they're wrong.

---

Frequently Asked Questions

Q: How do I know if Excel truncated my file on open? Press Ctrl+End to jump to the last cell. If it lands on row 1,048,576, the file was truncated. For confirmation, check the actual line count of the original CSV.

Q: Can I increase Excel's row limit? No — the 1,048,576 row limit is a fundamental architectural constraint of the .xlsx file format (OOXML). It cannot be changed via settings or add-ins.

Q: Does Power Query have the same row limit? Power Query processes without the display limit, but the worksheet it outputs to still has the 1M row limit. Power Query's strength is that your query result (after filtering and aggregating) usually fits in the limit even when the source doesn't.

---

Need to profile a large file before deciding how to process it? Sohovi can handle large files in the browser — drag it in and get a quality profile (null rates, duplicate count, type distribution) without opening the file in Excel at all.

Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.