Spreadsheet Size in Excel for Windows

2011-04-29

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.

Excel 2003 Worksheet Size

Next I open this file in Excel 2010, which now is in Compatibility Mode because it's an Excel 97-2003 Workbook (*.xls) file.

Compatibility Mode

It still has only 65,536 rows and 256 columns, which is only natural because it's still in the older XLS file format.

XLS file in Excel 2010

I save this file to the newer XLSX format.

Save XLS file as XLSX file

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.

XLSX file in Compatibilitiy Mode

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.

XLSX file not in Compatibility Mode

What is bizarre is the fact that I had to open an XLS file with Excel 2011 (Mac), to help me find quirk.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

Eric29 kwietnia 2011 17:39
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.
Gregoryexcelsemipro.com30 kwietnia 2011 16:07
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.
Khushnood Viccaji2 maja 2011 11:54
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.
Gregoryexcelsemipro.com2 maja 2011 16:38
@Khushnood I'm aware of the new file structure and how much more efficient it is in keeping file sizes smaller, but was unaware that you could see the file components in a ZIP utility. Interesting. I'll have to check it out. Thanks for the tip.
Khushnood Viccaji3 maja 2011 09:30
My pleasure Greg :) I think I read about viewing XLSX files in a ZIP utility on Ron de Bruin's excellent site. He has loads of great Excel stuff on files / VBA / Ribbon, and more, at https://www.rondebruin.nl/tips.htm Bonus : This web-page also gives an explanation (by Ron), on how to actually use this 'feature'. https://help.lockergnome.com/office/unzip-zip-xlsx-files--ftopict945053.html
Randy2 listopada 2011 14:56
Check your default Excel settings. Your "Save File in this format" is probably set to xls instead of xlsx. This will get rid of your Compatability mode issue. It worked for my co-worker.
Gregoryexcelsemipro.com4 listopada 2011 03:07
I was intentionally using the XLS format for 2003 because other people using the file didn't have a newer version.
Eddie Punch19 marca 2013 16:09
How can I limit the size of an Excel spreadsheet to say, 20 rows and 20 columns ? Smart answer "Mark the Rows and Columns you don't want and hide them" ha-ha. Is there a quick way of marking rows 20 to 1,048,576 and columns 20 to 16,384 and hiding or deleting them ? Is there a minimum size a spreadsheet has to be in order to hold and prosess data ? Thank You Eddie Punch
Gregoryexcelsemipro.com24 marca 2013 14:45
Your formula is correct, but the cell formatting is not. Excel looks at time in 24 hour increments unless you specifically modify or change the time formatting. To do this open the Format Cells dialog box (Cmd+1 on a Mac and Crtl+1 on a PC), select the Numbers tab, and under Category select Custom. Next you will need to put square brackets around the h, and then select OK. Your custom cell format should look something like this [h]:mm:ss;@ or this [h]:mm:ss and will tell Excel to show cumulative time.
Gregoryexcelsemipro.com24 marca 2013 14:57
To quickly select and hide columns 21 to 16,384 you would first click the U column heading to select it. Then use the keyboard combination Cmd+Shift+RightArrow (or Ctrl+Shift+RightArrow on a PC) to select all the columns to the right. Then you simply right-click and select Hide. Similarly you select row 21, the use the keyboard combination Ctrl+Shift+DownArrow on a PC or Cmd+Shift+DownArrow on a Mac to select all rows below, and then right-click the selection and click hide. As far as minimum size goes, Excel has the same size of spreadsheet regardless of what columns or rows you hide. What you show to the user has to be designed for their needs. You could have a one cell spreadsheet if all they had to do was type in their first name.
Eddie Punch24 marca 2013 15:26
Gregory: Thank you very much. So easy when you find out how!