I’ve been thinking about converting my older XLS files to the newer XLSX format, but ran into a quirk that stopped me. Maybe I’m just too impatient. Here’s my story.
A Few Facts First
Excel versions 97-2003 (Windows) have a file extension of XLS and the worksheet size is 65,536 rows and 256 columns. In Excel 2007 and 2010 the default file extension is XLSX and the worksheet size is 1,048,576 rows and 16,384 columns.
An XLSX File with Only 65,536 Rows
I open Excel 2003, create the following spreadsheet, and save the file. The formulas are listed in my last post.
Next I open this file in Excel 2010, which now is in Compatibility Mode because it’s an Excel 97-2003 Workbook (*.xls) file.
It still has only 65,536 rows and 256 columns, which is only natural because it’s still in the older XLS file format.
I save this file to the newer XLSX format.
But this worksheet still has only 65,536 rows. I create a new worksheet in this XLSX file and it has only 65,536 rows.
Now I’m discouraged and stop trying to convert my old Excel files to the new file format. In fact, I tried several times over the past few years to do this file conversion, both in Excel 2007 and 2010, but had the same result each time.
The Quirk
What I didn’t realize because of my impatience and inattention is that right after you save an XLS file to the newer XLSX format, Excel is still in Compatibility Mode, which is clearly labeled at the top of the Excel window.
Hence the smaller worksheet size.
After you save an XLS file to the newer XLSX format, just close and re-open the file to get rid of Compatibility Mode. Then all the worksheets have 1,048,576 rows and 16,384 columns.
What is bizarre is the fact that I had to open an XLS file with Excel 2011 (Mac), to help me find quirk.
I wonder if you cleared a check mark in the conversion process sometime… whenever I convert, I get the notice that I must close and reopen the file to use the new features, and then Excel ’07 asks me if I want to do that. I say yes, and it closes the .xls and opens the .xlsx. Same thing in the other Office ’07 products.
I’ve checked all the Settings I can think of and can’t find anything thats relate to Compatibility. I also checked Excel 2007 for this same behavior as all my tests were with Excel 2010, but had the same issue: saving an XLS file to XLSX format doesn’t really give me a larger spreadsheet until I close and re-open the file, and I receive no warnings to tell me that fact.
Another strange thing to ponder while I’m trying to fall asleep at night.
A comment which may not be entirely related to this topic, but just fyi —
When you save an XLS file as an XLSX file, the file-size is also reduced considerably.
In many cases, I have seen a reduction of almost 50%.
This is because the XLSX and XLSM file formats are essentially compressed file formats.
To check this out, make a copy of an existing XLSX file and change its extension to ZIP.
Then open the ZIP file in Winzip, or your preferred ZIP file utility.
You will see the file’s various ‘components’ in separate folders within the ZIP file.
Also, if you wish to *further* reduce the file-size, you can save it as an Excel Binary Workbook (XLSB). I’ve seen size reduction upto 80% when compared with an XLS file.
Note, that in this case, XLSB files are not viewable in a ZIP utility, even after changing the file extension.