Reducing files sizes... are macros the solution

**ste_mulv** · 12-16-2009, 10:36 AM

Hello all,

I have a spread sheet that I need to store, access and edit from a network drive.

I have 6 tabs as much of the imformation need to jump around between tabs. Each tab has data and formulars for 2000rows.

I have also added in statments such as:

=if(a1="","",'Sheet2'A1)

to make the sheet look emtpy until the relevant fields are entered.

The probelm is the file size is around 40mB, which takes far too long to load, making it unrealistic to actually use.

I also have some conditional formats to keep the document colours good.

Does anyone have a suggestion about how I can decrease the file size and thus make the document more likley to work day to day.

Cheers

Ste

**romperstomper** · 12-16-2009, 10:41 AM

Keep formulas as short as possible, don't prefill blank rows with formulas, and when using conditional formatting, it is usually better to apply it to an entire row or column than to do it to individual ranges.

**JBeaucaire** · 12-16-2009, 04:55 PM

Originally Posted by romperstomper

Keep formulas as short as possible, don't prefill blank rows with formulas,

Great advice....

...and when using conditional formatting, it is usually better to apply it to an entire row or column than to do it to individual ranges.

Have to disagree there. CF applied to entire workbook columns and rows is my #1 culprit on workbook bloat. I once did a CLEAR > ALL on all the cells outside of my data ranges on a workbook with 6 sheets...the file size dropped from 9MB to 55kb. Holy cow!

**teylyn** · 12-16-2009, 04:31 PM

On each sheet, hit the two keys END and HOME quickly after each other. If your cursor jumps to a cell way outside of your data area, delete unneeded rows and column. Select one column to the right of your data area and hit Ctlr-Shift-right arrow, then right click and delete. Same approach for rows.

Repeat for all tabs, then save the file, close it and reopen.