Most people think an Excel workbook with an XLS file extension has only 65,636 rows and 256 columns in the worksheet. They would be wrong. Of course, if you don’t use a Mac there’s no way for you to discover this fact.
When you open an Excel 97-2003 Workbook (*.xls) file in Excel 2007 or 2010 (Windows) it’s done in Compatibility Mode, which you can see at the top of the Excel window.
I put together a few formulas to show the size of the worksheet, the Excel version, Operating System version, and System info.
This information is from an XLS file opened in Excel 2010 (Windows) in Compatibility Mode. The formulas I used are:
- Rows =ROWS(A:A)
- Columns =COLUMNS(1:1)
- Last Cell Address =ADDRESS(Rows,Columns,4)
- Excel Release =INFO(“RELEASE”)
- OS Version =INFO(“OSVERSION”)
- System =INFO(“SYSTEM”)
XLS Files in Excel for Mac
Excel for Mac versions 2008 and 2011 do not open XLS files in this “Compatibility Mode” and the row and column limitations do not exist. What this means is that when you open an XLS file in Excel 2011 or 2008 (Mac) you get the newer, larger spreadsheet size.
The information above is from the same file I opened in Excel 2010 (Windows). As you can see there are 1,048,576 rows and 16,384 columns in this XLS file.
Excel for Mac Compatibility
I made a change to the worksheet by typing “test” into cell E4 and saved the file with no problem. Then I typed “test” into cell A65537, which is one row larger than the XLS format permits. When I tried to save the file, the following message appeared.
Which means I should save the file in the new XLSX format to keep my changes.
Excel for Mac has preferences for Compatibility. Choose Excel > Preferences > Compatibility.
However nothing will change the fact that if you put data outside the 65,536 row and 256 column limitation for XLS files, Excel will warn you to save the file in the new XLSX format.
It’s just another variation between Excel for Windows and Excel for Mac.