Nimbus

I've been going though one of our work servers as it's getting a bit low on disk space. The majority of large files are Excel spreadsheets. When I looked at some of these there does not seem to be any reason for the large size (2, 3, 4mb each and there are hundreds of them).

Does anyone know a quick and easy way to reduce the size of them? I have a feeling Excel is saving blank cells rather than just the ones with data in.

Any ideas?

mega_stream

Zip em up?

DJ Dunk

iTrader: (5)

It was a problem with older versions of Excel, file sizes seemed to get bigger even if you deleted rows.

Try deleting any blank sheets and delete and clear any blank rows/columns again Helps in Excel 2000/2002.

HHxx

You could try opening them up in Excel then do a "save as" on them...?!?! maybe ?!?!

father_jack

Zip em or use file compression on the server.

try doing save as on the files rather than save - as this clears out all the edits.

boomer

Nimbus,

you might want to check that they are not simply renamed (prawn) MPEGs!!!!


mb

Scooby-Doo

We had this at work with people complaining of 15mb Excel sheets. It turned out they had copied cell formats (i.e bold, font etc) to all cells. Although the cell will apear blank with no formulas the format must be removed to reduce the file size. Hope this helps.


David

Nimbus

Thanks chaps.

I can not compress them as the users need to access them directly (and it would cause untold confision for most of them having to deal with zip files ).

It think it is just a problem with formating in empty cells. I don't fancy going through each one though and removing the unwanted formatting. Looks like that's the only way though

father_jack

The users won't have to do anything if you use file compression on the server - it's transparent on microsoft stuff

Miles

Cut and paste the data into a new spreadsheet?

Crispin

Ok,

Excel will "clean itself up" only on file opening, so try the following for a couple o sheets:

File > Save
File > Close
File > Open
File > Save
File > Close


if this works for these let me know, and i'll write you a VB prog to go thru all excel files in any particular folder and do the above automatically.......


hth

it could be large for other reasons too, e.g. if you save as multiple formats, Excel 97 / 2000 which essentially saves the file twice in the same file....

[Edited by Crispin - 2/14/2002 3:16:25 PM]

Nimbus

father_jack,

I've had a look and compression is on, so the problem is not as bad as I though. Even so, the average seems to be about a 50% reduction.



Miles,

1852 Excel files over 1mb. . Mmm don't think so



Crispin,

Tried it on a few, but there was no change in the file size


If there's no technical fix, then it looks like "user education" is in order. Damn, I was hoping to avoid this as they don't seem to understand what it's all about so just ignore it - until the server runs out of space and they loose all their work [EDIT]. Not that I find that amusing or anything

Maybe some archiving procedures are called for...

Thanks for everyones input. If anyone does find a way, please let me know

Cheers

[EDIT] They are all Excel97.

[Edited by Nimbus - 2/14/2002 3:30:11 PM]

CaptainBeakie

You might want to see about saving them as CSV files.

Excel uses 'deltas' this can be reduced by cutting the data from one workbook into another one.That's if you want to leave them as '.xls'

Hope that helps, otherwise it's get some more disks.

Crispin

You wouldn't save all the formatting if you did that unfortunately......

Nimbus,

There's an Excel MVP, who has a page of poss solutions for you to try here, the MVP's know their stuff, so give the LastCell thing a go maybe.....if I can help with any VB / VBA stuff just let me know.....

http://www.mvps.org/dmcritchie/excel/excel.htm

http://www.mvps.org/dmcritchie/excel/lastcell.htm

Nimbus

Crispin,

Thanks. This looks like it will help. I'll have a look through this tomorrow and see if it will fix them.

Cheers