How to trim the size of a file?

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Banned
Joined
Sep 18, 2006
Messages
3,274
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
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

Active Member
Joined
Jun 11, 2007
Messages
328
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

Banned
Joined
Sep 18, 2006
Messages
3,274
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,105
Messages
5,622,763
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top