← All Kits · Excel Kit

This Data Set Is Too Large for the Grid: The Excel Row Limit and What to Do

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

An Excel worksheet holds 1,048,576 rows and 16,384 columns. Those are 2^20 and 2^14, chosen when the file format was rewritten for Excel 2007, and they have not moved since. When a file has more rows than that, Excel tells you that the data set is too large for the grid and offers you a partial load.

What you do first: find out whether anything was actually lost, because a truncated file that looks complete is a much bigger problem than a warning message.

The short version. If your row count is exactly 1,048,576, you are not looking at all your data.

Check whether you lost rows

1. Press Ctrl+End. If the cursor lands on row 1,048,576, treat the file as truncated until proved otherwise.
2. Count the source rows without opening it. In PowerShell: (Get-Content big.csv | Measure-Object -Line).Lines. On a very large file, Get-Content -ReadCount 10000 is faster.
3. Compare that count, minus one for the header, with what landed in the sheet.
4. Check the last date or ID in the sheet against the last one in the file. A truncated extract usually stops mid-period, which is the tell in any report built on top of it.

The four routes out

Aggregate first

The honest question is usually not about three million rows. It is about a few hundred summary rows. If the file comes from a system with an export screen, change the export to group by month and category before it lands. That solves the problem permanently and is the option people skip because it means asking somebody.

Power Query to the Data Model

Data, Get Data, From File, From Text/CSV, then Load To and choose Only Create Connection with Add this data to the Data Model ticked. The rows never touch a worksheet, so the million row limit does not apply. Build a PivotTable on the model and Excel aggregates over the whole file.

Two things to know. The model is compressed in memory, so a file that is 2GB on disk may hold in a few hundred megabytes, but 32-bit Excel will still run out at about 2GB of address space regardless. And the grid limit still applies to anything you drill through to.

Query the file where it sits

DuckDB reads a CSV directly with SQL and no import step:

SELECT region, COUNT(*) AS rows, SUM(amount) AS total
FROM 'big.csv'
GROUP BY region
ORDER BY total DESC;

That returns a handful of rows you can paste into Excel. Setup takes minutes and is covered in setting up DuckDB. For anything you will ask more than twice, load it into SQLite instead and keep it.

The column limit, and the reason you hit it

16,384 columns sounds unreachable, and it gets hit by exactly one thing: pivoting a long table wide on a field with too many values. One column per customer, or per day over several years. That is a sign the shape is wrong rather than the limit being tight. Keep the data long and let the pivot do the widening at read time.

Other limits worth knowing before you meet them

The 15 digit one catches people constantly: paste a 16 digit account number into a General cell and the last digit becomes zero, permanently. Format the column as Text before pasting, the same defence as in leading zeros on CSV import.

How to apply this to your own work

1. Add a row count to every workbook that starts from an import, and compare it to the source count. One cell, one habit.
2. When a file gets close to a million rows, stop opening it in Excel at all, even once.
3. Ask the system owner for an aggregated export before you build anything clever.
4. Learn the Data Model route once. It is fifteen minutes and it removes the limit for most reporting work.
5. Format ID columns as Text before any paste, and check the last digits of a few of them afterwards.

The one habit to keep

Never trust a row count you did not compute. The count is the cheapest check in analysis and it catches truncation, duplicated joins and failed filters, all of which look perfectly fine on screen.

Does the workbook you rely on most say anywhere how many rows it started with?