is it possibe to "defrag" and Excel data file?

[Ray Pendergast]

My company is on Excel 97. I have access to 2000, but the majority of users
have 97 and that's the platform I need to work with. I've noticed two
limitations, and they may be related, so I'm asking about them both here.
The immediate situation is a file that has apparently maxed out in graphs
and chart elements in 97. It has 32 different charts, a little under half
track a single series, but the rest track 5 or 6, each using a second axis.
I was in the process of adding the second axis and accompanying axis titles
to the last of these, when I hit a memory wall. I consistently get an
insufficient memory error and Excel shuts down everytime I try to add one
more element to any chart.
I've put this on different machines with more RAM, but the only thing that
makes a difference is using 2000, which, as I've said, isn't a good solution
for us. I can delete things. I got rid of the recently added series,
removing the need for the 2nd axis on 19 charts, but I still can't add any
more chart elements, even after saving the file.
This brings me to my related question. I process a significant amount of
data each week in a series of files. I maintain one file as the template,
just for the formulas and formatting. I add and delete thousands of records
each time I update the data. Over time, the file size grew to over 10MB. I
started a brand new file, and turned it into an actual .xlt, and now the file
sizes each week are one-tenth that size. Someone had explained to me that
Excel doesn't actually get rid of deleted data, that it holds onto the it
somewhere within the file, and that it can actually be recovered with the
right kind of software tool. He drew a parallel to the how an operating
system fragments files, and what the Windows defrag program fixes, while
explaining this.
I'd like to know if there is someone who can confirm this, or better yet,
explain it correctly; and could it somehow be related to my graph-o-matic
file? If there's a way to scrub out all of the deleted junk and free up
"memory" in that file, I'd rather do that, than try to rebuild all of that
data and those charts. In the scenario where I built the .xlt, I couldn't
copy too much from the old version without bringing in something that jacked
up the file size.
Beyond that, does anyone have any other ideas of how I can fix the
insufficient memory problem 97 has? I've considered moving the "bigger"
graphs into a separate, second file and linking them to the first. But
that's a last resort. This data is used by high level executives who didn't
get to their lofty positions by weighing their minds down with such useless
trivia as "computer saavy". If there are more than a few steps to getting
something off their email or a website, it's too complicated and needs to be
summarized better...
Thank you.

 

[Guest]

i am not sure but with large files you may have hit some
spec limit.
in xl help type: excel specifications.
this will tell the limits of excel.
I have 2k so i can't look up 97.

 

[Bernie Deitrick]

If there's a way to scrub out all of the deleted junk and free up

"memory" in that file, I'd rather do that, than try to rebuild all of that
data and those charts.

I gave up on Excel 97 a long time ago, so I can't comment on that.

But I can comment on the retention of data. Excel doesn't retain data that
is deleted, but it may retain the formatting of the cells from which data
was deleted, which can result in big files.

So you need to be careful about how you remove data. If you have 10,000
rows, and insert 10,000 new rows above them, and then clear the contents of
the old rows (that are now 10,001 to 20,000) Excel will keep the formatting
for those old 10,000 rows.

When you delete things, delete entire rows and columns, and then Excel will
no longer keep their formatting in the file.

From an old post by Tom Ogilvy:

Excel stores information for every cell that it
considers to have been used or maybe even the rectangular area out to the
farthest down and to the right used cell. This includes even if the cell
has just been formatted. If you delete the entire row and entire column of
all areas past your data (select the gray labels, not cells) and then save
and close your workbook, it should reset the definition of the last cell and
will reset your scroll bar and also the file size. This should work for any
version of excel. In Excel 95, just saving should effect this change. In
Excel 97, after doing the deletions, if you execute activesheet.usedrange
in the immediate window of the VBE, it has reset the definition of last
cell. I know this works in XL97 from first hand experience. The other
comments are based upon what I have seen discussed in the Excel newsgroups.
Hope this solves your problem.


HTH,
Bernie
MS Excel MVP