Why Excel Won't Open Your Big CSV File, and What to Do Instead (2026 Guide)

Diagnose it in 30 seconds: count the rows before you blame the file

The first thing to establish when Excel refuses or truncates your CSV file is how many rows the file actually holds, because the fix depends entirely on that number, and guessing wastes time. A file with 900,000 rows opens fine in Excel and the problem is something else, like a delimiter or an encoding issue, which the companion guides cover. A file with 1.2 million rows hits the grid limit, and no setting inside Excel will make it fit. Count first, then choose the fix that matches the count, instead of trying fixes at random.

The fastest count needs no software at all beyond a terminal. On Windows, open PowerShell and run Get-Content with Measure-Object, or on a Mac or Linux machine run wc -l on the file, which counts line endings. A text editor with a line count in the status bar works too, though a very large file may take a while to open in an editor. The number you get is lines, not data rows: a file with a header has one more line than data rows, and values that contain line breaks inside quotes can span more than one line, so the line count is an upper bound, not an exact row count, and for most exports it is exact enough to decide.

For an exact count, use the Python csv module, which reads the file one row at a time and never loads it into memory, so it works on files far too big for a spreadsheet. The snippet below counted a test file of 1,200,000 data rows in a couple of seconds and agreed exactly with the line count, because that file held no quoted line breaks. If the two counts disagree on your file, trust the csv module count: the difference is values with embedded line breaks, and it also tells you the file has quoted multi-line values, which matters for the split recipe later in this guide.

Terminal (Mac or Linux):
$ wc -l big_sample.csv
1200001 big_sample.csv

PowerShell (Windows):
PS> (Get-Content big_sample.csv | Measure-Object -Line).Lines
1200001

Python (exact row count, one row at a time):
import csv
with open('big_sample.csv', newline='') as f:
    reader = csv.reader(f)
    header = next(reader)
    rows = sum(1 for _ in reader)
print(header, rows)
# ['id', 'name', 'total'] 1200000

Lines minus the header row: 1,200,001 - 1 = 1,200,000 data rows.
That is 151,425 rows over Excel's limit.

Notice what this test file proves: a 1.2 million row CSV with three short columns is only about 32 MB on disk, so the problem is not file size in megabytes. People expect a big file to mean hundreds of megabytes, but a narrow file with a million short rows is small on disk and still too big for the grid. Row count is the number that matters, not megabytes, and that is why the count is the first step. If your file is over 1,048,575 data rows, you have a grid-limit problem, and the rest of this guide applies. If it is under that number, the problem is elsewhere, and the companion guides on the one-column problem, the encoding problem, and the leading-zeros problem are the places to look.

The one number that matters: 1,048,576 rows

Excel holds at most 1,048,576 rows by 16,384 columns on one worksheet, and a single cell holds at most 32,767 characters. Those are the published limits for the current spreadsheet format, and they have not changed in years. The header row counts as one of the 1,048,576, so a CSV file with a header can hold at most 1,048,575 data rows on one sheet. A file with 1,048,576 data rows plus a header is one row over, and Excel truncates it. The older .xls format is far smaller, at 65,536 rows per sheet, so if your file came from an old system, check the format too, but for a .csv file opened in a modern Excel, 1,048,576 is the number.

The CSV format itself sets no limit at all. A CSV file is plain text, rows separated by line breaks and values separated by a delimiter, and the format description, RFC 4180, says nothing about a maximum number of rows, columns, or bytes. A CSV file can hold as many rows as your disk has space for, and the test file in this guide holds 1.2 million rows in 32 MB without any problem. The limit comes from whatever opens the file, never from the file itself, which is why the same file opens fully in a text editor or a database and truncates in Excel. Blaming the file, or the program that exported it, misses the point: the exporter did its job, and the reader is the one with the ceiling.

Two other numbers shape the problem in practice. The first is the column count: 16,384 columns is generous, and almost no real CSV comes close to it, so columns are rarely the binding limit. The second is memory: Excel loads the rows it shows into memory, and a file near the row limit with wide rows can slow the program to a crawl or crash it, even when the row count is technically under the limit. A million rows of short values opens fine, while a million rows of long text values may not, so treat 1,048,576 as the hard ceiling and a few hundred thousand wide rows as the practical one. If your file is slow as well as big, the split recipe in this guide helps twice, because smaller files are faster files.

What Excel actually does when the file is too big

When you open a CSV file with more rows than the grid holds, Excel does not refuse the file. It loads the first 1,048,576 rows, including the header, and shows you a warning that the data set is too large for the grid and that saving the workbook will lose the data that was not loaded. Microsoft own support page phrases it exactly that way in every language: the data set exceeds the number of rows available in Excel, so some data was not loaded. The rest of your rows are still in the file on disk, untouched, but they are not in the workbook, and nothing on screen tells you which rows are missing except the count.

The silent part is what makes this dangerous. The workbook looks complete: it has a header, it has rows down to the bottom of the sheet, and the missing rows are simply absent, with no gap and no marker. If you sort, filter, or build a chart on that workbook, you are working on a partial data set and the results are wrong in a way that is hard to spot, because the first million rows often look representative. Totals, averages, and counts computed from the truncated workbook are all understated, and the error grows with every row past the limit. A file with 1.2 million rows loses about an eighth of its data silently, and a file with 5 million rows loses four fifths of it, while looking exactly the same on screen.

The destructive part happens when you save. Saving the truncated workbook writes back only the rows Excel loaded, so the rows that never made it into the grid are gone from the saved file permanently. The companion guide on saving covers this round trip in detail, but the rule here is absolute: never save over the original CSV from a workbook that warned you about the grid limit. If you already saved over the only copy, check OneDrive or SharePoint version history, a backup, an emailed attachment, or the source system the file came from, because the saved file no longer holds the missing rows and no formula can bring them back. Close the truncated workbook without saving, and start again from the original file with one of the fixes below.

The limits by app: Excel, Sheets, LibreOffice, and the tools with no limit

Not every program stops at the same place, so the right fix sometimes starts with a different program. Excel stops at 1,048,576 rows by 16,384 columns per worksheet, as above. LibreOffice Calc stops at the same 1,048,576 rows, but only 1,024 columns, so it is no help for a wide file either. Apple Numbers warns when a file exceeds about 1,000,000 lines, so it is in the same class. Google Sheets counts cells rather than rows: a spreadsheet can hold up to 20 million cells, and a CSV import is also capped at 100 MB, so a narrow file can hold more rows than Excel allows, while a wide file holds fewer, and the exact row limit depends on your column count. A 10-column file can hold about 2 million rows in Sheets, while a 50-column file holds about 400,000.

Text editors have no row limit of their own, only the memory of your machine, so a large CSV opens fully in Notepad, TextEdit, or VS Code as plain text, and you can search it, read the first and last rows, and check the header, though you cannot sort or filter it like a spreadsheet. That makes a text editor the right tool for inspecting a big file and the wrong tool for analyzing it. Python and its csv module have no row limit either, only memory, and because the csv module reads one row at a time, it can count, filter, and split files far larger than any spreadsheet, using almost no memory. The database route has no practical row limit at all: import the CSV into SQLite, Postgres, or even a desktop database, and a million rows is a small table.

The practical rule is to match the tool to the job, not to force the file into the tool you happen to have open. If you need to look at the data, use a text editor. If you need to filter or split it, use a script. If you need to analyze all of it in a grid, use the Data Model fix below, or a database. If you only need a summary, aggregate the file with a script and open the small summary in Excel. The file is not the problem, and the spreadsheet is not broken. The mismatch is between a million-row data set and a million-row grid, and the fix is to stop asking the grid to hold more than it can.

Fix 1: split the file into chunks Excel can open

Splitting is the fix to try first when you need the data in ordinary worksheets, because it is simple, it needs nothing beyond Python, and every chunk opens normally in Excel with its header intact. The recipe is to read the file one row at a time, write at most 1,048,575 data rows into each chunk, and repeat the header line at the top of every chunk, so each chunk is a complete CSV file on its own. Use 1,048,575 rather than 1,048,576, because the header counts as a row, and a chunk with 1,048,576 data rows plus a header would be one row over the limit again. The script below does exactly that, using only the Python standard library, so there is nothing to install.

The script was tested on the 1,200,000 row sample file from the diagnosis section, which is 32 MB on disk. It produced two chunks: the first holds the header plus 1,048,575 data rows, exactly 1,048,576 lines, and the second holds the header plus the remaining 151,425 data rows. Rejoining the chunks by stripping the repeated header from the second chunk and concatenating produced a file byte-identical to the original, confirmed by comparing file hashes, so the split loses nothing and changes nothing. A 5 million row file would split into five chunks the same way, and a 10 million row file into ten, with no upper limit except disk space.

Two details matter when you split. First, keep the header in every chunk, because a chunk without a header opens with the first data row as the header, and the column names vanish. Second, never split the file by opening it in Excel and copying rows by hand, because the manual copy starts from the truncated workbook and the rows past the limit are already missing. Split from the original file on disk, with a script, before Excel ever touches it. Once the chunks exist, open them one at a time, do your work, and keep the original file aside, because the chunks are working copies and the original is the only complete copy.

import csv

LIMIT = 1_048_575  # data rows per chunk (header counts as a row)

with open('big.csv', newline='') as f:
    reader = csv.reader(f)
    header = next(reader)
    chunk = 0
    count = 0
    out = open(f'chunk_{chunk}.csv', 'w', newline='')
    writer = csv.writer(out)
    writer.writerow(header)
    for row in reader:
        if count == LIMIT:
            out.close()
            chunk += 1
            count = 0
            out = open(f'chunk_{chunk}.csv', 'w', newline='')
            writer = csv.writer(out)
            writer.writerow(header)
        writer.writerow(row)
        count += 1
    out.close()

print(f'split into {chunk + 1} chunks')

# Verified on a 1,200,000-row file (32 MB):
# chunk_0.csv = 1,048,576 lines (header + 1,048,575 rows)
# chunk_1.csv = 151,426 lines (header + 151,425 rows)
# Rejoining the chunks (header stripped from chunk 1 onward)
# reproduced the original file byte-identically.

One more advantage of splitting deserves a mention: the chunks are independent files, so you can hand one chunk to a colleague, open one chunk on a slow machine, or process the chunks in parallel, and none of that is possible with a single file that no spreadsheet can open. If the file grows every week, keep the script and run it on each new export, instead of splitting by hand each time. And if you split because you need to send the data somewhere, remember that the chunks are the working copies: the original file is the one to archive, and the chunks are the ones to open.

Fix 2: load the whole file into the Data Model with Power Query

Splitting works when you need ordinary worksheets, but sometimes you need to analyze the whole data set at once, and for that Excel has a second door: the Data Model. Power Query can load a CSV file into the Data Model instead of the grid, and the Data Model uses compression and holds tens of millions of rows in memory, far past the 1,048,576 row grid limit. You cannot see all the rows on a sheet, because the grid limit still applies to what is displayed, but you can build PivotTables and PivotCharts over the full data set, and the aggregations count every row, including the ones the grid could never show. That is the right fix when the question is a summary, a total, or a pivot, rather than a row-by-row look.

The steps, on Excel for Windows, are deliberately boring: open a blank workbook, go to Data, then Get Data, then From File, then From Text/CSV, pick the file, and in the preview choose Load To, then select Only Create Connection and check Add this data to the Data Model, instead of loading to a table. Then build a PivotTable from the Data Model, and the pivot reads the full data set. The documented Microsoft guidance for data sets too large for the grid describes exactly this route, and it also warns, again, not to save over the original file, because the grid still holds only the first rows if you load to a table by mistake.

Two caveats belong here, stated plainly. The first is that the Data Model route needs Excel for Windows with Power Query, and Excel for Mac has limited support for creating and using Data Models, so Mac users may need to run Windows, or use the split recipe instead. The second is that a PivotTable over the Data Model answers aggregate questions, not row questions: you can total, count, and group the whole file, but you cannot scroll through row 3,000,000 on a sheet, because the sheet still stops at 1,048,576. If you need to see individual rows past the limit, split the file. If you need totals over all of them, use the Data Model.

Fix 3: look at only the rows you need

Most of the time, nobody actually needs all million rows on screen at once. You need the rows for one customer, one month, one region, or one error code, and the rest of the file is context you will never read. Filtering the file down before opening it is faster than splitting it, and it produces a small file that opens normally in Excel, with no chunks to manage. The filter runs in a script that reads one row at a time, so it works on files of any size, and the output is a complete CSV file with its own header, ready to open.

The recipe below uses only the Python standard library, and it was tested on the same 1,200,000 row sample file: keeping only the rows where the total column is greater than 90 produced a 74,227 line file, with 74,226 data rows, in about two seconds, and the first kept row is the one you would expect, the row where the total first passes 90. Change the condition to match your own question, a date range, a customer name, a status value, and the output is the slice of the file you actually need. The same pattern works for the first N rows, with a head command or a counter that stops after N rows, and for the last N rows, with a tail command, when you only need to check how a file ends.

Filtering also answers the question of whether the file is worth opening at all. Count the rows that match your condition first, and if the count is small, open the filtered file. If the count is still over the grid limit, filter further, or aggregate instead: group the file by month, by customer, or by status, and open the summary, which is a few hundred rows instead of a million. A summary is often the real deliverable anyway, and it is the file your colleagues can actually open, which is worth remembering when you send results to someone else.

import csv

kept = 0
with open('big.csv', newline='') as src, \
     open('filtered.csv', 'w', newline='') as dst:
    reader = csv.DictReader(src)
    writer = csv.DictWriter(dst, fieldnames=reader.fieldnames)
    writer.writeheader()
    for row in reader:
        if int(row['total']) > 90:
            writer.writerow(row)
            kept += 1
print(f'kept {kept} rows')

# Verified on the 1,200,000-row sample file:
# kept 74226 rows -> filtered.csv has 74,227 lines
# First kept row: 91,Customer 91,91
# The script reads one row at a time, so memory stays flat
# no matter how large the file is.

Notice what the filter proves about the file itself: nothing in the recipe cares how big the file is, because the script never holds more than one row in memory. That is the difference between a tool built for streams and a tool built for grids. A spreadsheet loads everything it shows into memory at once, which is why it has a limit. A streaming script processes one row, writes it or drops it, and moves on, which is why it has no practical limit. When the file is too big for the grid, reach for the streaming tool first, and open only the small result in the grid.

Fix 4: use the right tool for the job

If you keep hitting the grid limit, the honest answer may be that a spreadsheet is the wrong tool for the data set, and no fix inside Excel will change that. A text editor is the right tool for inspecting a big file: it opens the whole file as text, lets you search for a value, and shows you the first and last rows, with no row limit except memory. A database is the right tool for analyzing a big file: import the CSV into SQLite, which is a single file on your machine and needs no server, and a million rows becomes a small table you can query, filter, and aggregate with SQL, with no grid in the way at all.

The choice between those tools depends on the question. If the question is what is in the file, use a text editor. If the question is which rows match a condition, use a filter script. If the question is a total or a pivot over the whole file, use the Data Model or a database. If the question is a one-off look at a few rows, use head and tail in a terminal. Only if the question is row-by-row editing of a subset should the data be in a spreadsheet at all, and then only the subset, split or filtered down to fit. Forcing a million-row data set into a million-row grid is the mismatch, not the data set.

There is also a planning point here, for people who produce these files. If you export a CSV every week and it keeps growing past a million rows, stop exporting the whole history every time and export only the new rows, or only the summary, or split the export by month at the source. The export that fits in a spreadsheet is the export people can actually use, and an export that nobody can open is not a deliverable, however complete it is. Tell recipients the row count in the file name or in a one-line note with the file, so nobody discovers the limit by losing data to a truncated save.

Four fixes that do not work

Before the fixes that work, it is worth clearing away the four fixes that do not, because each one wastes time and one of them destroys data. The first is compressing the file into a ZIP. Compression shrinks the file on disk, sometimes dramatically, because CSV text compresses well, but the moment you extract it or import it, the raw data still holds the same number of rows, and Excel still stops at 1,048,576. Compression changes storage, not row count, and the grid limit counts rows, not megabytes.

The second is saving the file as .xlsx, or asking someone to convert it for you. The .xlsx format has the same 1,048,576 row grid limit as a CSV opened in Excel, because the limit belongs to the worksheet, not to the file format, so converting the file changes nothing about how many rows fit on a sheet. The third is opening the file in Google Sheets and hoping the cell limit saves you. Sheets can hold more rows than Excel when the file is narrow, because it counts 20 million cells rather than rows, but a wide file hits the cell cap sooner, a 100 MB import cap applies as well, and a 5 million row file exceeds Sheets too, so the fix only works for files just over the Excel limit, not for genuinely large ones.

The fourth is splitting the file by hand inside Excel, by copying rows into new sheets or new workbooks. That starts from the truncated workbook, where the rows past the limit are already missing, so the copy can never include them, and the manual copy is slow, error prone, and limited to the rows Excel loaded in the first place. Split from the original file on disk with a script, before Excel touches it, as the recipe above does. And the fix that actively destroys data, rather than merely failing, is saving over the original from the truncated workbook, which deletes the unloaded rows permanently, as the companion guide on saving explains in full.

What this means for files from this page converter

The converter on this page produces exactly the kind of file this guide is about, so it is worth stating its behavior plainly, verified against the real code rather than assumed. The conversion engine was run on this machine on a 10,000 row JSON sample and finished in 28 milliseconds, producing 10,000 rows and 3 columns, and on a 100,000 row sample it finished in 218 milliseconds, producing a 2.35 MB CSV. An earlier run in this same series measured 100,000 rows at about 0.4 seconds and 500,000 rows at about 1.4 seconds of engine time, so the converter itself handles large files comfortably, and the bottleneck is the program that opens the result, not the conversion.

That distinction matters, because it decides where to fix the problem. If your converted file will not open fully in Excel, the conversion did not fail and the file is not damaged: the file holds every row, byte for byte, and Excel loaded only the first 1,048,576 of them. Check the row count with the recipe at the top of this guide, and if the file is over the limit, split it with the script above, or load it through Power Query into the Data Model, or filter it down to the rows you need. Do not convert again hoping for a different result, because the converter will produce the same complete file, and Excel will truncate it the same way.

One more converter detail matters here. The download is plain UTF-8 with no byte-order mark, which is the cleanest form for most tools, and the converter lets you choose the delimiter before downloading. Neither choice affects the row limit in any way: a semicolon-delimited file truncates at exactly the same row as a comma-delimited one, and a UTF-8 file truncates at exactly the same row as a legacy-encoded one. If your converted file also opens in one column, or shows garbled characters, those are separate problems with separate fixes, covered in the companion guides linked below, and they can be fixed in the same import that handles the size. Fix the opening, split the file if needed, and never save over the download from Excel.

Engine check, run on this machine (2026-11-09):
10,000 rows -> 28 ms, 3 columns, 226,762 bytes
100,000 rows -> 218 ms, 3 columns, 2.35 MB

Sample output, first rows:
id,name,total
1,Customer 1,1
2,Customer 2,2

Earlier verified timings in this series:
100,000 rows / 10 MB JSON in about 0.4 s
500,000 rows / 25 MB JSON in about 1.4 s engine time

The converter finishes in milliseconds. The file it writes
is complete. Excel is the bottleneck, not the conversion.

If the file you need is the converted output itself, and it is too big for Excel, the cleanest path is often to keep the JSON and convert again whenever you need a fresh CSV, rather than keeping one giant CSV and splitting it every time. The JSON is the source, the CSV is a snapshot, and regenerating from source is always cleaner than repairing a file that has already passed through a spreadsheet. That is the same master-copy habit the companion guide on saving recommends, applied to large files: keep the source, export what you need, and split or filter the export, never the source.

Prevention checklist: produce CSVs people can actually open

Row limits are a mismatch between writer and reader, so prevention means making the writer declare the size, and making the reader check before trusting. Run through this checklist when you produce or receive a CSV, and the next person who touches it will not need this guide. First, count the rows before you send a file, and put the count in the file name or in a one-line note with it, so the recipient knows before opening whether the file fits in a spreadsheet. A file named customers-2026-11-09-1200000-rows.csv warns its reader; a file named customers.csv does not.

Second, split at the source when the export will exceed the limit. If the data comes from a database, export by month or by region, so each file fits in a grid on its own, instead of exporting the whole history into one file nobody can open. Third, keep the source data, whether that is JSON, a database, or a workbook, so a file that gets truncated or saved over can be regenerated cleanly instead of repaired. Fourth, never save over an original from a truncated workbook, and check every file you receive in a text editor or with a row count before letting any spreadsheet save over it.

Fifth, tell recipients how to open the file: a one-line note saying the file holds 1.2 million rows and should be split with the script in this guide, or loaded through Power Query into the Data Model, prevents the whole class of problems, and it costs nothing to include with the file. Most CSV horror stories end with someone discovering too late that the truncated file was the only copy, or that a summary was computed from a partial data set. Count first, split or filter before opening, and keep the original safe, and a big CSV becomes an ordinary file again.

Related guides

• Why Your CSV Opens in One Column in Excel, and How to Fix It (2026 Guide) →
• Why Saving Your CSV in Excel Changes It, and How to Keep the Original Safe (2026 Guide) →
• Why Excel Drops the Leading Zeros From Your CSV Codes, and How to Keep Them (2026 Guide) →

Frequently asked questions