Excel file size mysteriously large

[Mzzh]

Hi XL Gurus

I’m having a size issue with an excel file lately. The file is about ~120MB with lots of data so I’ve been trying to find ways on reducing its size. Most recently I’ve decided to do a test by extracting a random worksheet (let’s call it “ws” into a new file. I then proceeded to clear all cell contents and formats, delete all rows and columns etc. The blank file size however remains around ~200kb. There are no external links, connections/queries, vba codes, defined name, hidden/very hidden worksheets or anything like that. I even added a new worksheet and deleted ws then re-saved the file but still it remained at around 200ish kb in size. Does anyone have an idea what’s going on??

 

[denzo36]

You are probably doing vlookups on a large number of row (or multiple vlookups) or using other volatile functions like COUNTIFs, INDIRECT, RANDBETWEEN etc. These recalculate everytime you try and do something.

Lots of conditional formatting can slow your sheet too, stuff like highlighting duplicates, especially over a large number of rows

Maybe copy and paste any formulas as values if you can and that should speed it up and reduce file size

 

[Mzzh]

You are probably doing vlookups on a large number of row (or multiple vlookups) or using other volatile functions like COUNTIFs, INDIRECT, RANDBETWEEN etc. These recalculate everytime you try and do something.

Lots of conditional formatting can slow your sheet too, stuff like highlighting duplicates, especially over a large number of rows

Maybe copy and paste any formulas as values if you can and that should speed it up and reduce file size

Hi Denzo

Yes the original file has a lot of indirect formulas and over 250 worksheets but that shouldn’t affect the single worksheet that is moved out into a new workbook? The sheet that was being moved out was even subsequently deleted from that new empty workbook and re-saved but alas the workbook file size remained unusually large. It seemed like that exported worksheet somehow “infected” an entirely workbook that has absolutely nothing. No queries/connections/conditional formatting/named ranges whatsoever. How is it possible??

 

[Skyybot]

Are there any Hidden Workbooks that open with XLSTART?

 

[NdNoviceHlp]

I'll throw in a few possibilities: a Corrupt file; Hidden used range on sheets. XL has a "memory" of all those cells that were once used and now forgotten which can make for strange memory things; Named ranges. Transfering sheets with named ranges seem to create trouble. You may be able to fix the problem by using a collection to transfer the sheet to a new wb. Finally, you could trial adding the sheet to a new file and then Zip and UnZip the file. HTH. Dave