13

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?

1
  • 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, 2012 at 17:55

6 Answers 6

7

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.

2
  • I had encountered file with same problem. Instead of deleting drawing1.xml, i removed reference to drawing from sheet1.xml and did "save as". Possible cause may be copying data or formatted strings from mail, internet or other spreedsheets.
    – Andrei
    Apr 15, 2019 at 14:34
  • 1
    Encountered a similar situation. In my case, there are several drawingN.xml, with size ranging from "acceptable" 200 KB to 80 MB. That file brings even quad-core desktop with 16GB RAM to its knees. Removing all drawingN.xml (since the Excel file doesn't even contain any chart/graphic) solve the problem, works on the latest Office 365 at the time of this writing.
    – Martheen
    Sep 10, 2019 at 11:49
6

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.

1
  • 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, 2012 at 17:47
2

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.

0

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.

3
  • I tried saving in xlsb. No reduction in size at all. Screenshot.
    – Simon Hova
    Jul 10, 2012 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. Jul 10, 2012 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, 2012 at 17:49
0

In my case the problem was related to "Format as table". The table had 16000 empty columns. I guess this is one of the most frequent reasons for this kind of problem.

0

I had a look at all the conditional formatting rules, in the whole worksheet, and there were loads. I went through and cleared them all, and then created the ones I actually needed, and now it is really fast. Also got rid of all the 'Connections' I didn't need, and save as .xlsb after it all worked well, and it is even faster now :o)

1
  • This duplicates information in other answers, and so feels like a "thanks" comment.
    – bertieb
    Apr 18, 2017 at 10:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .