"This Data Set Is Too Large for the Excel Grid": What It Means for Your CSV and How Not to Lose Rows

The warning “This data set is too large for the Excel grid. If you save this workbook, you’ll lose data that wasn’t loaded” means your CSV has more rows or columns than an Excel worksheet can hold, which is 1,048,576 rows by 16,384 columns, so Excel opened only part of it. Do not save over the original file. To analyze every row, load it with Excel’s Get Data into a PivotTable, or open it in a tool without the grid limit. CSVPrune from Automata Labs is a local CSV viewer and cleaner for Windows that opens large files without Excel’s row limit, so you can filter, de-duplicate and trim them, then export a file Excel can handle.

Open the whole CSV, cut it down to what you need, and export a clean copy.

Get it from Microsoft

What does “too large for the Excel grid” actually mean?

Microsoft’s support article What to do if a data set is too large for the Excel grid explains it directly: “This means the dataset exceeds the number of rows or columns that’s available in Excel, so some data wasn’t loaded.”

The grid is fixed. Microsoft’s Excel specifications and limits lists the total number of rows and columns on a worksheet as “1,048,576 rows by 16,384 columns.” No setting raises it. The older .xls format is smaller still: Microsoft notes it “has a limit of 65,536 rows in each sheet.”

So the spreadsheet in front of you looks complete, but everything past the last row the grid can hold is simply not there.

What should I do right now?

Microsoft lists three precautions. In plain terms:

1. Do not save the file in Excel. “If you save over the original file, you’ll lose any data that wasn’t loaded.” This is the one that causes real damage, because a CSV saved from a truncated workbook is permanently shorter.
2. If you must save, save a copy. Use File, Save a Copy, and give it a name that says it is truncated.
3. Reopen the data in a way that loads all of it. Either with Excel’s Get Data feature (below) or in another tool.

Then make a backup copy of the original CSV before you do anything else with it.

How do I know how many rows my CSV really has?

Count before you choose a fix, because 1.1 million rows and 40 million rows need different approaches.

In PowerShell, this counts lines in a file:

(Get-Content .\export.csv | Measure-Object -Line).Lines

Measure-Object with -Line “counts the number of lines in the input objects.” Two caveats. It can be slow on very large files. And a line is not always a row: if a quoted field contains a line break, one row spans several lines, so the line count is higher than the row count. We explain how quoted line breaks work in RFC 4180 CSV rules.

Once a file is open in Excel, Microsoft suggests checking the count by selecting an entire column and reading the count in the status bar.

How do I open the full file in Excel?

Excel for Windows can load more data than the grid shows by using Power Query. Microsoft’s steps:

1. Open a blank workbook.
2. On the Data tab, select From Text/CSV in the Get and Transform Data group.
3. Find the file and select Import.
4. In the preview, choose Load To and pick PivotTable Report. If you do not see Load To, use the arrow next to Load.
5. Build your PivotTable from the field list. Microsoft says it “will work with your entire data set to summarize your data.”

The important limit, in Microsoft’s words: “the data still won’t display more than the number of rows and columns in Excel.” This method is for summarizing, such as totals by month or counts by region. It is not a way to scroll, edit cells, or eyeball every row.

What are my options if I need to see or clean every row?

Option	Sees every row	Good for	Watch out for
Excel Get Data to PivotTable	Summaries only	Totals, counts, grouping	Cannot browse or edit individual rows
Split the file into parts under the limit	Yes, across several files	Handing data to Excel users	Repeating headers, keeping related rows together
Filter or reduce before Excel	Yes, in the reduced file	When you only need a subset	You need a tool that can open the whole file first
A database or scripting language	Yes	Very large or repeated jobs	Setup and skills
A CSV tool without the grid limit	Yes	Browsing, cleaning, de-duplicating	Memory on very large files

The most common real need is the third row. You rarely need all 1.5 million rows in Excel. You need last quarter’s orders, one region, no duplicates, and five of the forty columns. Get the file down to that, and Excel is happy again.

How does CSVPrune handle a CSV that is too big for Excel?

CSVPrune opens the file in its own grid rather than Excel’s. Its Microsoft Store listing and product page describe:

• No row-limit wall. The listing promises “no row-limit wall, no freezing,” and the product page says there is “no 1,048,576-row wall silently truncating your data.”
• Fast browsing. Scroll, search, filter and sort 100,000+ row files, with type-aware sorting for numbers and dates.
• Filters and profiling. Profile each column for types, blanks, distinct counts and invalid values, and filter to exactly the rows you need.
• De-duplication. Remove exact duplicates, or catch near-duplicates with fuzzy matching.
• Encoding and delimiter fixes for UTF-8, UTF-16 and Latin-1 files, and comma, semicolon, tab or pipe separators.
• Leading zeros and long IDs kept intact, which Excel often changes.
• Recipes: save your cleaning steps and replay them on next week’s export.
• Export to CSV, JSON or XLSX.
• Private by design: no cloud, no telemetry and no network calls. Everything runs in memory on your PC.

A practical workflow:

1. Open the original CSV in CSVPrune.
2. Filter to the rows you need, remove duplicates, and drop rows you do not need.
3. Check the row count in the run stats.
4. Export to CSV or XLSX and open that in Excel.

Two honest limits. First, CSVPrune keeps the data in memory, so very large files depend on your PC’s RAM. Our listing and product page talk about 100,000+ rows; if your file has many millions, test it on a copy before you rely on it. Second, an XLSX export is still an Excel file, so it is still subject to the 1,048,576-row worksheet limit. Reduce first, then export.

Filter, de-duplicate and trim a big export locally, then hand Excel a file it can open.

Get it from Microsoft

Why did my numbers change too?

If the file is large enough to hit the grid limit, it often has other problems Excel causes on open: long IDs shown in scientific notation, and leading zeros removed from codes. Microsoft’s limits page lists Excel’s number precision as 15 digits, so longer numeric IDs cannot be stored exactly as numbers. Our guides to Excel removing leading zeros from CSV files and CSV UTF-8 vs CSV comma delimited cover the related fixes.

The safe habit is the same for all of these: keep the original export untouched, and do your cleaning on a copy in a tool that does not reinterpret your values.

How do I avoid this next time?

• Export less. Many systems let you filter by date range or columns before export.
• Export in parts, such as one file per month, if the system allows it.
• Keep the original CSV read-only, so an accidental save in Excel cannot shorten it.
• Save your cleaning steps as a recipe, so the reduced file takes seconds next time.
• If you only need totals, go straight to Get Data and a PivotTable.

If you often compare two exports to find what changed, our guide to comparing two lists in Excel is a useful next read.

Frequently asked questions

What is the maximum number of rows in Excel?

1,048,576 rows by 16,384 columns per worksheet, according to Microsoft’s Excel specifications and limits. The older .xls format holds 65,536 rows.

If I click OK and keep working, is my data safe?

The original file is safe as long as you do not save over it. The workbook in front of you is incomplete, so totals and counts in it cover only the loaded rows.

Can I increase Excel’s row limit?

No. The grid size is fixed. Power Query can load more data into a PivotTable, but the worksheet still shows at most 1,048,576 rows.

Will splitting the CSV in two fix it?

It will let Excel open each part, if each part is under the limit and includes the header row. Calculations across the parts then need combining.

Does CSVPrune upload my file?

No. It has no network code and processes the file in local memory.

Try CSVPrune

Get CSVPrune from the Microsoft Store. It is a one-time purchase with no subscription, and the Store listing shows the current price.

Open the CSV Excel could not, clean it, and keep it on your PC.

Get it from Microsoft

Microsoft and Excel are trademarks of the Microsoft group of companies. CSVPrune is not affiliated with or endorsed by Microsoft.

Last updated 22 October 2026. Published by Automata Labs, a software company in Canada.