You have a CSV. Someone exported it from a warehouse, a CRM, or a billing system, and it is large. You double-click it, Excel thinks for thirty seconds, and then either it gives up, or it opens and every scroll takes a second, or — worst of all — it opens cleanly and quietly throws away part of your data.
That last one is the dangerous case, so let’s start there.
What is the actual row limit in Excel?
A modern Excel worksheet holds 1,048,576 rows and 16,384 columns. Those numbers look arbitrary until you notice they are 2^20 and 2^14: the .xlsx format addresses each row and column with a fixed number of bits, and that is how many fit. The older .xls format used a narrower address space and stopped at 65,536 rows.
If your CSV has more rows than that, Excel imports the first 1,048,576 and discards the rest. It will tell you, once, in a dialog that is easy to click past. Any total you calculate afterwards is wrong, and nothing on screen says so. If you are working with exports that might be near a million rows, check the row count before you trust a number that came out of them.
Why does Excel struggle before it even reaches the limit?
Because opening a CSV is not like streaming it. Excel parses the whole file up front and builds an in-memory object for every single cell — its value, its type, its format, its position in the dependency graph used for recalculation. The working set is a multiple of the file size on disk. A 400 MB CSV does not need 400 MB of RAM; it needs several gigabytes, and it needs them all at once.
This is why the failure is so abrupt. You are not gradually slowing down as the file grows. You are fine, fine, fine, and then you are out of memory.
Option 1: Split the file
The bluntest fix, and sometimes the right one. On macOS or Linux, split will cut a file into chunks of a fixed number of lines:
split -l 500000 big.csv chunk_
Each chunk opens without complaint. The catch is that only the first one has the header row, and you now have a directory of files that have to be kept in sync. Splitting is good for a one-off look at the data. It is a bad place to live, because every question that spans the whole dataset now spans several files.
Option 2: Load it with Power Query instead of opening it
Excel has a mode that does not blow up: Data → Get Data → From Text/CSV. This loads the file through Power Query, which streams it rather than materialising every cell in the grid. You can filter, aggregate, and shape the data, then load only the result into a worksheet.
The row limit still applies to anything you load into a sheet, but it no longer applies to what you can process. If your goal is a pivot table over ten million rows, Power Query into the Data Model will get you there and opening the file never will. The cost is that it is a different tool with a different language (M), grafted onto the side of the one you know.
Option 3: Query the file in place
If you are comfortable with a terminal, the fastest path is to not load the file anywhere at all. DuckDB will run SQL directly against a CSV on disk:
SELECT country, sum(amount) FROM 'big.csv' GROUP BY country;
It reads only the columns the query touches and never holds the whole file in memory. Python with pandas or polars gets you the same place with more typing. This is genuinely the best option — provided the answer you want is one you can express in SQL, and provided you are the only person who needs it. Neither is usually true. Data that matters tends to be data other people need to look at, edit, and argue about.
Option 4: Put it in Google Sheets
Sometimes suggested, rarely a good idea for a file this size. A Google Sheets spreadsheet is capped at 10 million cells across all of its tabs, and blank cells count toward it. At 26 columns wide, that ceiling arrives at roughly 384,000 rows — well short of Excel’s limit. It will also feel slow long before it gets there, for reasons worth understanding on their own; we wrote those up in why Google Sheets slows down with large datasets.
Option 5: Import it into a database-backed grid
The reason a spreadsheet struggles with a large file is architectural: the whole dataset lives in the client. Every row is in your browser tab or your Excel process, whether or not you are looking at it.
Tablitsa takes the other approach. When you drop a CSV or XLSX file in, the rows are parsed and stored in a database, and the grid requests only the rows currently on screen as you scroll. Your machine holds a screenful of data instead of all of it. You still get a spreadsheet — cells you can click and type into, columns you can sort and filter — but the file is not what your laptop is holding open.
Two limits are worth knowing before you try it, because they are the ones that will stop you. Upload size is capped by plan: 5 MB on Free, 50 MB on Pro, and 500 MB on Team. Rows per sheet are capped at 1,000 on Free, 100,000 on Pro, and 1,000,000 on Team. A multi-gigabyte CSV will not upload; you would need to split it first, or push the rows in through the API.
Once the data is in, the thing that made the CSV painful — its size — mostly stops being your problem. You can ask it questions in plain English rather than writing a formula across a million rows, and you can share it with someone without emailing them a file.
So which one should you use?
- You need one number, once. DuckDB. It will take you five minutes and you will not think about the file again.
- You need a pivot table and you live in Excel. Power Query into the Data Model.
- You need to look at it once and never again. Split it.
- The data has a life ahead of it — people will edit it, filter it, ask it questions, and it will be re-exported next month — then stop treating it as a file. Put it somewhere that was built to hold it.
That last case is the one people misdiagnose most often. The CSV that crashes Excel is rarely a file problem. It is a sign that the data outgrew the tool a while ago and nobody noticed, because a spreadsheet degrades so gracefully right up until the moment it doesn’t.
If that is where you are: import the file into Tablitsa and see what it looks like when the rows live in a database. The Free plan takes a 5 MB file, which is enough to find out.