Forum Discussion

brentjames's avatar
brentjames
Copper Contributor
Apr 23, 2024

Question about the excel spreadsheet row limit

Hi,

 

I'm having trouble understanding what is meant by the excel worksheet/workbook row limit of 

1,048,576 rows. Does this mean that Excel cannot display a greater number of rows or that it cannot store data for more than than 1,048,576 rows? For example, if I combine various datasets and write this combined dataset to an excel spreadsheet file, and this dataset contains 2 million rows and 20 columns, will the excel spreadsheet contain all 2 million rows and the data contained in each of them? Not being able to view all 2 million rows in Excel is not an issue; I just want to be sure that no data is being lost. Thank you!
Excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 23, 2024

    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.

    • brentjames's avatar
      brentjames
      Copper Contributor
      Apr 23, 2024

      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?

Resources