Reducing the size of spreadsheets

[Chrissirhc]

Hi,

I have these following ideas for reducing the size of a spreadsheet. I was hoping some of you could provide more.

1) Open without macros enabled and then save.
2) Delete unused rows on each WorkSheet.
3) Find out where the size comes from by:
a) Exporting modules of code, deleting them and then saving.
b) Saving each worksheet in their own workbook.

What do you think of the above and do you have any other ideas. (1 and 2 only saved about 4megs off a 57 meg file)...

Thanks in advance,

Chris

 

[macropod]

Hi Chris,

Deleting unused columns can be just as important as deleting unused rows. Any time using Ctrl-End takes you to a cell beyond the data/formula range means the worksheet is taking up unnecessary space.

The are alos som significant design issues that can affect memory/storage requirements, quite apart from the amount of data/formulae and the complexity of the formulae. Keeping the layout of each worksheet to a simple rectangular arrangement starting at A1 will minimise memory/storage requirements. T-shaped and L-shaped worksheets tend to inefficient in this area.

Some formulae are also more efficient than others; there can be a trade-off here, though, where speed might be more important than memory/storage requirements, or vice-versa.

I don't see how saving with macros disabled will reduce memory requirements. Using efficient code, and minimising the size of code modules will also help reduce memory/storage requirements. You can use code to reduce vba project size, but removing a heavily-edited module, then re-inserting the updated code in a new module will do much the same.

Cheers

 

[Zathras]

You could do some experimentation by copying all of the cells in the worksheet and do a paste special values into a new worksheet. Save the new worksheet and see what size it is.

If it is relatively small, then your problem might be that you have too many, or too complicated, formulas. Depending on what it is exactly that you are doing, there may be ways to simplify the formulas that you are using. For example, you might be able to use a macro instead of several thousand formulas, trading space for processing speed and the inconvenience of having to click a button to run the macro. If you use a lot of VLOOKUP formulas with the same search cell, they could be replaced with MATCH and INDEX formulas (using a hidden column to hold the row number of the lookup result). Perhaps you could simplify by using database formulas.

But first things first. It is difficult to understand how your worksheet could grow to over 50 megabytes.

 

[macropod]

Hi Zathras,

It is difficult to understand how your worksheet could grow to over 50 megabytes

It's not hard when you know how ... I have one that's over 60Mb, with minimal data - mostly it's 1.4 million formulae and a bit of formatting.

Cheers

 

[Chrissirhc]

Doing 3B I realised that one sheet with 11000 rows takes 16 MB.

 

[Zathras]

I have a worksheet with 64,788 rows and 18 columns that is under 20 meg. When saved as a .csv file it is under 14 meg. Most of the data are names and addresses. I have another one that is under 4 meg which has over 43,000 rows but only 4 columns. It is all numbers. Neither one has any formulas nor formatting.

So, 11,000 rows at 16 meg may not be too bad depending on exactly what you are doing. (Of course you didn't say how many columns you are using so this is still all guesswork here.) But it may be possible to reduce it somewhat, depending on the formulas you are using. What size is it if you save it as a .csv file? That would represent the rock-bottom minimum based on how much actual data you have in the sheet -- the difference being what the formulas and formatting are adding.

Fancy formatting also adds to the size of the sheet.

 

[dhulbert]

Chris

One of the fatest wayss to reduce the siz eof a spreadsheet would be to remove all of the formulae.

If you are distribiting the sheet to people around the organisation it's easy enought to create a script to Save a copy of the workbook with only the essential formulae still in then save it (With a new name)
I've had 50-60Mb sheets reduced to 5Mb doing this, The longer the formulae the mor ememory it takes. A load ov vlookups from other work books on the network takes more space then simple additions on the same sheet.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[bpeirson]

I have a template which does calculations for 1 to 32000 items. The most I have done is a sheet with 500 items. When I first made the template all the formulae were in the sheet ready for use on 30 rows. Copy & paste as required.

I now have the same template but none of the formulae are inserted until the quantity required is known. VBA code inserts the formulae as required.

The drawback is the delay while the VBA code loads the formulae.

The advantages: smaller files and no external links until I am ready for the formula to be loaded.

Updating external links can be time consuming.

I avoid having VBA do calculations which Excel is capable of doing within the spreadsheet because VBA is slower by comparison.

For backup information I only save the items entered into the template. I can rebuild the template from the info later. My backup information resides on one spreadsheet about 328Kb. Each template with info installed is about 400-700Kb. The template without information inserted is about 340Kb.

I have made in excess of 3000 files over the last 5 years with this template. My original motivation for memory savings was to make backups feasible.

 

[Gavona]

If you have references to another workbook (I have lots of vlookups) then Untick: Tools, Options, Calculation, Save External Link Values.

If you have pivot tables then the default is to save data with table. Can be huge savings by unticking this option.

I also have managed to have named ranges in my workbook refering to external workbooks. For me these were no longer required and deleting the named range seemed to reduce file size significantly. I never fully understood that one.



Gavin