Excel file size too large

[skoolmarm]

My husband is running Excel 2000 on a Vista computer. He has a rather
complex Excel workbook file that he uses on a regular basis (which was
originally created on his old XP machine) that contains multiple worksheets,
data that he updates every week, and a lot of vlookup formulas to put the end
result of all the data from the other sheets onto a summary worksheet. Since
he needs to e-mail the data on the summary sheet and the file size is so big,
he copies and pastes the data from the summary sheet into a new workbook,
using "paste special" to paste the values only. He creates a new workbook
each week using this process. However, each week, the file size for the new
workbook increases drastically from the week before. (The file size for the
master workbook also gets a lot bigger each time he replaces any of the
original data with new info, for some reason.) There is no reason we can
think of why a workbook containing no formulas, just several dozen rows and
less than a dozen columns of text and numbers, should be almost 4MB!

He used the same copy/paste process with the same master workbook back when
he had his old XP computer, and he never had this problem, so we're wondering
if Vista might be causing the problem. (We had also wondered if copying and
pasting the whole worksheet might be causing the file to be too big because
of copying and pasting all those blank cells, but even when he highlights and
copies only the non-blank cells, the file size is still ridiculously large,
so obviously that's not the problem.)

Is there anything he can do to keep the file size of the weekly workbooks,
if not the master workbook, down to a manageable size?

 

[Arvi Laanemets]

Hi

In Excel files, the history of all changes may be keeped hidden for users.
This may be the cause for file size blowing up. To mend this, you can save
the file with a new name (Save As ...), and then overwrite the old file with
new one (delete old file, and after that save the new file with old name
using Save As again).

To transfer part of data into another workbook, you may consider using links
or ODBC query/queries to retrieve the needed part of data, and generating
wanted report based on retrieved data there.

Or you can write a procedure, which generates a new Excel workbook from
original one, accordingly conditions set on special sheet (p.e. you have a
sheet Export with some fields where you can determine some parameters like
month, customer, etc., and a command button to start the procedure. When the
button is pressed, the procedure creates a new Excel file, and fills it
accordinglly set conditions - without any formulas).