Shrink Excel files? 1

[dgallup]

I have an Excel file that seems to have "bloated" itself up to over 10 MB. It does have 8 sheets with several hundred rows and up to 32 columns in each sheet. However, it still seems too big by a factor of about 5 or 10. Is there anyway to see what is hogging all this memory and possibly stripping out unused garbage? I know with AutoCAD you can purge a file and get rid of all kinds of worthless overhead. Sometimes I can take a 10 MB *.dwg file and by purging and wblocking get it down to 100k! Anything like that available in excel?

 

[Haf]

One thing that might help is doing a "save as" and renaming the file. This sometimes helps with office files (Word and PowerPoint, too). I'm not sure exactly what it does but I've been told that office files like to save the entire history associated with a file. I was told that doing a "save as" purges this history.

 

[dgallup]

I tried save as (twice), no luck. I then deleted half the worksheets from each copy of the file. One shrank to 520 KB, the other grew to 13 MB! Looking at the one that grew, on several sheets the vertical scroll bar handle is very small. This indicates that Excel thinks there is a lot more rows of data in these worksheets. I deleted all the rows from the end of my data to the bottom of the worksheet. This did not change a thing. So I inserted new worksheets, copied my data to the new worksheets and deleted the original worksheets. The vertical scroll bar handles in the new worksheets ae much larger indicating Excel thinks there is less data. I saved this version of the spreadsheet and Voila! the file size is now 443 KB!

 

[IRstuff]

Persuant to your comment about scroll handles, if you do a <ctrl>-END, you'll go to the cell that is the intersection of the vertical and horizontal limits of the data as perceived by Excel. If this is way outside of the actual data, then delete all columns and rows between the actual data and the perceived endpoint and then save.

Just remember that ANYTHING you do will leave some sort of footprint in Excel, so you'll need to be scrupulous in cleaning left over stuff.

TTFN

 

[melone]

Do you have pivot tables or complex graphs?

 

[Haf]

dgallup,

I've had similar problems in the past.

For example, when I bring data in from scopes, there's often more data than Excel will load (often 75,000 xy pairs; Excel will only load 64,000). The truncation is usually not a problem since I am usually not interested in the data late in time. In fact, I often delete large sections of rows of the data before graphing.

Here's where things get tricky: for some reason, I HAVE to save the file immediately after deleting the rows. If I don't, Excel throws an error message when I graph the xy pairs, telling me that Excel can only graph 32,000 xy pairs. It throws this error even if I have deleted all but a few of the xy pairs. For some reason, Excel still &quot;thinks&quot; the data is there. The really annoying thing is that it throws the error message every time you activate the graph!

If I save before graphing, I can watch the scroll bar get smaller, indicating Excel is &quot;seeing&quot; less data, just as you said. Then, when I graph, I don't get the error message anymore.

So what I'm saying is this: if you have performed various functions that for one reason or another referenced cells that at one time had data but now do not BEFORE SAVING, it is a real pain in the butt to get rid of them afterwards. There may be methods, but I have not had any luck. The moral of the story is to SAVE immediately after deleting large sets of data.

Maybe someone else could offer an alternate method?

Haf

 

[joerd]

This is a well-known &quot;feature&quot; of Excel. Take a look in the following newsposts for hints:

http://www.mrexcel.com/board2/viewtopic.php?t=70251

http://www.mrexcel.com/board2/viewtopic.php?t=64613

http://www.ozgrid.com/Excel/ExcelProblems.htm

http://www.beyondtechnology.com/tips019.shtml

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[pablo02]

Also, check the formatting of your cells, particularly borders and shading -- remove all except the very essential borders and shading... make sure all unused cells are formatted as &quot;general&quot; for numbers; don't use any fancy fonts... (I usually follow &quot;briand2&quot; suggestions to reduce tmy file sizes..)

 

[rnordquest]

Run a utility called &quot;Unfrag&quot;. This gets rid of a lot of the claptrap with the constant changes, editing, saves, etc. that you do. I used it with SolidWorks files and could often cut the size in half. It also works with Word and Excel files though the results aren't usually as spectacular.

Roger

 

[pmover]

dgallup,

i understand your delima and have a technique that i've used and it does reduce file size. i've not investigated previous posting links, so i cannot elaborate to their usefulness.

1) create another excel file of equivalent number of sheets and void of any data.

2) for each sheet, copy only the used range (range that has data) and paste special-formulas. Make certain that the destination cell (upper left cell address) is identical to original cell address. paste special formats only those cells that you absolutely need. for example, i only use conditional formats in results/data output section of spreadsheets.

3) save the file (i.e filename2).

4) you will likely need to ensure that all links to original file do not exist. simply: Edit-Links, then Change Source and find/select filname2. This will change the links from old file to filename2, thus breaking/updating links.

5) save file again.

double check the spreadsheet functionality to ensure it performs to what is required.

objects do tend to increase file size, so removing any unimportant objects is helpful.

i've encounterd many situations similar to yours and found that by simply creating another blank spreadsheet file of same size & original source data is a corrective measure.

i trust this is helpful...
Good Luck!
-pmover

 

[Catcher]

I had a similar problem and what I did was that I copied each worksheet into a brand new file. I made all the new xls files to read data from one other file that contains the data only. THIS REDUCES THE NUMBER OF FORMULAS in the multi sheet file I had before . Now I have a series of small xls files rather than a very large on.

 

[cmza]

The solution that works for me is,

1) Click on the X icon at the top right, as if to close the Excel application - Note: NOT the icon associated with the particular workbook but the one related to Excel itself.

2) When asked &quot;Do you want to save changes to the particular workbook&quot; click the CANCEL button.

3) Now click on the X icon(Close) again BUT this time the one related to the workbook in question. When asked &quot;Do you want to save changes&quot; click the YES button.

I am currently working on a spreadsheet of 30Mb when saved normally, but this way it reduces to 10Mb.

Hope this works.

 

[Denial]

Cmza,

How on earth did you discover this method?
Do you have any idea why or how it works?

Cheers,

Denial.

 

[briand2]

The best thing is to reduce the size of each worksheet to a minimum.

Assuming the actual data / formulae / etc that you want to keep are located in the range A1:Z12500 (or whatever your real figures are):

Select column Z, and every column to the right of it.
Delete all of these columns.
Select row 12501, and every row below it.
Delete all of these rows.
Select cell A1 (This is VERY IMPORTANT).
Save your workbook.
Repeat for all other worksheets.

Your file will now be cleaned up, being as small as possible.

Note: It really is important that when you save, you are on cell A1!!!

Regards,

Brian

 

[jmw]

Go to this link on the office update site.

http://www.microsoft.com/downloads/...ED-D43E-42CA-BC7B-5446D34E5360&displaylang=en

Sadly, even though you may have Office XP, it only works if you also have Windows XP OS. Sadly i have Win98se.

 

[IRstuff]

Interesting; I tried that trick on a 39 MB file and it GREW to 48 MB.

TTFN

 

[cmza]

Denial,

I was given this tip by an exchange student from Europe that I worked with last year. I have no idea how it works, nor did he, but as you can imagine it has been a topic of much debate ever since.

Regard

cmza

 

[melone]

When you cancel the Save function, will it delete the undo cache?

 

[starka]

Use of "wrap text" in Excel 2002 SP-2 causes file size bloat. I have a spreadsheet with 150 rows, 30 columns. Many columns have word wrap on. With word wrap on, file saves as 3,300 KB (3.3 MB). With it turned off, saves as 190 KB (about 0.2 MB).

Doing Ctrl-End as suggested in this thread identified my "last cell" in row BG, column 65,000+. Deleting all rows and columns to or past that cell (tried several variations) made no difference to file size. Also, the last cell remained same (way past actual end of data).

After I removed all wrap text, my last cell (as determined via Ctrl-End) is now correct: row 150, column 30!

As my hero, Billy MS-G, is reported to have said, "Failure is NOT an option! It's built into the code."

 

[ganelon]

The most effective way I know to shrink an Excel File to its very smallest possible size is to save it with Excel 97. As you may know, the file format has remained the same since this release of Excel (I'm not sure about XL 2003).

Of course, this punctual action won't solve the incremental "bloating" effect as soon as you start saving the file again with a more recent version of Excel. But it is very useful when you want to send a "clean" version of your workbook.

Now, using Excel 97 in your day to day work probably isn't your dream come true. As, to my knowledge, Office won't accept two different releases on the same machine, you will need either a multi-boot or a dedicated second computer (which may easily be quite an old one, because Excel 97 does not need a lot of resources)

Personally, I use this trick to prepare the release of full-featured macro-intensive workbooks that are delivered to our customers. The product is called VisiSal and features a function to build age/income charts dynamically. The workbooks does contain a very broad set of Excel resources including filtered spreadsheets, charts (as objects and as stand-alone sheets), formulas, dialog boxes and, of course, a lot of macros (100+ components).

In the process of preparing a workbook template, I usually see the document shrink from 5.3 MB to 1.8 MB, which is not bad. Especially when you want to zip it and send it by e-mail.