How to trim the size of a file?

[Johnny C]

I've a file, 5 sheets, 200 rows, 24 columns in each. Say 60% of the cells are populated, all with values (i.e. no formulae).

There are no embedded objects, and no conditional formatting. There are no macros, no hidden sheets.

The file size is 25Mb, I would have expected 2-3% of the size. Excel has no problem but our network is slow and the file can take 2 mins to load on a bad day.

It has no custom fonts beyond the standard ones.

I am guessing that some numty has formatted each cell separately. I copied values and set up identical formats in a new sheet, and the file size was <½Mb

If it is the formatting, how can I remove it?

I'm guessing that the file was generated by a macro, possibly initiated from SAS.

 

[Gerald Higgins]

If you think it's the formating, why not try re-formating the entire file ?

Click on the box at the top left (to the left of the A column heading and above the row 1 indicator) and set the font, fontsize, background colour etc for each sheet, and repeat for all the sheets.

 

[Stormseed]

Is your file filled with borders?

If the file is exported from an external source, try pasting the whole data as Values in another workbook and save the file.

 

[Johnny C]

I copied everything as 'paste values' and reformatted it to look like the original. File size was 437K. I'd remembererd reading here that Excel stores the format for all ranges for which it has been specified within a sheet, i.e. top left cell, lower right cell, format details - even if the range is only one cell. So if someone has gone through and formatted each cell separately, Excel will store the formats for all cells separately rather than as a single contiguous range if it had been formatted as such.

Alas there is quite a bit of different format within each sheet, blocks with borders, different number formats etc. I was wondering if a macro could find all cells with identical formats and 'block' them together. Whilst it's easy to sort for one spreadsheet, we get a lot from different parts of the world with similar problems, so a macro to clean a file up would be handy. One day when I'm bored maybe...

In the end I went and saw the chap who creates the file and gave him my revised template and he'll use that in future. He's happy as it took him an age to open the template and save with refresshed data plus he was zipping up several 25Mb files each time, now he can send them through Outlook with no problems.

We cant send files more than 3Mb via Outlook, stupid company policy that's hardcoded into Outlook and access to folders is restricted to single departments, so sharing files is a pain in the proverbial if the files are a bit big.

 

[theozz]

Hmmm that sounds bad.

I Suggest please make new file

some reason
1. when copy new sheets = Hold sheet' tab and copy
this works make new formatconditionsand other's.
--> Please copy cell to cell
2. Inspect Virus?
--> Kill All Virus and make new file
3. How old the file?
when excel save some information to file
as, user name or time and etc
so the size might increase
--> make new

 

[Stormseed]

Does your file contain Pivots? Can we have a look at one of your sheet data in your file? Is your computer protected by an antivirus with latest definitions? Does the same file have a different size on another computer? Is your file shared on the network? If yes, how many users access the file at the same time?

There are infinite possibilities of a file for which size is growing...you need to call microsoft technical support for an effective resolution to your problem.