Excel's 1 Million Row Limit: 4 Ways to Open Large CSV Files That Won't Fit

You exported a CSV from your CRM, ERP, Google Analytics, or internal database. You opened it in Excel. And somewhere around row 1,048,576, the data just stops — or Excel warned you that "the data set is too large for the Excel grid" before quietly dropping everything past the limit.

Excel's maximum row count is 1,048,576 rows (just over 1 million). Open a CSV that exceeds this limit by double-clicking it, and the excess rows are silently discarded. If you save that file without realizing what happened, those rows are gone from the CSV permanently.

This article covers four practical ways to work with CSVs that exceed Excel's row limit, ordered from quickest to most technical.

4 Ways to Handle CSVs That Exceed Excel's Row Limit

---

Method 1: Use an Online CSV Analysis Tool (Easiest)

You don't need to learn Power Query or write any code. Browser-based CSV tools let you open large files, filter rows, run pivot aggregations, and export results to Excel — all without installing anything.

The workflow is straightforward: drag your CSV into the browser, filter down to the rows you care about, aggregate with a pivot table, and download the result as an Excel file ready for reporting.

LeapRows (disclosure: built by the author), for example, processes CSV files entirely in the browser — your data is never uploaded to a server, which matters for files containing confidential business data. It handles large files and auto-detects file encoding (UTF-8, Shift-JIS, Windows-1252), so international exports work without manual conversion.

This is the fastest option if you need to see what's in a large CSV right now, without configuring anything in Excel.

---

Method 2: Filter or Aggregate with Power Query Before Loading (★★☆)

Power Query is built into Excel 2016 and later. It can read CSVs with millions of rows — the trick is to filter or aggregate the data before loading it into the worksheet, so the result fits within the 1,048,576-row limit.

Filter rows down to what you need

1. Open a new, blank workbook in Excel
2. Go to Data → Get Data → From File → From Text/CSV
3. Select your CSV → click Transform Data (not Load)
4. In the Power Query Editor, use the filter dropdowns on each column to narrow the data
5. Once the filtered row count is under 1 million, click Close & Load

Only the filtered rows land in your worksheet. The rest of the CSV was read and discarded by Power Query — not by Excel's row limit.

Aggregate with Group By

If you don't need individual rows at all — just totals, counts, or averages by category — use Group By to compress millions of rows into a summary table.

1. In the Power Query Editor, go to Home → Group By
2. Choose your grouping key (e.g., product category, month, region)
3. Select an aggregation (Sum, Count, Average, etc.)
4. Click OK → Close & Load

A 5 million-row sales export becomes a 200-row monthly summary table that Excel handles effortlessly.

Load into Power Pivot instead (no row limit)

If you need to analyze the full dataset with pivot tables — not just a filtered subset — load it into Power Pivot's data model instead of the worksheet.

Instead of Close & Load, choose Close & Load To → Add to Data Model. The data is stored in memory outside the worksheet grid, so the 1,048,576-row limit doesn't apply. You can then build pivot tables against the full dataset.

---

Method 3: Split the CSV into Smaller Files (★★☆)

If you want to browse the raw data without filtering or aggregating, splitting the CSV into chunks under 1 million rows is the most direct workaround.

Mac / Linux (terminal)

# Create output directory to avoid glob collisions on re-runs
mkdir -p split_out

# Save the header
head -1 large_file.csv > split_out/header.csv

# Split the body into 900,000-line chunks
tail -n +2 large_file.csv | split -l 900000 - split_out/chunk_

# Add the header to each chunk and remove intermediate files
for f in split_out/chunk_*; do
  cat split_out/header.csv "$f" > "${f}.csv"
  rm "$f"
done

Each output file (split_out/chunk_aa.csv, split_out/chunk_ab.csv, etc.) is under 900,000 rows and includes the original header row.

Windows (PowerShell)

$header = Get-Content large_file.csv -TotalCount 1

# Write the first 900,000 data rows to part1.csv
@($header) + (Get-Content large_file.csv | Select-Object -Skip 1 -First 900000) |
  Set-Content part1.csv

# Write everything after row 900,000 to part2.csv
@($header) + (Get-Content large_file.csv | Select-Object -Skip 900001) |
  Set-Content part2.csv

Each split file includes the header row automatically. If you need more than two parts, adjust the -Skip and -First values accordingly (part 3 would use -Skip 1800001 -First 900000, and so on).

If command-line tools aren't your preference, search for "CSV splitter online" — several free browser-based tools handle this without any setup. Look for one that has a "preserve header row" option.

After splitting: verify that each file includes the header. Without it, Excel treats the first data row as column names, producing "Column A", "Column B" instead of your actual field names. Also check that file encoding hasn't changed — some tools silently convert UTF-8 to the system default during splitting, which garbles non-ASCII characters.

---

Method 4: Aggregate with Python or PowerShell, Then Export to Excel (★☆☆)

If you're comfortable with scripts, this approach removes the row limit entirely and handles files with tens of millions of rows.

Python (pandas)

For files that fit in memory, the basic script is straightforward:

import pandas as pd

# For files that fit in memory
df = pd.read_csv('large_data.csv')
summary = df.groupby('category').agg(
    sales_total=('sales', 'sum'),
    quantity_avg=('quantity', 'mean')
).reset_index()
summary.to_excel('summary.xlsx', index=False)
print(f"Done: {len(df):,} rows → {len(summary):,} summary rows")

For very large files that don't fit in memory, use chunksize to process the data in batches:

import pandas as pd

# Process in 100,000-row chunks to avoid memory crashes
chunks = []
for chunk in pd.read_csv('large_data.csv', chunksize=100000):
    partial = chunk.groupby('category').agg(
        sales_total=('sales', 'sum'),
        quantity_sum=('quantity', 'sum'),
        count=('quantity', 'count')
    )
    chunks.append(partial)

# Re-aggregate the partial results
result = pd.concat(chunks).groupby('category').agg(
    sales_total=('sales_total', 'sum'),
    quantity_sum=('quantity_sum', 'sum'),
    count=('count', 'sum')
).reset_index()

# Calculate true average from totals (not average of averages)
result['quantity_avg'] = result['quantity_sum'] / result['count']
result.drop(columns=['quantity_sum', 'count'], inplace=True)

result.to_excel('summary.xlsx', index=False)

Note that averaging requires care with chunked processing: averaging the averages of each chunk is mathematically wrong when chunks have different sizes. The pattern above accumulates sums and counts separately, then divides at the end for a correct overall average.

PowerShell

PowerShell is built into Windows — no installation needed:

$data = Import-Csv -Path "large_data.csv"
$summary = $data | Group-Object -Property category | ForEach-Object {
    [PSCustomObject]@{
        Category   = $_.Name
        TotalSales = ($_.Group | Measure-Object -Property sales -Sum).Sum
        Count      = $_.Count
    }
}
$summary | Export-Csv -Path "summary.csv" -NoTypeInformation

PowerShell works well for moderate-sized files, but it's significantly slower than pandas for very large datasets. For tens of millions of rows, Python is the better choice.

Once a script works correctly, it's easy to schedule. On Windows, use Task Scheduler; on Mac/Linux, use cron. A recurring CSV aggregation that takes 30 minutes of manual work each month becomes a background task that runs automatically.

---

Excel Row Limits by Version

The 1,048,576 row limit includes the header row, so the maximum number of data rows is technically 1,048,575.

If you're seeing a "file exceeds 65,536 rows" error, you may be saving in .xls format. Re-saving as .xlsx solves this — the limit jumps from 65K to 1M rows immediately.

What happens when you open an oversized CSV

When you open a CSV that exceeds the row limit by double-clicking it in Windows Explorer or Finder:

1. Excel shows a warning: "The data set is too large for the Excel grid"
2. The first 1,048,576 rows are loaded; everything after is discarded
3. The discarded rows are not stored anywhere — if you save the file as CSV, they're gone permanently

This is different from opening the same file through Power Query (Method 2), which reads the full CSV into memory before applying your filters — no rows are lost at the read stage.

The danger with double-click opening: the warning is easy to miss. It appears briefly and disappears. Many people proceed not realizing their data is incomplete, then build reports on a truncated dataset.

Saving a truncated file overwrites your original data

The most destructive scenario: you open a 2 million-row CSV, Excel loads the first 1,048,576 rows, and you save it back as a CSV. Excel writes only the rows it holds — the other 951,424 rows are erased from the file permanently.

Always keep a backup of the original CSV before opening it in Excel. Better yet, treat the original as read-only and export a separate copy for any Excel work.

Check whether your data is already truncated

If you've already opened a CSV in Excel and aren't sure whether data was cut off, compare the row counts:

Check CSV row count (PowerShell):

(Get-Content "large_data.csv" | Measure-Object -Line).Lines

Check CSV row count (Mac/Linux terminal):

wc -l large_data.csv

Compare the result to Excel's row count (press Ctrl+End to jump to the last active cell, or check the row number in the status bar). If the numbers don't match, rows were dropped.

---

One More Thing: Memory Limits Within the Row Limit

Even if your CSV is under 1,048,576 rows, Excel can still freeze or crash from memory pressure — especially with many formulas.

Rough thresholds (will vary based on formula complexity and available RAM):

These assume 20–50 columns. Fewer columns means more rows before trouble starts.

32-bit Excel has a 2 GB memory ceiling, which makes it prone to crashes with large files even when the row count is technically within limits. To check which version you have: go to File → Account → About Excel — the dialog header shows "32-bit" or "64-bit." If you work with large files regularly and are running the 32-bit version, switching to 64-bit is worth the upgrade.

---

Aggregation Pitfalls When Splitting CSVs

If you split a CSV and aggregate each part separately before combining results, some aggregations are mathematically safe and others are not.

Safe to combine across split files:

• SUM — add each file's total together
• COUNT — add each file's count together
• MAX / MIN — take the max of each file's max (or min of mins)

Not safe to combine naively:

• AVERAGE — averaging each file's average produces a wrong result unless all files have the same row count. Correct approach: sum all values, sum all counts, divide at the end
• DISTINCT COUNT — if the same ID appears in two files, counting distinct IDs per file and adding them double-counts. You need to deduplicate across the full dataset first
• MEDIAN — there's no way to derive the overall median from per-file medians. You need access to the full sorted dataset

For any of these aggregations, use Method 1 (online tool) or Method 2 (Power Query) to work with the full dataset directly.

---

Making Large CSV Work Sustainable

If you're doing this every month, the one-off fixes above become a recurring burden. These approaches reduce or eliminate the manual work.

Save your Power Query setup for reuse

A Power Query configuration — your filters, column selections, aggregations — is saved as a "query" inside the Excel workbook. Next month's process becomes:

1. Drop the new CSV into the same folder with the same filename (or set up a folder-based query)
2. Open the workbook
3. Click Data → Refresh All

The same transformations run automatically on the new data. If the filename changes each month, configure Power Query to read from a folder path instead of a specific file — it picks up whatever CSV is in the folder.

Ask the data source to fix the problem upstream

Large CSV problems often start with how data is exported. Consider asking whoever provides the file:

• Export a filtered or aggregated version — if you always filter to the last 3 months, ask for that directly instead of the full history
• Export as .xlsx instead of .csv — same row limit, but data types are preserved (no leading-zero loss, no date parsing issues)
• Provide an API endpoint — if the source system has an API, you can pull only the data you need directly into Excel via Power Query's web connector

The cleanest long-term fix is to stop receiving more data than you need.

Standardize on a browser-based CSV tool for your team

If teaching Power Query to everyone on the team isn't realistic, establishing a browser-based CSV tool as the team standard is a practical alternative.

The workflow — upload CSV to the tool, filter and pivot, export results to Excel — requires no special skills. Setting a team norm of "large CSVs go through the tool, not Excel directly" also prevents the accidental data-loss scenario where someone double-clicks a multi-million-row file and saves over it.

---

Summary

Excel's row limit is 1,048,576 rows. Open a larger CSV by double-clicking and Excel silently drops everything past that point — and if you save, those rows are gone from the file permanently.

Quick fixes, in order of effort:

1. Online CSV tool — filter, aggregate, and export from the browser. No setup, no code
2. Power Query — filter or aggregate before loading into Excel. Saves as a reusable query for next month
3. Split the CSV — divide into chunks under 1 million rows. Be careful with aggregations across split files
4. Python or PowerShell script — no row limit at all. Worth the setup cost if you run the same process monthly

Before doing anything: check whether your data is already truncated. Run a line count on the original CSV and compare it to what Excel shows. If the numbers differ, you're working with incomplete data.

And whatever you do, keep the original CSV file untouched until you're sure you have a safe working copy.