How to Reduce Spreadsheet Size and Speed

[ExcelMonkey]

I have a spreadsheet which is 24MB! Yikes! Anyways, I have been given the
task of reducing its size and increasing its speed. Now I know one of the
first things I should do is go to each worksheets and hit CNTRL End to find
out what the Used Range is. From there I can delete unused cells and
unneeded formatting.

I can also export VBA code and attempt to clean it.

There are macros in worsheets which are always active running. I know these
may cause speed issues. For example when I select certain cells, text
appears in them. When I select them again, the text disappears. These are
used as anchors or identifiers when looping through rows with ceratin
optimisation macros.

There are not any User Defined Functions.

I can get rid of unneeded comments.

There is a ton of Data Validation drop boxes (not sure if this matters).

Minimal Charts in the model (6-7).

Then my last task will be to start looking at formulas. I can look for
Volatile formulas (NOW(), RAND()) and see if there are too many of them.

This model is very large and has some very sophisticated formulas for DATES
and Dynamic Ranges. A typical formula might be:

(=SUM($G157:OFFSET(P157,0,-ROUNDUP(INDEX(P$245$254,$C291)*$D291,0)))-SUM($F291:O291)+OFFSET(P157,0,1-ROUNDUP(INDEX(P$245$254,$C291)*$D291,0))*(INDEX(P$245$254,$C291)-ROUNDDOWN(INDEX(P$245$254,$C291),0))).

I can't remember if OFFSET is a volatile formula. I am also assuming that
most other types of formulas that take ranges (Index, lookups Match) may
cause speed issues as well.

There is an active scenario manager built into the model where the active
run is based on a drop down box which pulls data from one of 6 columns.
These columns represent inputs for each of the runs. I could always reduce
this to one scenario.

I can always export the sheets and save them as individual files to see how
much of the file memory size stems from each sheet. Note this will not
necessarily speak to the speed. And it will not speak to macro memory.

Can anyone provide me with some other tips which I can use to reduce the
size and increase the speed of this spreadsheet. Or tips that I can used to
identify what portion of the spreadsheet contributes to size and speed loss.

Thanks.

 

[Andy Wiggins]

Which version of Excel are you using?

 

[Charles Williams]

I suggest you look at my web pages on optimising calculation speed.
http://www.DecisionModels.com/optspeedd.htm etc


Charles Williams
Decision Models

 

[ExcelMonkey]

XL2000

Thanks

Which version of Excel are you using?

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

 

[Harlan Grove]

ExcelMonkey wrote...

I have a spreadsheet which is 24MB! Yikes! Anyways, I have been given the
task of reducing its size and increasing its speed. Now I know one of the
first things I should do is go to each worksheets and hit CNTRL End to find
out what the Used Range is. From there I can delete unused cells and
unneeded formatting.

You'd be better off creating new worksheets adjacent to existing
worksheets,
*cutting* nonblank cells in existing worksheets and pasting into the
new
worksheets, deleting the old worksheets and giving the new worksheets
the
old worksheets' names. You'll have some additional fix-up for centered
horizontal alignment across cells and reformatting unprotected entry
cells
that happened to be blank (better - write 2 macros, one to put the text
constant <entrycell> into all unprotected blank cells, and another to
delete
that text constant from unprotected cells), and you may need to set
column
widths and row heights (though it's always best to choose the standard
width
that works for the largest number of columns, and *NEVER* screw around
with
any row heights other than AutoFit).

I can also export VBA code and attempt to clean it.

VBA is seldom a source of significant bloat.

There are macros in worsheets which are always active running. I know these
may cause speed issues. For example when I select certain cells, text
appears in them. When I select them again, the text disappears. These are
used as anchors or identifiers when looping through rows with ceratin
optimisation macros.

There are not any User Defined Functions.

I can get rid of unneeded comments.

Comments also are seldom a source of significant bloat.

There is a ton of Data Validation drop boxes (not sure if this matters).

How many distinct lists do they draw from?

Minimal Charts in the model (6-7).

Then my last task will be to start looking at formulas. I can look for
Volatile formulas (NOW(), RAND()) and see if there are too many of them.

Don't get too stuck on eliminating NOW(), TODAY() and RAND(). They're
usually there for a reason. However, you could replace TODAY() calls
with
a defined name resolving to a date constant (the current date) that's
set
by Workbook_Open. NOW(), on the other hand, provides time of day as
well,
and when it's needed, it's NEEDED. Same for RAND().

This model is very large and has some very sophisticated formulas for DATES
and Dynamic Ranges. A typical formula might be:

Dynamic ranges are a killer. Don't go fundamentalist, though. A *few*
dozen dynamic range are a good thing. Thousands of dynamic range
references
are a bad thing.

=SUM($G157:OFFSET(P157,0,-ROUNDUP(INDEX(P$245$254,$C291)*$D291,0)))
-SUM($F291:O291)+OFFSET(P157,0,1-ROUNDUP(INDEX(P$245$254,$C291)*$D291,0))
*(INDEX(P$245$254,$C291)-ROUNDDOWN(INDEX(P$245$254,$C291),0))

I can't remember if OFFSET is a volatile formula. I am also assuming that
most other types of formulas that take ranges (Index, lookups Match) may
cause speed issues as well.

....

OFFSET is volatile. INDEX usually isn't volatile. In the formula above,
if
there were no text in G157157, the first SUM call could be replaced
with

SUMPRODUCT(--(COLUMN(P157)-COLUMN($G157157)

INDEX(P$245$254,$C291)*$D291),$G157157)

Longer, but nonvolatile. Further, the term

INDEX(P$245$254,$C291)-ROUNDDOWN(INDEX(P$245$254,$C291),0)

should be replaced with

MOD(INDEX(P$245$254,$C291),1)

Can anyone provide me with some other tips which I can use to reduce the
size and increase the speed of this spreadsheet. Or tips that I can used to
identify what portion of the spreadsheet contributes to size and speed loss.

Most formulas include repeated terms. Your sample formula uses the term

INDEX(P$245$254,$C291)

4 times. At the cost of one cell in which to hold the formula

=INDEX(P$245$254,$C291)

you could replace this repeated term with a reference to the cell
containing
the term as its entire formula. Rather than evaluating this term 4
times, Excel
would evaluate it once in the new formula cell and access that value 4
times
(well, 3 times when you use the MOD call) in your revised formula. If
the newly
used cell were X291, your formula could reduce to

=SUMPRODUCT(--(COLUMN(P157)-COLUMN($G157157)>$X291*$D291),$G157157)
-SUM($F291:O291)+INDEX($G157157,11-$X291*$D291)*MOD($X291,1)