To open a large CSV file in Excel without losing rows, do not double-click it. Count its rows first. Under 1,048,576 rows, a normal open works. Over that, load it with Data, From Text/CSV, and send it to a PivotTable or the Data Model, or split the file into pieces that each fit on one worksheet.
Why does Excel drop rows from a large CSV file?
Excel drops rows from a large CSV file because a worksheet has a fixed size. Anything past the last row is never loaded onto the grid. A CSV has no such limit, so a file exported from a bank, a ledger or a converter can hold more lines than any single sheet can show.
Microsoft's Excel specifications and limits page gives the worksheet size as 1,048,576 rows by 16,384 columns. The header row counts as one of those rows, so the most data rows you can open directly is 1,048,575.
When you open a file that is too long, Excel loads what fits and warns you. Microsoft's article on what to do if a data set is too large for the Excel grid quotes the message: "This data set is too large for the Excel grid. If you save this workbook, you'll lose data that wasn't loaded." The rows past the limit are not hidden. They were never read into the sheet.
A few other things can make a large CSV look shorter than it is:
- Line breaks inside quoted fields. A description with an embedded line break counts as one record but two lines in a text editor.
- A stray quote character. An unclosed quote can make Excel treat many lines as one long field.
- The wrong delimiter. If Excel splits on commas but the file uses semicolons, every row lands in column A, which looks broken even when nothing is missing.
Counting a large CSV file's rows before Excel opens it
Count the rows before you open a large CSV, so you know whether it will fit and what total to check against afterwards. A line count from the command line takes seconds even on a big file, and it does not need Excel at all. Subtract one for the header row to get the number of records.
On Windows
Open PowerShell in the file's folder and run:
(Get-Content .\transactions.csv | Measure-Object -Line).Lines
This reads the whole file, so it can take a while on a very large one, but it never loads anything into Excel.
On a Mac or Linux
Open Terminal in the file's folder and run:
wc -l transactions.csv
wc -l counts newline characters. If the last line has no newline at the end, the count is one lower than the number of lines, so treat the result as approximate by one.
Why the count matters
Write the number down. After loading, you will compare it with what Excel holds. Microsoft's too-large article recommends exactly this: compare the source file's row and column counts with Excel's, which you can read from the status bar after selecting a whole row or column.
Loading a large CSV file through Power Query
Power Query loads a large CSV through a preview, so you can choose where the data goes before Excel tries to put it on a sheet. In Excel for Windows, go to Data, From Text/CSV, pick the file, and in the preview use the arrow beside Load to choose Load To. That one choice decides whether rows survive.
The steps:
- Open a blank workbook.
- Go to Data, then From Text/CSV.
- Select the file and choose Import.
- In the preview, check the delimiter and the data type detection. Dates and amounts should look right in the sample.
- Choose Transform Data if you need to fix types, remove columns you do not need, or filter rows. Fewer columns load faster.
- Back in the preview or the Power Query editor, open the arrow next to Load and choose Load To.
The Load To dialog offers a table on a worksheet, a PivotTable report, a PivotChart, or only a connection. A table on a worksheet still hits the row limit. The other options do not put rows on the grid, which is the point.
Filtering in Power Query is an underused option. If you only need one account or one year from a large export, filter it in the editor and load just those rows. A file of two million lines might hold only 40,000 that matter to you, and those fit on a sheet easily.
Keeping large CSV rows in the Data Model and a PivotTable
The Data Model holds rows outside the worksheet grid, so a large CSV can be analysed in full even though no sheet can display it. Load the file as a PivotTable report, or as a connection with "Add this data to the Data Model" ticked, and summarise from there. The grid limit no longer applies.
Microsoft's too-large article says that loading the data as a PivotTable report lets the PivotTable work with the complete data set, even though the grid cannot show every row. You can sort, filter and group without losing anything.
The Data Model has its own constraints, set by memory rather than rows. The Excel specifications page says 64-bit Excel is limited only by available memory and system resources, while 32-bit Excel has about 2 GB of address space shared with the workbook and add-ins, of which a Data Model can use roughly 500 to 700 MB. If a large load fails on an older computer, check whether you have 32-bit Excel.
A worked example
The figures below are an example, not a real file.
| Step | Example value |
|---|---|
| Lines in the CSV, from the command line | 1,312,401 |
| Records after removing the header | 1,312,400 |
| Rows a direct double-click open would load | 1,048,575 (263,825 records lost) |
| Records counted by the PivotTable from the Data Model | 1,312,400 |
| Records after filtering to one account in Power Query | 286,950, which fits on one sheet |
The PivotTable count matches the source, so nothing was lost. The filtered load fits on a sheet for row-by-row review.
Splitting a large CSV file into sheet-sized pieces
Split a large CSV file when you need every row visible in a grid, such as to edit transactions one by one. Cut it into pieces under the row limit, each with its own header, and open them separately. Split by a natural boundary such as year or account when you can, since those pieces are easier to check.
On a Mac or Linux, the split command cuts a file by line count:
tail -n +2 transactions.csv | split -l 1000000 - part_
That skips the header, then writes pieces of one million lines named part_aa, part_ab and so on. Add the header back to the top of each piece before opening, or Excel will treat the first transaction as column names.
On Windows without extra tools, Power Query can do the split for you: load the CSV, filter to one year, load that to a sheet, then repeat for the next year in a new query. Each query is a piece, and the source file is never changed.
Check the pieces
Add up the record counts of every piece and compare with the source count. If they differ, a split landed inside a quoted field with a line break. Splitting by year in Power Query avoids that, because it splits on whole records, not raw lines.
If the CSV came from a converted PDF and the columns look jumbled, the problem may be upstream. Sideways pages in the source PDF can scramble the columns, and converting a rotated PDF to Excel explains how to fix the page orientation before converting again.
Does manual calculation help a large CSV file open faster?
Manual calculation does not speed up the open itself, since a plain CSV holds no formulas. It helps afterwards, once you add formulas to hundreds of thousands of rows. Every edit then triggers a full recalculation, and switching to manual lets you make many changes before recalculating once.
Set it under Formulas, Calculation Options, Manual, and press F9 when you want the results updated. Setting Excel calculation to manual covers the trade-offs, including the risk of reading a total that has not been refreshed.
A faster route is to do the heavy work in Power Query rather than with worksheet formulas. Type changes, splitting columns and filtering in the editor run once at load, then the sheet holds plain values with nothing to recalculate.
Saving over a large CSV file after a partial load
Saving over a large CSV file after a partial load is the one mistake that cannot be undone. If Excel loaded only the first 1,048,575 records and you press Save, the file on disk is rewritten with only those records. Every row past the limit is gone from the source, not only from the screen.
Microsoft's too-large article warns against overwriting the original for this reason. If you must save the loaded part, use File, Save a Copy, and give it a name that shows it is incomplete, such as transactions_first_million.csv.
Habits that protect the source:
- Keep the original read-only. Right-click the file, open Properties, and tick Read-only on Windows, or lock it in Finder on a Mac.
- Work in a workbook, not the CSV. Load the data into a new .xlsx through Power Query. The CSV stays untouched as the source.
- Recount after every save. If the count drops, restore from a backup at once.
When the CSV comes from a statement converter, the cleanest fix is often a smaller export. A converter that produces one file per statement keeps each file well under the limit; one-click PDF to Excel conversion looks at when a single step is enough. FastBankConvert, which is getting ready to open, is built to return each statement as its own Excel, CSV or JSON file, so one month's export stays small; see the FastBankConvert homepage for a sample run.