Forum Discussion
Question about the excel spreadsheet row limit
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.
I've just run an experiment using an R data frame with 1.5 million rows of data (1 column with random numbers from 1-9). I wrote the dataset to an .xlsx file, then I read the .xlsx file into SAS. The SAS dataset has all 1.5 million rows. Do you know why this is?
I cannot explain that.
When I open it, I get this message:
If I press yes, then I get this message:
In excel, it only shows the first 1,048,576 rows, as expected. When I close out of excel, it asks if I want to save. If I say yes, then the rows after 1,048,576 rows are removed (checking in R). If I don't save, they remain.
