Merging files larger than Excel’s row limit
By the Analistable team · Updated · 5 min read
A worksheet holds at most 1,048,576 rows, so a merged result bigger than that cannot live on a sheet. Merge with Power Query and load to the Data Model (connection only), which stores far more rows and feeds pivot tables, or skip the sheet entirely and query the files with a database such as DuckDB.
Part of our guide: How to merge Excel files: every method compared
The limits you are hitting
| Symptom | Cause |
|---|---|
| "File not loaded completely" when opening a CSV | More than 1,048,576 rows; the rest are dropped |
| Power Query load fails or stops at the row limit | Loading to a worksheet table |
| Excel freezes or crashes during the merge | Memory, especially in 32-bit Excel |
| The saved workbook is too large to open or email | Rows stored on sheets plus formatting and formulas |
The worksheet limit is 1,048,576 rows by 16,384 columns and has been since Excel 2007. The first warning matters most: when you open a CSV that is too long, Excel keeps the first 1,048,576 rows and drops the rest. Saving that sheet writes a truncated file.
| Item | Rows |
|---|---|
| Average export (one month) | 142000 |
| Twelve months combined | 1704000 |
| Worksheet capacity | 1048576 |
| Rows that would be lost on one sheet | 655424 |
Option 1: Power Query into the Data Model
- Put the files in one folder and choose Data > Get Data > From File > From Folder.
- Click Combine & Transform, check the column types, and remove any columns you don't need; fewer columns save more memory than anything else.
- Choose Close & Load To…, select Only Create Connection and tick Add this data to the Data Model.
- Insert a PivotTable from the Data Model (Insert > PivotTable > From Data Model).
The Data Model compresses data column by column and is not bound by the worksheet row limit, so it holds millions of rows in a file far smaller than the same data on sheets. Practical capacity depends on memory and on how repetitive the columns are, so test with your data. You can also relate lookup tables to it; see Data Model relationships.
If Excel still crashes, check File > Account > About Excel. 32-bit Excel can use only a limited amount of memory; 64-bit Excel can use what the machine has.
Option 2: keep it out of the workbook
If you need the rows themselves rather than a pivot, a worksheet is the wrong place. Merging the raw files at the command line is fast whatever the size (see merging large CSV files), and the result can be queried directly:
-- DuckDB: query every monthly file as one table without loading it into Excel
SELECT strftime(order_date, '%Y-%m') AS month,
count(*) AS orders,
sum(amount) AS revenue
FROM read_csv('exports/2026-*.csv', union_by_name = true)
GROUP BY month
ORDER BY month;Bring only the summary back to Excel. For Excel inputs, convert each workbook to CSV first or read them with pandas in chunks.
Analistable takes this approach: the files you load go into DuckDB running in your browser, and you ask questions in plain English, so a result of a few hundred rows comes back without the full data ever needing a worksheet. Browser memory is still a limit, so very large files are better handled by a desktop database.
Option 3: split the output
If someone needs the full rows in Excel, split by a natural key (one sheet or file per year or region), each under the limit. It works, but every later question needs all the parts, which is what Options 1 and 2 avoid.
Making a big merge less likely to crash
- Remove unneeded columns as the first step, before any merge or type change.
- Avoid whole-column formulas and conditional formatting on large sheets.
- Save as .xlsx or .xlsb, not CSV, if you must keep the result in Excel; .xlsb usually opens faster.
- Close other workbooks; each one shares the same Excel process memory.
The merge Excel files guide compares the smaller-scale methods.
Choosing between the options
| You need | Best route | Watch out for |
|---|---|---|
| Pivot tables over all rows | Power Query to the Data Model | Memory; trim columns first |
| A few summary figures | Query files with DuckDB or similar | Getting results back into Excel |
| Every row in Excel for someone else | Split by year or region | Questions spanning splits |
| A one-off merged CSV to send on | Command-line merge | Never open it in Excel to check |
The last row deserves emphasis. A merged CSV of 1.7 million rows is a perfectly valid file; it only becomes a problem when someone double-clicks it, Excel loads the first 1,048,576 rows, and the file is saved again. Check large CSVs with a row count instead: wc -l merged.csv on a Mac or Linux, or (Get-Content merged.csv | Measure-Object -Line).Lines in PowerShell (slow for very large files, but it does not load the file into Excel).
Excel does not always say when a Power Query load to a worksheet goes over the limit as clearly as the CSV warning does, so compare the row count shown in the Queries & Connections pane with the source total. If they differ, switch the load to connection only.
Finally, merging may not be needed at all. If the goal is to compare or total across files, a query that reads them in place (Power Query appending connections, or SQL over the folder) answers the question without ever creating the oversized combined file.
Frequently asked questions
- What is Excel's maximum number of rows?
- 1,048,576 rows and 16,384 columns per worksheet in Excel 2007 and later.
- Can the Data Model hold more than a million rows?
- Yes. It is not limited by the worksheet grid; its practical capacity depends on available memory and how well the data compresses.
- Why does Excel crash when merging large workbooks?
- Usually memory. 32-bit Excel, many open workbooks, wide tables and volatile formulas all make it worse. Load to a connection and the Data Model instead of sheets.
- Can Google Sheets handle more?
- No. Google Sheets has a cell limit per spreadsheet (10 million cells at the time of writing), which large merges hit quickly.