How to Reduce Excel File Size Finally Revealed !

[earthworm]

Hi All,

I am sure most of the user have one complain .

1) The size of my excel file has become very large example 45-90 MB
2) Due to Above file size i am unable to perform further action on that file due to error (limited resource available)

The Answer to the above question has finally solved after perform several testing

A) If you have locked the sheet with option "Lock and Hidden Cell" of the entire sheet i.e ctrl+a +a (selecing entire sheet cell) , then the file size will increase significantly because after selecting the entire sheet i.e Data and other unused blank cell and selecting the option " Lock and Hidden " , the same is applied on each and every cell i.e 1 million + cells rows and columns , due to this the file size becomes huge.

in order to reduce the file , make sure that only those cells are lock and hidden on which data is present , remaining is not necessary .

In a nut shell , the option " Hidden " is the root cause for this .

Enjoy !!!

 

[FDibbins]

Another cause could be axcessive use of entire rows/columns in certain formulas (arrays for 1), and applying formatting - especially Conditional Formatting - to ranges beyond the minimum needed to serve your purposes

 

[Roar]

Thank you. I did this the other day and could not figure out what had happened. My sheet became 150MB with one page of data, hahhaa. I also did what FDibbins said and had formatted all rows. Oops

 

[FDibbins]

an easy enough mistake to make as long as you picked it up and corrected it

 

[Michael M]

Also look here...

http://www.mrexcel.com/board2/viewto...ht=reduce+size

 

[chuckchuckit]

You can try this on each sheet and then save the file to see if filesize is reduced:

Sub QueryTablesDeleteAllIn_Sheet1()
'Filesize grows (when you save it) due to Excel keeps track in background the path of each query table
'so each individual query will grow the filesize bigger.
'So delete them all after you process or copy the data.
    Dim i As Integer
    'With ActiveSheet
    With Worksheets("Sheet1")
        For i = .QueryTables.Count To 1 Step -1
            .QueryTables(i).Delete
        Next
    End With
End Sub

 

[earthworm]

Thank you. I did this the other day and could not figure out what had happened. My sheet became 150MB with one page of data, hahhaa. I also did what FDibbins said and had formatted all rows. Oops

select the sheet in which data is present press ctrl+a+a to select entire sheet , go to cell properties and click on lock tab , and uncheck hidden and lock option , and save the file . then see the result .

 

[Ms. Violet]

Boy, I wish this worked for me. My file has gone up from 1.4M to 2.3M just from adding a few new sheets + formulas. I developed a quick macro to unlock every cell (none were hidden) and it only save about 0.1M of space.

My workbook has 30 tabs, nothing extreme, lots of vlookups & index/matches & tables. And now it's hanging up when I do simple copy 1 cell into 30 cells.

 

[lox1985]

Boy, I wish this worked for me. My file has gone up from 1.4M to 2.3M just from adding a few new sheets + formulas. I developed a quick macro to unlock every cell (none were hidden) and it only save about 0.1M of space.

My workbook has 30 tabs, nothing extreme, lots of vlookups & index/matches & tables. And now it's hanging up when I do simple copy 1 cell into 30 cells.

If your vlookups and index/matches use Columns as Look up tables (A:B), it does more looking that it has to which slows down calcs (not reduces the size). If possible, use a specific range (A1:B10000), however, it is usually not an ideal solution for dynamic files.

 

[Ms. Violet]

The calculation speed is fine. It just gets completely bogged down when I do copy/paste. I'm trying to create a table to use as a reporting database and it has vlookups and some date functions to populate the fields. It only had 200 rows when it ground to a halt. I was copying/pasting the date down the column (for about 30 records) and it would take several minutes to do!!! Ridiculous.

I've since gone to a different solution, but I'm so disapointed. How do people have files over 100M and mine is bogged down at 2M?