Spreadsheet Size in Excel for Mac

by Gregory on April 28, 2011

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.

Compatibility Mode

I put together a few formulas to show the size of the worksheet, the Excel version, Operating System version, and System info.

XLS file in Excel 2010

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.

XLS Worksheet in Excel 2011

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.

How odd.

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.

Compatibility Warning Excel for Mac

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.

Excel for Mac Compatibility Icon

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.

Related Posts Plugin for WordPress, Blogger...
vikas June 12, 2011 at 10:36 pm

How can i open .xls (windows 97 excel file ) in mac os (office 11) ??

Gregory June 13, 2011 at 5:31 am

Open Excel 2011 Mac, which is part of Mac Office 2011, then click File > Open… (from the menu) then navigate to the Excel 97 file (Windows), select it and click Open.

Comments on this entry are closed.

Previous post:

Next post: