An Excel worksheet holds a maximum of 1,048,576 rows. When your reference data grows past that — or you open a CSV export that is larger — Excel loads the first 1,048,576 rows and silently discards the rest. There is no error in the data, no red flag on the sheet. The missing records simply are not there, and everything you build on that sheet is now based on a partial list.
For a transaction log, losing the oldest rows is annoying. For master data it is a different kind of problem. A truncated master list means a real customer, a real product, a real GL code is missing from what your organization treats as the single source of truth — and every downstream system that reads from it inherits the gap without anyone deciding to leave those records out.
1,048,576 — the number that ends the spreadsheet
The limit is 2 to the power of 20, because Excel addresses rows with a 20-bit index. Columns cap at 16,384 for the same reason. This has been fixed since the .xlsx format arrived in 2007, and no setting changes it. It is not a bug or a licensing tier — it is the shape of the file format.
You hit it faster than you would think, because master data compounds. A product catalog with every variant, size, and pack configuration. A customer list across every region and legacy system you have ever acquired. Part numbers, location codes, historical price lists that nobody is allowed to delete. Any of these can cross a million rows, and the SKU-variant and historical tables get there first.
The truncation is quiet by design
Here is the part that makes this dangerous rather than merely inconvenient. When you open an oversized CSV, Excel does show a dialog saying the file is too large and that it will load only the first 1,048,576 rows. Then it opens the file, and it looks completely normal. There is no marker at the bottom, no gap, no color. The data that was dropped is not represented at all, so nothing on the sheet suggests it was ever there.
And that dialog only appears when a person opens the file by hand. Pull the same CSV in through a scheduled Power Query refresh, a macro, or an automated import, and there is no dialog to dismiss because there is no one there to dismiss it. You find out weeks later, when a lookup returns an error for a customer everyone knows exists, or a report's totals quietly stop matching the source system.
You hit the wall long before the number
The million-row ceiling is the theoretical limit. The practical one is far lower. A master data workbook is not a bare list — it has formulas, VLOOKUPs reaching across other sheets, data-validation dropdowns, and conditional formatting on every row. Stack all of that on a few hundred thousand rows and Excel starts to crawl: recalculation lags, the file balloons past a hundred megabytes, saves take minutes, and the whole thing loads into memory every time you open it.
The usual advice is to move the data into Power Query or the Excel Data Model, which can process millions of records. But that data lives outside the grid, as a query result you look at rather than a sheet you edit. You cannot hand a data steward a Power Query model and ask them to correct a supplier's address in it. The workarounds for scale trade away the one thing master data needs most, which is being editable by the people who own it.
Rows are the wrong way to measure master data
A row count assumes master data is a flat table, and it isn't. Each record is rows times attributes times relationships times change history. A 200,000-row customer list with forty attributes each, domain lookups to countries and currencies, and a full audit log is not a large spreadsheet — it is a database that happens to be wearing a spreadsheet costume. Judging it by row count is like judging a building by its floor plan and ignoring the floors. If you're thinking about the reference lists underneath all this, we cover how they should be structured in our guide to reference data management.
What replaces the row limit
Put the data where row count stops being a ceiling — a database — and give stewards a grid on top of it. In Primentra the records live in SQL Server, where a table holding millions or billions of rows is unremarkable. The browser grid virtualizes and paginates, so it never tries to load the whole set into memory the way a worksheet does.
In practice, a steward filters to the thirty rows they actually need, edits them in place, and the other 1.9 million sit untouched in the database. Nothing gets discarded, because nothing had to be squeezed into a file in the first place. The same grid that replaced the old MDS Excel add-in is the one doing this — it just isn't bounded by what a worksheet can hold.
The row limit is one of several places a spreadsheet quietly stops being the right tool for this job. We wrote a broader piece on why Excel fails as a master data tool if you want the full picture before you commit to a migration.
Frequently asked questions
How many rows can Excel handle?
An Excel worksheet has a hard limit of 1,048,576 rows and 16,384 columns, unchanged since the .xlsx format arrived in 2007. The number is 2 to the power of 20, because Excel uses a 20-bit row index. In practice the usable limit for master data is far lower — a workbook full of formulas and lookups becomes slow and fragile at tens of thousands of rows.
Does Excel warn you when it truncates data?
Barely. Opening a CSV over 1,048,576 rows shows a dialog and loads only the first 1,048,576 rows, discarding the rest. The dialog is easy to dismiss without reading, and a scripted import or scheduled Power Query refresh skips it entirely. The truncated data looks complete because the dropped rows simply are not there.
Why does Excel drop rows past 1,048,576?
Excel uses a 20-bit internal row index, and 2 to the power of 20 is 1,048,576, so no row can be numbered higher. Excel also loads the whole worksheet grid into memory at once, so the limit is both a data-structure ceiling and a memory constraint. Anything beyond the last addressable row cannot be stored and is dropped.
How do you manage master data too large for Excel?
Move the data into a database and put an editing grid on top. In a tool like Primentra the records live in SQL Server, where row counts run into the millions or billions, and the browser grid virtualizes and paginates so a steward edits a filtered slice rather than loading the whole set. You filter to the rows you care about and the rest are never pulled into a fragile file.
Stop losing rows you can't see
Primentra keeps your master data in SQL Server, not in a file with a million-row ceiling. Stewards edit a filtered slice in the browser while the full set stays in the database. Load your largest reference table into the 60-day trial and watch nothing get truncated.