formating multiple cells

[John Gray]

I have an spreadsheet froma data collector that has roughly 42,000 rows and
15 columns of data. Unfortunetly the data collector formats the cells to be
"text" instead of "number". When i go to make this change by selecting all
the cells and changing there format this process can take up to 30-45
minutes. Now this is on a p4 with 2gb of ram. However i do this same
change on my laptop (much slower) and it happens in a matter of seconds.
What gives....why would my work PC be slll slow. I am running Office 2003
at work and Office XP on my laptop. Hopefully someone will be able to shed
some light on this....thanks all....

John

 

[Gary''s Student]

No matter how much real RAM a computer has, the worksspace used by Excel is
limited. It may be that the workspace used by Excel XP is larger than the
space used by 2003.

Less memory thrashing would mean better performance.

 

[John Gray]

I'm sorry i don't understand.....

No matter how much real RAM a computer has, the worksspace used by Excel
is
limited. It may be that the workspace used by Excel XP is larger than the
space used by 2003.

Less memory thrashing would mean better performance.

 

[Gary''s Student]

Check out:

http://www.decisionmodels.com/memlimitsc.htm

 

[Lori]

The Convert Text to Number command takes a long time as it is carried
out cell-by-cell rather than on ranges. Other methods to convert text
based on ranges are:

a) Copy a blank cell, select the data (Ctrl+*) and then pastespecial -
with Values & Add

b) Copy whole worksheet to clipboard (by selecting all cells and
pressing Ctrl+C twice), then format cells as general and paste back
from clipboard.

The first method works quicker but the second has the advantage that
date and number formatting are retained.

Hope this helps, Lori

 

[John Gray]

Thank you Lori that makes much more sence...i appreciate it.

 

[Lori]

Thanks for the comeback, I generally avoid the command on large data
volumes. It doesn't explain the speed difference between machines
though. I think you might need to investigate further to see whether
there is a significant difference between other commands.