Excel's 1,048,576 row limit, explained and worked around

1,048,576 is 2 to the power of 20. 16,384 is 2 to the power of 14. Those are not business decisions, they are the widths of the fields Excel uses to address a cell, and they have been fixed since the XLSX format arrived in 2007.

Which means the number is not negotiable, and the interesting question is not how to raise it but what to do when your data is bigger than a worksheet.

Where the number comes from

Before 2007, a worksheet held 65,536 rows and 256 columns: 2^16 and 2^8. The XLSX format raised those to 2^20 rows and 2^14 columns. Row and column indices are stored as fixed-width numbers throughout the format and in the cell reference syntax, so raising the limit would mean a new file format, not a new build.

The last column is XFD. The last cell is XFD1048576. A worksheet can therefore hold about 17.2 billion cells, far more memory than any machine would give it, which is why you will hit performance walls long before you hit the theoretical maximum.

LibreOffice Calc has the same 1,048,576 row ceiling for compatibility. Google Sheets takes a different approach and caps total cells at 10 million, so its effective row limit depends on how many columns you have: about 250,000 rows for a 40-column export.

Symptom: the file opened and the totals are wrong

Cause. Excel does not refuse a CSV with more rows than a worksheet holds. It imports the first 1,048,576 data rows, shows a warning dialog once, and then behaves completely normally. Nothing in the workbook afterwards indicates that rows are missing.

I have seen this produce a quarterly report that was short by three weeks of data, reviewed by four people, none of whom had reason to suspect anything. The numbers were internally consistent. They were just computed on 82 percent of the rows.

Fix. Treat "the sheet ends at exactly 1,048,576" as a red flag, always. Get an independent row count from the source file before you trust a total. On macOS or Linux, wc -l file.csv is enough to answer the question, remembering that quoted newlines make it an upper bound rather than an exact row count.

Fix it: open the source file and read the parsed row count →

Five routes past the limit

In rough order of how often they are the right answer.

1. Aggregate before you open

This is nearly always the real answer, and it is the one people reach for last. Nobody needs to look at four million rows. They need a number per region, a trend per month, a list of the top fifty accounts. Compute the aggregate against the whole file, then open the twelve-row result in Excel and build the chart there.

SELECT DATE_TRUNC('month', order_date) AS month,
       region,
       COUNT(*)     AS orders,
       SUM(revenue) AS revenue
FROM data
GROUP BY 1, 2
ORDER BY 1, 2;

Fix it: run the aggregate in the browser and export the small result →

2. Filter to the slice that matters

A four million row file is often four million rows because it covers three years and eleven countries. If you need last quarter in Germany, filter first and the result fits a worksheet with room to spare. Filtering upstream is also faster than filtering in Excel, because Excel has to load the rows before it can hide them.

3. Split into parts

When the destination genuinely needs every row in a spreadsheet, split. 100,000 rows per part is a comfortable size. The two rules are that every part must carry the header row, and the split must be done by a parser rather than by counting newlines, because a quoted field can contain a newline and a byte-counting split will cut a row in half.

Fix it: split by row count with the header preserved →

4. Use the Data Model instead of the grid

Excel's Power Pivot data model is not bound by the worksheet limit, because the rows never become cells. Load the CSV through Power Query with Only Create Connection and Add to Data Model, and you can pivot over tens of millions of rows. What you give up is the ability to scroll the raw data, which for most reporting work is not a loss.

5. Stop using a spreadsheet for this

If the file arrives every week and it is always over a million rows, the tool is mismatched to the job. A browser-based workbench, DuckDB on your laptop, or a small database will all handle it without ceremony, and the analysis you build there can be re-run rather than rebuilt.

The other limit: 16,384 columns

Wide files hit the column cap long before the row cap. Survey exports with one column per question, sensor tables with one column per device, and pivoted financial models are the usual offenders.

The fix is almost always to unpivot: turn the wide table into a long one with a key column and a value column. Ten thousand columns becomes three columns and a lot more rows, which is both under the limit and much easier to analyze.

-- wide:  respondent_id, q1, q2, q3, ... q5000
-- long:  respondent_id, question, answer
SELECT respondent_id, question, answer
FROM data
UNPIVOT (answer FOR question IN (q1, q2, q3));

Fix it: unpivot a wide table into a long one →

Check what is really in the file

Three numbers, written down before you do anything else.

• Rows in the source. If it is above 1,048,576, a worksheet cannot hold it and no import setting changes that.
• Rows in your workbook. Ctrl and the down arrow from A1 takes you to the last populated row. Exactly 1,048,576 means truncated.
• Columns. Above a few hundred, plan to reshape rather than to widen.

And one habit worth adopting: when you send someone a large export, tell them the row count in the message. It costs you nothing and it is the single cheapest way to stop a silent truncation from becoming a decision.

Fix it: open the file and get the real row and column counts →