Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bill Sturdevant
 
Posts: n/a
Default Effect of Conditional Formatting, Data Validation

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?


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM
Determine cells that drive conditional formatting? Nicolle K. Excel Discussion (Misc queries) 2 January 7th 05 02:08 AM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 04:33 PM
time formatting and time categorizing (vlookup or data validation) MarianneR Excel Worksheet Functions 4 November 18th 04 04:24 PM
time formatting and time categorizing (vlookup or data validation) MarianneR Excel Worksheet Functions 0 November 18th 04 04:13 PM


All times are GMT +1. The time now is 06:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"