Why is there still 1,048,576 row limit in Microsoft Excel?
Microsoft Excel one of the most extensively used software in the IT industry. Microsoft Excel (Spreadsheets) are not only useful for business management but also in Data Science. The theoretical limit of rows & columns is well defined in excel. Today we have interesting concepts for you regarding row limits in Microsoft Excel.
1,048,576 rows by 16,384 columns is the file limit by Excel 2019 / Office 365 Workbook. Open workbooks are limited by available memory and system resources. In Office 2003 or earlier, Microsoft Excel has a maximum of 65,536 rows & 256 columns.
Microsoft excel support 3 worksheets by default. Each worksheet can support stated rows & columns limit. However, one can have more worksheets if CPU has enough memory for additional data storage.
A worksheet can have maximum of 17,179,869,184 cells. Each cell can have maximum of 32,767 character values. There is no limit on number of worksheets in a workbook. It totally depends on available memory of the system.
How the limits are calculated?
Many users do believe that 64-Bit Excel can support more rows & columns than 32-bit. But, remotely that’s not true. The limit is specified by version of product not by number of bits supported.
Row Limit = 2^20 = 1,048,576
Column Limit = 2^14 = 16,384
Columns are labelled using alphabets. From A to Z. Then from AA till XFD. Which completes 16,384 columns. Rows are labelled directly by numbers from 1 to 1,048,576.
What happens if we exceeds the limit?
If you exceed this column or row limit in Microsoft Excel, then you’re prompted with a popup. The popup says, “File not loaded completely”. With following description:
- The file contains more than 1,048,576 rows or 16,384 columns. To fix this problem, open the source file in a text editor such as Microsoft Office Word. Save the source file as several smaller files that conform to this row and column limit, and then open the smaller files in Microsoft Office Excel. If the source data cannot be opened in a text editor, try importing the data into Microsoft Office Access, and then exporting subsets of the data from Access to Excel.
- The area that you are trying to paste the tab-delineated data into is too small. To fix this problem, select an area in the worksheet large enough to accommodate every delimited item.
Why Row & Column limitations on MS Excel?
1. It increases Stability.
2. It improves software performance.
3. Imagine there’s no specific limit. While opening & processing the file, software may get crashed due to limited resources.
- Find out maximum number of rows, place the cursor in an empty column & press Cltr + Down Arrow.
- To find out maximum number of columns, place cursor in an empty row & press Cltr + Right Arrow.