Compacting or Shrinking an Excel ?

[TechFobe]

Hi Folks,
Quite sure you've heard this one many times, the story of an excel file growing to a disproportionate size and seemingly unable to bring it back to its normal size....I've tried what I assume is the basics, copied to a new sheet, scrolled down to the bottom of the file and deleted any excess fields but to no avail.

Read up a little and there's a vast amount of information out there but ideally I'd prefer a simple solution that's tried and testing if anyone is able to spread there own experience and tested solution then I'd love to hear about it

Anyway, thanks for taking the time to read this post and thanks further still if your able to offer your own insights.

Happy computing.....Steve.

[jdc2000]

Some additional information might help us determine a solution.

Questions:

1. If you start Excel, create a new, blank sheet/workbook, and save it, what size does it show on the disk?

2. What size is the problem sheet/workbook showing on disk?

3. Do you have any macros on the sheet/workbook?

4. What happens if you save it as a .csv file - what size is it on disk?

[TechFobe]

Hi .... The excel file is showing up as a 20mb file in its present state
and was previously about 400kb.....The exact size at the moment is
about 19.4mb as I've shrink it a little but nothing like its original size.

There's no macros on the sheet as I'm aware .... The file hasn't been
saved as a .csv file though which is certainly an option to explore.

The workbook has about 7 sheets in total, creating a new workbook
has only saved a couple of hundred kb's. Copied the sheets one at a
time and I guess there's probably a simpler solution but I'm not as fluid
with excel as I'd like

[jdc2000]

How many rows and columns in each sheet on the problem file?

What size is a saved, new, blank worksheet?

[jerryctx]

First, make a backup copy of the file

Excel saves only the used elements in a spreadsheet. Unfortunately, once used an element is always used. For example, if you apply formatting to an entire column, its all considered used even though you never enter data in most cells.

Try this, save the spreadsheet as an HTML file (only the currently active cells are saved). Close Excel. Open Excel. Open the HTML file. Save as an xls file.

Please, please, please make a backup copy first. Since I retired I can't justify paying for MS Office so I can't test this procedure. Good luck.

[Steve R Jones]

Hit the END then HOME keys...This will take to the lower most right hand corner of the sheet. Often time you will find you hae way more rows and colums then needed. Delete the extra row and columns...not just the cells....Save and then reopen the spreadsheet.

[TechFobe]

Just wanted to thank folks for there input and to let you know that I try out the suggestions and recommendations tomorrow and post back a reply.

[TechFobe]

Hi Folks.

The original problem still remains despite attempting the suggestions noted ... The saving to .htm and saving back to .xls saved about a 1mb and the file is now 18.5mb .. Using the 'home' & 'end' options didn't show any issues. The document has 8 worksheets ... the first is 580 rows and 16 columns...Another has 484 rows and 16 with the rest around 100 rows ... all have 16 columns....One worksheet goes up to 32 columns and I'm gonna take a look at that one over the weekend to see what if any abnormalities maybe at faulty.

Thanks again.

[jdc2000]

8 sheets that size should not require 18.5 MB of space.

[Steve R Jones]

Using the 'home' & 'end' options didn't show any issues. The document has 8 worksheets

Did you do the End/Home trick on all EIGHT sheets and any blank ones?

Do you by any chance have LARGE Images pasted into any of the sheets?

[TechFobe]

Hi All....There aren't any large images posted and I did use the 'End/Home' on all 8 sheets .... That said I didn't do that on any blank ones as I didn't deem it necessary but that's certainly worth another look....If that doesn't work then I'm gonna extract 7 worksheets out and try saving those and exclude the worksheet that has 32 columns as that's the only one which looks some what different ... If that doesn't work then I guess it maybe a drawn out process of extracting each worksheet separately, saving and seeing when the file size expands exponentially...

Keep you posted all the same and thanks once again for your input

[TechFobe]

Hi All ... My problems still continue I tried exporting the worksheets separately and all the entries lead to the file ending up at 23mb not what I was inspecting. The first worksheet has 16 columns and 580+ lines and it saved at 8mb....Pretty much knew I was on a downhill curve from then onwards.

If there are any further suggestions then I'll gladly attempt them, otherwise I'll bring this to a close and just plod on as best ......

Thanks for reading

[jdc2000]

What size were the .html files? Did you try saving the sheets as .csv files (comma delimited text files)? The .csv files option removes ALL formatting. If so, what size were those files, and what happened when you opened the .csv files in Excel and saved them as .xls files? What happens when you open the .csv files in Notepad? Do you see any extra, entries (lines with blanks and commas)?

[SpywareDr]

Remove hidden data and personal information from Office (2007) documents
http://office.microsoft.com/en-us/he...375931033.aspx

The Remove Hidden Data tool (Add-In) for Office 2003 and Office XP (Info)
http://support.microsoft.com/kb/834427

Office 2003/XP Add-in: Remove Hidden Data (Download)
http://www.microsoft.com/downloads/d...displaylang=en

?

[TechFobe]

Hi .... Saving the file in .html saved about half a meg as I recall and there was very little difference with saving with a .csv extension. Don't recall opening the .csv with notepad though and I'll certainly try that and read the links suggested by SpywareDr ... Let you know
how I get on Thanks again.