📁 File Limits · 8 min read

How to Open a CSV File That's Too Big for Excel

If Excel will not open the file at all, split it first. A splitter turns one oversized CSV into parts that open normally, in about a minute, and it is the only route that ends with a file you can hand to somebody else. If Excel does open it but the sheet stops at row 1,048,576, the file was not opened, it was truncated, and the missing rows are not hiding further down the sheet.

Those two answers cover the two questions people actually search for: “How do I open a CSV file that is too large for Excel?” and “How can I open a CSV file with over 1 million rows in Excel?” Which one you have matters, because the fixes are not the same, and one of them is not a fix at all.

Match the symptom before you pick a fix

The bottom two rows are worth separating out, because people who hit them often assume the file is too big and go looking for a bigger tool. A one-column import is usually a semicolon-delimited export or a character-encoding mismatch, and it happens on files of every size.

What the ceiling actually is

The row ceiling belongs to the worksheet grid, not to your file and not to your disk. RFC 4180 describes how a CSV handles quoting and line endings and says nothing about how many rows one may contain, which is why exports from billing systems, ad platforms and monitoring tools routinely cross a million rows without anybody noticing until they open one.

Opening a file is a separate limit from displaying one. Both need memory, and the 32-bit build of Excel tops out at 2 GB of addressable memory no matter how much RAM the machine has. The 64-bit build is bound by installed memory instead. That is why the same 1.2 GB export opens on one laptop and hangs on another with a bigger drive, and why the file size matters more than the row count when the symptom is a crash rather than a short sheet.

Two numbers catch people out. Google Sheets is limited by cells rather than rows: 10 million cells per spreadsheet on the free tier, so a six-column file holds roughly 1.6 million rows. That is more than Excel and still the same order of magnitude. And a CSV is often larger than the equivalent workbook, because a .xlsx is a compressed package built on XML that stores repeated values efficiently while a CSV writes every character out in full. Shrinking the file does not raise the ceiling: a 900 MB CSV can convert to a 90 MB workbook and still be truncated at row 1,048,576.

Four routes, ranked by effort

1. Split it, in the browser

The CSV splitter reads the file in your browser and writes out parts by row count or by target file size. Nothing is uploaded, so a client export with names and addresses in it never leaves the machine, and each part repeats the header row so it opens as a normal file. This is the route to take when the goal is to look at the data in Excel or send a section to somebody who will.

If you would rather not use a browser tool, the other splitting methods cover the command line and spreadsheet approaches, along with a plain-text editor route for files you only need to peek at.

2. Read it without loading the grid

Power Query can hand a file to another engine and never put it on a worksheet. Import with Data → Get Data → From Text/CSV, then use Close & Load To… → Only Create Connection. The rows are available to PivotTables and DAX measures without ever occupying cells, which sidesteps the grid limit rather than working around it.

The trade-off is that you lose the familiar grid. You cannot scroll to row 4,000,000, so this suits sums, groupings, joins and filters and is a poor fit for eyeballing a specific record. For that, a browser CSV viewer will show you a file that Excel refuses, because it never has to render the whole thing at once.

3. Use a tool that reads from disk

DuckDB queries a CSV in place, streaming it from disk, and handles files that are far past what any spreadsheet will hold. Power BI Desktop wraps the same query engine in a friendlier shell on Windows. Both are a real step up in capability and a real step out of the workflow you already know, which matters if the only thing you needed was a row count or a total.

4. Move it into a database

If the same oversized export arrives every month, the file is a symptom rather than the problem: the data wants to live somewhere that is not a spreadsheet. Loading it into SQLite or a server database costs an afternoon the first time and nothing afterwards. The SQLite route has no server to set up, and querying a CSV with SQL is worth a look if you want the query part without the import.

Why the split is usually the first thing to try

It is the only option on the list that produces a normal file. The other three give you an answer, a query or a different application; only a split gives you something you can email. It also needs no installation, which is what makes it the sensible first move even when the long-term answer is a database.

Two things go wrong with a careless split. The first is the header row: every part needs it, or the second file onward has no column names. The second is more subtle. A field wrapped in double quotes is allowed to contain a comma, a double quote or a line break, so a splitter that simply counts newline characters can cut a single record across two files. Use a splitter that parses the file as CSV, and check afterwards that the row counts of the parts add up to the original — counting the rows without opening the file in Excel takes a few seconds and catches both mistakes.

One calculation trap: totals, averages and distinct counts taken per part do not add up to the whole-file figure. If you need an aggregate over the entire data set, compute it before you split, or run it in a tool that reads all the rows at once.

When the deliverable is an answer, not a workbook

Plenty of oversized files are opened once, to answer one question: what was the total, which region grew, where is the outlier. That question does not need 1,048,576 cells. NoCodeCSV takes the file, answers the question and returns a chart, and there is no grid involved, so the row limit never comes into it.

This is a different thing from pasting the data into a chat assistant. A language model has to fit the file into a context window measured in tokens, and CSV text is expensive because every comma and repeated column heading is charged for. That is the usual reason a large export produces a vague or invented answer. The row limit explainer goes into that failure mode in more detail, and the numbers behind the grid ceiling.

Check the file before you fight it

Two facts decide the route: how many rows the file has, and how large it is. The size you can read from a right-click (Windows) or Get Info (macOS). The row count does not require opening it in Excel, which is useful when Excel is the thing that is failing. Once you have both, the symptom table at the top of this page picks the route for you.

Frequently asked questions

How big of a CSV file can Excel open?

The CSV format defines no size limit, so the ceiling comes from two other places: the worksheet, which holds 1,048,576 rows by 16,384 columns, and memory. A 32-bit install of Excel can address only 2 GB, and parsing text needs more room than the file takes on disk, which is why a 1 GB export sometimes hangs while a 700 MB one opens fine.

How do I open a CSV file with more than 1 million rows in Excel?

Either split the file into parts that sit under the limit, or import it with Power Query and load the result to the Data Model, which is not capped at 1,048,576 rows. Both keep you inside Excel. Only the split gives you a grid you can scroll.

Can Excel open a 2 GB CSV file?

Not in any useful way. Rows past 1,048,576 are discarded whichever import route you take, and at that size the import usually runs out of memory or times out before you can check what was dropped. Splitting the file by size is the practical answer.

Why does Excel say the data set is too large for the Excel grid?

That message appears when a query result is pointed at a worksheet. Send the query output to the Data Model instead. Microsoft documents loading to the Data Model as the supported workaround when a data set exceeds the grid.

Can Excel handle 10 million rows?

No. A .xlsx worksheet stops at 1,048,576 rows and 16,384 columns, and the older .xls format stops at 65,536 rows and 256 columns. Ten million rows belongs in DuckDB, Power BI or a database, where the file is read from disk instead of laid out in cells.

Does splitting a CSV lose the header row or break quoted fields?

Not if the splitter parses the file as CSV rather than as plain lines. Every part should repeat the header row, and a quoted field is allowed to contain a comma or a line break, so a tool that counts newline characters can cut one record across two files. Use a splitter that understands quoting, then check that the row counts of the parts add up to the original.

How can I open a large CSV file without losing data?

Work out which limit you are against. If it is the grid, the sheet holds only the first 1,048,576 rows and the rest were dropped without an error, so scrolling will never reveal them. Split the file, load it to the Data Model, or read it without rendering it into cells.

How do I reduce the size of a CSV file?

Convert it to .xlsx or split it. A workbook is a compressed package and stores repeated values far more efficiently than plain text, so a file that is hundreds of megabytes as CSV can shrink to a fraction of that. Converting does not raise the row ceiling, though: the workbook still stops at 1,048,576 rows and will truncate a larger file on import.