Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

A client contacted me, complaining of Excel spreadsheets that were taking far too long to open. They use Excel for creating invoices, so they have hundreds of Excel spreadsheets with light formatting and very simple calculations. When sorting the spreadsheets by size, I saw that while most of the spreadsheets ranged from 10-250k, there were a handful of spreadsheets with filesizes of 2-3 MB and more. Oddly, the filesizes weren't huge, they aren't holding much data, just a bit of formatting, maybe two or three pages of printed invoices, but they amount of data was almost identical to the smaller sized (and normal opening) spreadsheets.

When opening the file, the amount of RAM needed would spike from 3 MB to 400 MB, and it would completely occupy a single core (tested on a dual-core in the office, and my quad-core laptop), while starting. I thought that they had somehow caught some VBA code, but there are no macros, no VBA code. Ctrl+End shows 39 rows and about 12 columns (ends at M). I've even deleted the data, row by row, or column by column, until there is no data left, and it still gives me the same problem.

I've reviewed plenty of Google searches, but I've gotten nowhere. Can anyone offer any help?

share|improve this question
    
Another interesting note: I am not able to save the xlsx file as an xls file in Excel 2007, but by importing it into Google Docs, and then saving it as an xls file, I get a normal sized file once again. –  Simon Hova Jul 12 '12 at 17:55
add comment

4 Answers

Are you using the binary .xls format or the new XML-based .xlsx format? In general the .xlsx format results in a dramatic reduction in filesize.

Check for things like an excessive number of styles stored in the document.

Try to "remove personal information" (a feature of Excel/Word/etc.) to clean up certain kinds of cruft that might be sitting in the file.

If the spreadsheet is, or has previously been shared, it may have old share data stored in it.

One simple fix is to copy and paste just the relevant data from that spreadsheet to a new one, then save it in .xlsx format, and see how small it is. If it's very small then you have your answer -- Excel is doing poor accounting of its internal file format's data structures.

Also check for complicated or circular formulas and references to external sheets (especially those on network drives). If you think a formula might be slow, you can step through it using the formula auditing tool.

Last thing: if you save it as an .xlsx and it's still big, try downloading the OpenXML SDK Productivity tool: http://www.microsoft.com/en-us/download/details.aspx?id=5124

Open up the .xlsx in that and just take a look at all the elements within the file and see if anything is obviously extraneous. This may require knowledge of XML and of the specific OpenXML schemas, but it's a surefire way to find out what is causing the bloat.

P.S. -- if this kind of thing annoys you, stop using Microsoft formats / programs or suggest to your customer to do so. If you google around for "Microsoft Access database bloat" you'll see that Microsoft has a long history of letting their proprietary formats leak tons of useless data to disk that never gets cleaned up. It's like a really nasty memory leak that eats your disk instead of your RAM.

share|improve this answer
    
The spreadsheet is indeed an xlsx, and one odd thing: I cannot save it as an .xls file. It keeps returning an error in Excel 2k7. I did save a copy as 2003 xml spreadsheet, and the odd thing that I found was it listed an enormous number of styles. This is odd because the entire spreadsheet uses the same font and no special formatting. Still no idea what to tell my client. –  Simon Hova Jul 12 '12 at 17:47
add comment

For a dramatic reduction in size you might try the format .xlsb. Should reduce more than any other XML-based format that excel provides.

Open one of those spread sheets and take a look at the format. Look for anything usual. Try to copy the entire column to another excel spreadsheet and paste values only (with no formatting whatsoever). See if it helps.

share|improve this answer
    
I tried saving in xlsb. No reduction in size at all. Screenshot. –  Simon Hova Jul 10 '12 at 21:35
    
@Simon Hova Did you try to set the calculation to manual and restart the excel? It could be anything: formulas, formatting. Did you copy the data column to another excel spreadsheet to see if the performance is the same? Just the data; no formula or formatting. The add them one at a time and see where is the problem. –  Alexander Ceed Jul 10 '12 at 22:43
    
That's another odd thing, there is no calculation, there are no formulas being run. It's just a few rows and columns with some minor formatting. I've tried deleting the data row by row, and I am left with a file that is about the same size as before. Still stumped. –  Simon Hova Jul 12 '12 at 17:49
add comment
up vote 3 down vote accepted

I've found the answer to my problem!

Using the clues given to me by allquixotic, I opened the xlsx files in 7-Zip, and compared the file sizes. There was one file that was much larger than the others. The file xl\drawings\drawing1.xml was a file that contained multiple references to formats, over and over again.

I spent a few hours trying to figure out a pattern, but wasn't able to. Nothing I was able to do could get it working! Then, after a fit of aggravation, I just deleted the damn file, and tried to reopen in Excel (2010- didn't test it in Excel 2007).

It complained that the file was damaged, and asked if I'd like to attempt a repair. Repairing the file simply erased the shape, but didn't change the formatting of the file at all. I had to resave the file as the same file, which was a bit odd, but it worked!

As I'd mentioned before, I hadn't tested this solution for any products other than Excel 2010, so I don't know if the shape file was critical to Excel 2k7 or any OpenOffice products. But, if you have a similar problem, hopefully this could be helpful.

share|improve this answer
add comment

Another possible solution would be:

  1. Create a copy of the problematic Excel file
  2. Open that copy
  3. Press CTRL+A and then click on "Clear" -> "Clear Formats"
  4. Repeat step 3 on every worksheet
  5. Save the file and try to reopen it

When there is too much formatting, especially if there are many different styles applied to many individual cells, Excel really struggles trying to apply formatting when opening files.

Naturally, when you remove all formatting you'll end up with just text with default style applied to it in all of the cells with borders, shading, etc gone. But this way you can pinpoint the cause of the problem.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.