How to Open a CSV with More Than 1,048,576 Rows (Excel's Row Limit)

If your CSV exceeds Excel's row limit, opening it by double-clicking will silently discard the excess rows. Saving the file afterward erases them permanently.

Excel's maximum row count is 1,048,576 rows (just over 1 million). When you double-click a CSV that exceeds this limit, Excel loads the first 1,048,576 rows and drops everything else without a prominent warning. The brief alert that does appear vanishes quickly, so many people proceed without realizing their data is incomplete.

Google Sheets isn't a workaround either. Each spreadsheet caps at 10 million cells. A 50-column CSV hits that ceiling at 200,000 rows — well below Excel's limit. For wide datasets, Google Sheets is actually more restrictive. Performance degrades before reaching the cell limit, and uploading the file means sending data to Google's servers — which may conflict with internal data policies, GDPR, or CCPA.

A 2-million-row sales export opened in Excel loads only the first 1,048,576 rows. The remaining 951,424 rows are gone. Save the file, and the original CSV is overwritten with the truncated version.

Both Excel and Google Sheets fall short for large CSVs. This article covers five practical methods to handle oversized CSVs, ordered from easiest to most technical. Pick the one that fits your situation and skill level.

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

Quick Check First: Open the CSV in a Text Editor

Before choosing a method, open the CSV in a text editor to check the basics: column layout, delimiter, encoding, approximate row count.

A text editor opens CSVs as raw text — no row limit, no silent value conversion. Knowing what you're dealing with determines which method to use next.

Windows — EmEditor: Built for huge files. Opens 1GB+ CSVs in seconds. Its CSV mode aligns columns into a grid and supports column-level sorting and filtering. Paid, but the investment makes sense if you handle large CSVs routinely.

Windows — Notepad++ (free): Handles files up to roughly 2GB. Slower than EmEditor, but search and regex replace work well for locating specific rows.

Mac / Linux — Terminal:

# Show the first 100 rows
head -100 large_file.csv

# Count total rows
wc -l large_file.csv

Use text editors only for inspection. Once you know the column names and row count, move on to Methods 1–5 for filtering, aggregation, and export.

---

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

A browser-based CSV tool is the fastest way to open, filter, and aggregate a large CSV. No installation, no coding, no Power Query setup.

These tools bypass Excel's row limit entirely. You drag the CSV into the browser, apply filters or pivot aggregations to compress millions of rows into a manageable summary, 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.

If you've hit Excel's row limit and need answers now, start here. You can always move to Power Query or Python later — but this gets you working in under a minute.

---

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

If you want to stay inside Excel, Power Query is the answer. It reads CSVs with millions of rows and lets you filter or aggregate before anything hits the worksheet.

Power Query is built into Excel 2016 and later. Unlike double-clicking a CSV, Power Query reads the full file into memory first. You apply filters or Group By operations in the Power Query Editor, and only the processed result — now under 1,048,576 rows — loads into the worksheet.

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.

Power Query configurations are saved as "queries" inside the workbook. If you process the same CSV monthly, set it up once and click Data → Refresh All next time. See the "Making Large CSV Work Sustainable" section below for details.

---

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

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

Each split file stays within Excel's row limit, so you can open them normally by double-clicking. No special tools or coding knowledge required.

Mac / Linux (terminal)

mkdir -p split_out
head -1 large_file.csv > split_out/header.csv
tail -n +2 large_file.csv | split -l 900000 - split_out/chunk_
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

@($header) + (Get-Content large_file.csv | Select-Object -Skip 1 -First 900000) |
  Set-Content part1.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. 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.

Treat splitting as a "browse only" approach. If you need to aggregate across the full dataset, use Method 1 or Method 2 instead — averaging or counting uniques across split files produces wrong results (see "Aggregation Pitfalls" below).

---

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

Scripts remove the row limit entirely. If you can write basic Python or PowerShell, you can process tens of millions of rows and output a clean Excel summary.

Neither pandas nor PowerShell has a row limit. You read the CSV, aggregate in code, and write only the summary to Excel. Once the script works, you can schedule it to run automatically — eliminating monthly manual work.

Python (pandas)

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

import pandas as pd

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

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)

result = pd.concat(chunks).groupby('category').agg(
    sales_total=('sales_total', 'sum'),
    quantity_sum=('quantity_sum', 'sum'),
    count=('count', 'sum')
).reset_index()

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.

If you run this process monthly, register the script in Windows Task Scheduler or cron. A recurring CSV aggregation that takes 30 minutes of manual work becomes a background task that runs automatically.

---

Method 5: Import into a SQL Database (★☆☆)

When you need to run different queries against the same large CSV repeatedly, load it into a SQL database once. Every subsequent query skips the CSV parsing step.

With Python scripts, every query variation requires re-reading the CSV. A SQL database stores the data in an optimized format, so queries execute in seconds. SQL syntax also handles complex filtering, aggregation, and JOINs more naturally than pandas code.

SQLite (minimal setup)

SQLite is a serverless, file-based database. If Python is already installed, no additional setup is needed.

import sqlite3
import csv

conn = sqlite3.connect('data.db')
cur = conn.cursor()

cur.execute('''
    CREATE TABLE IF NOT EXISTS sales (
        date TEXT, region TEXT, product TEXT,
        quantity INTEGER, revenue REAL
    )
''')

with open('large_data.csv', 'r', encoding='utf-8') as f:
    reader = csv.reader(f)
    next(reader)
    cur.executemany(
        'INSERT INTO sales VALUES (?,?,?,?,?)', reader
    )
conn.commit()

for row in cur.execute('''
    SELECT region, SUM(revenue) as total
    FROM sales GROUP BY region ORDER BY total DESC
'''):
    print(row)

conn.close()

Once the data is in data.db, you can try different WHERE clauses and GROUP BY keys instantly — no CSV re-read each time.

PostgreSQL / MySQL

Server-based databases make sense when a team needs shared access to the same dataset, or when you need to JOIN multiple large CSVs. The setup cost is higher (server configuration, schema design).

Reserve SQL for scenarios where you're iterating on queries against the same dataset. For a one-off monthly analysis, Method 1 (browser tool) or Method 2 (Power Query) will get you there faster with far less setup.

---

Excel Row Limits by Version

Excel's row limit depends on the file format. Knowing your version's limit is the first step to understanding whether your data is being truncated.

The .xls format (Excel 2003 and earlier) has a much lower limit than .xlsx (Excel 2007 and later). Some organizations still use .xls for compatibility reasons, hitting the 65K ceiling without realizing a simple format change would solve the problem.

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 jumps the limit from 65K to over 1 million rows immediately.

Check your file extension first. If it's .xls, convert to .xlsx before trying any other workaround — that alone might fix the problem.

What happens when you open an oversized CSV

Double-clicking a CSV that exceeds the row limit causes Excel to silently discard the excess rows.

The sequence is specific:

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: the warning appears briefly and disappears. Many people proceed not realizing their data is incomplete, then build reports on a truncated dataset.

When working with large CSVs, make Power Query (Method 2) your default entry point instead of double-clicking. That single habit change eliminates the truncation risk.

Saving a truncated file overwrites your original data

Saving a truncated CSV from Excel overwrites the original file, permanently erasing the rows Excel couldn't load.

CSV is a plain text format. Excel writes only the rows it holds — 1,048,576 at most. The rest are gone from the file with no undo.

Open a 2 million-row CSV, Excel loads 1,048,576. Save it as CSV. The file now has 1,048,576 rows. The other 951,424 are erased.

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

You can verify whether data was cut off by comparing the original CSV's row count with what Excel shows.

CSV is a text file, so a simple command returns the exact line count. If it doesn't match Excel's row count, data was dropped.

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.

Run this check immediately after opening any large CSV in Excel. If the counts don't match, re-import the data using one of Methods 1–5 before proceeding with your analysis.

---

One More Thing: Memory Limits Within the Row Limit

Even CSVs under 1,048,576 rows can freeze or crash Excel if the data is wide or formula-heavy. The real bottleneck is memory, not just row count.

Memory consumption scales with cell count (rows × columns), not just row count. Heavy formulas like VLOOKUP and SUMIFS multiply the load further.

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 your CSV has many columns, use Power Query to select only the columns you need before loading. If you're on 32-bit Excel and work with large files regularly, switching to 64-bit is worth the upgrade.

---

Aggregation Pitfalls When Splitting CSVs

Some aggregations are safe to combine across split files, others are not. Knowing the difference before you start prevents incorrect results.

SUM and COUNT are additive — you can total each file's result and get the correct answer. But AVERAGE, DISTINCT COUNT, and MEDIAN are not additive. Combining per-file results produces mathematically wrong numbers.

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

If your analysis requires AVERAGE, DISTINCT COUNT, or MEDIAN, do not use Method 3 (splitting). Use Method 1 (online tool) or Method 2 (Power Query) to work with the full dataset directly.

---

Handling Confidential Data in Large CSVs

When your CSV contains sensitive data, where the file is processed matters as much as how it's processed. Not all tools handle data the same way.

Most browser-based CSV tools — including Google Sheets — upload the file to a remote server. This is convenient, but creates risk: data is stored (at least temporarily) on infrastructure you don't control, and your data's fate depends on the provider's privacy policy.

The risk of cloud-upload tools

Cloud-upload tools transmit your data to external servers during processing.

This may violate internal data handling policies, GDPR, or CCPA requirements. The risk compounds with services that advertise AI-powered analysis — some use uploaded data for model training.

Uploading a customer list or revenue breakdown to a third-party service without checking the terms of service is a compliance risk, even if the tool's features are excellent.

Before using any external tool, check the FAQ or privacy policy for explicit statements about data handling. If your organization prohibits external uploads, choose a local-only alternative below.

Local-only processing as an alternative

Several approaches keep data entirely on your machine — no server upload, no network transfer.

When data never leaves your device, internal data policies and regulations like GDPR become non-issues for the tool itself. Recent browser technologies like WebAssembly make it possible to build powerful tools that run entirely client-side.

• Text editors (EmEditor, Notepad++): the file never leaves your disk
• Python / PowerShell scripts: code and data both stay on your PC
• Browser-based tools with client-side processing: the tool runs in your browser, but the file is processed locally — no server upload

LeapRows (disclosure: built by the author) uses this approach. CSV files are processed by a DuckDB-WASM engine running inside the browser. No data crosses the network. Closing the tab clears everything from memory.

"Runs in the browser" does not automatically mean "uploads to a server." When choosing a tool for sensitive data, look for explicit statements about client-side or local-only processing in the FAQ or privacy policy.

---

Making Large CSV Work Sustainable

If you process the same type of large CSV every month, one-off workarounds become a recurring time sink. Build the fix into your workflow so it runs automatically.

Automation eliminates manual errors, prevents data loss from accidental double-click opening, and removes dependency on any single person knowing the process.

Save your Power Query setup for reuse

A Power Query configuration is saved as a "query" inside the workbook. Next month, you click one button instead of rebuilding the setup.

The query definition — your filters, column selections, aggregations — persists in the Excel file. Only the source data changes.

Next month's process:

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.

When setting up Power Query for the first time, build it with reuse in mind. Name the query clearly and document the filter logic. Your 10-minute setup investment pays back 30 minutes every month.

Ask the data source to fix the problem upstream

The root cause of the large-CSV problem is often receiving more data than you need. If the source can export a filtered or aggregated version, the problem disappears entirely.

As long as you receive a raw multi-million-row CSV, you'll need a workaround every time. Getting the source to deliver data in a usable format eliminates the entire processing step.

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

Next time you meet with the data provider, ask: "Can you export just the last 3 months, or a monthly summary?" They may be spending time generating the giant file too — a smaller export benefits both sides.

Standardize on a browser-based CSV tool for your team

If teaching Power Query to everyone isn't realistic, establish a single browser-based CSV tool as the team standard.

When the team lacks a shared process, some people open large CSVs in Excel (risking data loss), others upload to Google Sheets (risking privacy), and others use ad-hoc splitting tools. Report numbers stop matching. A unified workflow prevents all of this.

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.

Pick one tool — any tool — and make it the team standard. The specific tool matters less than having a single, shared process that everyone follows.

---

Summary

Excel's row limit is 1,048,576 rows. Google Sheets caps at 10 million cells. Neither is designed to open large CSVs directly.

Opening an oversized CSV by double-clicking silently truncates data. Saving it erases the excess permanently. These risks are entirely avoidable with the right method.

The five methods, 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. Browse-only; be careful with aggregations
4. Python or PowerShell script — no row limit at all. Worth the setup cost for monthly processes
5. SQL database — load once, query repeatedly. Best for iterative analysis against the same dataset

If your CSV contains confidential data, verify whether the tool uploads files to a server or processes them locally.

Start by checking your CSV's row count with PowerShell or terminal. If it exceeds 1,048,576 rows, pick the method that matches your skill level and urgency. And whatever you do, keep the original CSV file untouched until you have a safe working copy.