Question about the excel spreadsheet row limit

Some reference as supplement, support.microsoft.com/en-us/excel/excel-specifications-and-limits

The 1,048,576 row limit is not just a display limit. It is the actual maximum number of rows in one Excel worksheet.

So if you write 2 million rows into a single worksheet, Excel cannot safely store all of them in that sheet. It may open only the first 1,048,576 rows, and if you save the workbook from Excel, the rows beyond that limit can be removed.

If you need to keep all 2 million rows, split the data across multiple sheets, keep it as CSV/database/parquet, or load it into Power Query/Data Model instead of putting all rows directly into one worksheet.

brentjames 

All worksheets in a .xlsx, .xlsm or .xlsb workbook have exactly 1,048,576 rows and 16,384 columns. No more, no less.

As a consequence, you cannot store more than 1,048,576 rows in a worksheet. If you have a dataset with 2 million rows and 20 columns, you have to split it into two datasets of 1 million rows each. You can either store those side by side in the same worksheet, for example in columns A to T and in columns V to AO, or in two separate worksheets.