[Did You Know] Microsoft Excel has a Limit of Maximum 1,048,576 Rows and 16,384 Columns

Today while trying to open a worksheet/spreadsheet in Microsoft Excel program, I encountered a very annoying and weird problem. Excel denied to open the worksheet and displayed following error message:

File not loaded completely.

This message can appear due to one of the following:

Advertisement

  • 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 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 Excel. If the source data cannot be opened in a text editor, try importing the data into Microsoft 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.

Notes

  • Excel cannot exceed the limit of 1,048,576 rows and 16,384 columns.
  • By default, Excel places three worksheets in a workbook file. Each worksheet can contain 1,048,576 rows and 16,384 columns of data, and workbooks can contain more than three worksheets if your computer has enough memory to support the additional data.

Following is a screenshot of the error message shown by Microsoft Excel:

Microsoft_Excel_Rows_Columns_Limitations.png

I was shocked to see that MS Excel comes with a limit and can’t contain more than a specified number of rows and columns.

The error message contained OK button, when I clicked on it, Excel opened the worksheet but only displayed the first 1,048,576 rows. Remaining rows were not showing in the worksheet.

I decided to investigate the issue and I was able to find out some interesting piece of information. Today in this article, I’m going to share this information so that it may help other Excel users as well.

POINT 1:

Advertisement

A worksheet in Microsoft Excel can contain maximum 1,048,576 rows and 16,384 columns. This limit is applicable on Office 365 and newer versions of Microsoft Office such as Excel 2019, 2016, 2013, 2010 and 2007. Older versions such as Excel 2003, Excel XP (2002), Excel 2000, Excel 97 contained a limit of maximum 65,536 rows and 256 columns. Classic Excel 95 and Excel 5 versions came with a limit of maximum 16,384 rows and 256 columns.

POINT 2:

Due to this limitation, the maximum columns by letter are XFD in Excel 2019, 2016, 2013, 2010 and 2007, IV in Excel 2003, XP (2002), 2000, 97, 95 and Excel 5.

POINT 3:

A worksheet can have maximum 17,179,869,184 cells and a cell in a worksheet can contain maximum 32,767 characters.

POINT 4:

There is no limit on number of worksheets in a workbook. It depends upon the available memory.

POINT 5:

You might be wondering how the limit of maximum rows and columns was calculated in Excel. It’s calculated as following:

Maximum rows = 2^20 = 1,048,576

Maximum columns = 2^14 = 16,384

The columns are labeled using alphabets. First 26 columns are labeled A to Z. After that another alphabet is added so the next column is labeled as AA and this process continues till we get the last column XFD i.e. 16,384 column.

The rows are labeled using numbers from 1 to 1,048,576.

POINT 6:

You may ask why was this limitation applied to Microsoft Excel? I think it was done to improve performance and stability. If there was no limitation applied on maximum rows and columns capacity, Microsoft Excel could have crashed while opening huge worksheets and workbooks.

POINT 7:

If for fun you decide to go to the last row or last column in Microsoft Excel worksheet, you can do it using Ctrl+Down Arrow and Ctrl+Right Arrow hotkeys respectively.

POINT 8:

Some other interesting Excel limitations to know about:

  • You can resize a column width to maximum 255 characters only.
  • You can resize a row height to maximum 409 points only.
  • A worksheet can have maximum 1,026 page breaks.
  • A worksheet can have maximum 66,530 hyperlinks.
  • You can have maximum 100 undo levels.

If you know about any other limitation or interesting information in Microsoft Excel program, feel free to share it in comments.

Also Check:

[Excel Fix] Worksheet Scrolls, Can’t Move Between Cells Using Arrow Keys

Published in: Microsoft Office

About the author: Vishal Gupta (also known as VG) has been awarded with Microsoft MVP (Most Valuable Professional) award. He holds Masters degree in Computer Applications (MCA). He has written several tech articles for popular newspapers and magazines and has also appeared in tech shows on various TV channels.

Comments

NOTE: Older comments have been removed to reduce database overhead.

  1. I wonder whether these limitations apply equally to both 32- and 64-bit versions of Excel. Did you get the chance to try both?

  2. The first thing I do when I get a new version of MSO Excel is to read What’s new and check its new limits under Help / What’s new / Excel specifications and limits. I was shocked to read this kind of article in 2020. I thought it was basic knowledge by now.

Leave a Comment

Your email address will not be published. Required fields are marked *

NOTE: Your comment may not appear immediately. It'll become visible once we approve it.