I have a very large workbook with many worksheets and am experiencing bloat
and slowness. I know, I know, I am inviting joke responses about using
Gas-X, but please restrain yourselves!

In order to provide a visual key as to which fields are empty, I am using
conditional formatting to turn empty fields red. I also use Data Validation
to control what goes into the cells.

I never know how many rows will be filled in, so I just select the entire
column and put in conditional formatting and the data validation, and then
delete the conditional formatting and data validation for the header row.

Am I causing bloat? Is the conditional format and data validation being
recorded for every one of the 65000+ cells in a column, or are they just
stored in one place and the program applies it appropriately as needed.

In addition, am I causing slowness in recaluclation or movement through the
sheet?

Or is the bloat and slowness just because of the amount of data involved?

I have relatively few formulas, but I do have lots of dropdowns that draw
their options from lists on a hidden sheet.

My tests have been inconclusive. I also have been working on this workbook
for several years. Is there some sort of "compaction" I should apply to it?

Just a couple of quick experiments (xl2002).

I created a new single sheet workbook and saved it. (Size 14k).

I put some test data in A1:A10.
I selected column B and applied data|validation using A1:A10 as my list.
I saved it.
Size 14k.

I selected column B and applied format|conditional formatting.
=MOD(ROW(),3)=1
(to just shade every 3rd cell.)
I saved it.
Size 15k.

I hit ctrl-end.
I was taken to A10. Excel saw A10 as my last used cell.

If you hit ctrl-end, does excel match your expectation?

If not, maybe that's where the bloat is coming from.

Debra Dalgleish has some techniques for resetting the last used cell at:
http://www.contextures.com/xlfaqApp.html#Unused

(Remember to save before you experiment.)

===
Then I typed something in IV65536 and saved it. Size 40k.



Bill Sturdevant wrote:

I have a very large workbook with many worksheets and am experiencing bloat
and slowness. I know, I know, I am inviting joke responses about using
Gas-X, but please restrain yourselves!

In order to provide a visual key as to which fields are empty, I am using
conditional formatting to turn empty fields red. I also use Data Validation
to control what goes into the cells.

I never know how many rows will be filled in, so I just select the entire
column and put in conditional formatting and the data validation, and then
delete the conditional formatting and data validation for the header row.

Am I causing bloat? Is the conditional format and data validation being
recorded for every one of the 65000+ cells in a column, or are they just
stored in one place and the program applies it appropriately as needed.

In addition, am I causing slowness in recaluclation or movement through the
sheet?

Or is the bloat and slowness just because of the amount of data involved?

I have relatively few formulas, but I do have lots of dropdowns that draw
their options from lists on a hidden sheet.

My tests have been inconclusive. I also have been working on this workbook
for several years. Is there some sort of "compaction" I should apply to it?

--

Dave Peterson