Workbook is too large, struggling to reduce size.

[wit1111]

Hi,

A colleague recently returned a workbook to me where she had added additional sheets, the file is now enormous 25MB.

There is no code nor extensive logic, it is primarily SUMIF functions.

I have worked through the sheets and cleared the contents of all unused cells, I have checked the sheets for any VBA's, I have checked the workbook for conditional formatting.

Any idea as to how I could reduce the size, all of the above have not worked.

Many thanks

 

[Nine Zero]

This is a longshot because i dont think this could be responsible for 25MB but try making sure that your "white" cells are not actually white, but they are set to "No Fill" that will help a bit

check for hidden sheets

check for formulas referencing sheets outside the workbook maybe

 

[SpillerBD]

Formulas will take up more memory per cell than just a value, but it would be a lot of formulas.
Do you have the size of the file with just data, no formulas?
On large data sets, Pivot Tables can be more efficient and often provide the desired answers for the data set.
Conditional formatting and even regular formatting can add an amount to the file size.

I have hit bigger file sizes. However, I now try to separate data and analysis by using PowerQuery to link and bring data into the Data model which uses excellent compression technology. I have 150MB of data (multiple text files) that are loaded into a PowerPivot Workbook that is about 4MB of results. I can send the smaller workbook and while connections are broken and can't be updated, the data serving the Pivot Tables is still intact and the Pivot Tables can mostly be changed. Some things are still better via a network share.

 

[steve the fish]

This is what i would try first. Go to each worksheet in turn and find the last cell with data. Highlight a row after the data then press CTRL-SHIFT-DOWN. Right click and delete. Press save.

 

[Dryver14]

You could try saves .xlsb format

 

[wit1111]

Thanks, I have tried this already

 

[wit1111]

This is what i would try first. Go to each worksheet in turn and find the last cell with data. Highlight a row after the data then press CTRL-SHIFT-DOWN. Right click and delete. Press save.

If I select and delete the empty columns, excel just replaces them, I cannot reduce the columns to anything less than XFD?!?

 

[Joe4]

If I select and delete the empty columns, excel just replaces them, I cannot reduce the columns to anything less than XFD?!?

After you do this, SAVE your workbook. Then see where Excel thinks where the end is.

 

[wit1111]

After you do this, SAVE your workbook. Then see where Excel thinks where the end is.

Thanks, but still restores the deleted cells even after saving

 

[Joe4]

Thanks, but still restores the deleted cells even after saving

What do you mean by it "restores the deleted cells"?
Is there something physically in these cells?
If so, how exactly are they being populated?
I would think if it is being populated with something, there must be some sort of VBA or linked data source populating it.