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