Why is my blank worksheet file so big?

[Professor22]

I have a single-sheet Excel 2007 file which contained 900 rows by 8 columns of data which was pasted from the web. Each cell contains no more than 50 characters.

New data is continually being added (approx 50 rows per week) but the file has now ground to a halt because of its size (4.5MB!).

One column of data contained hyperlinks but removing the hyperlinks makes no difference.

I have now deleted all rows bar the header row (which was manually created) leaving ostensibly an empty file but it is still showing a file size of 4.5MB - how can this be?

[martindwilson]

try the excess format cleaner
http://support.microsoft.com/kb/244435

[MarvinP]

Hi Professor22 and welcome to the forum.

There are a few possible resons. I'd first look for a lot of shapes or pictures in your sheets.

Another way to see is to make a copy of your .xlsx file and rename it with an extension of .zip. Then look at the zip file to see which component is the big one.

There is also a tool called PUP that may help at: http://spreadsheetpage.com/index.php/pupv7/home

or http://excelexperts.com/Excel-Tips-W...el-File-So-Big
Or http://www.excelforum.com/excel-gene...-so-large.html

Hope this gives you some help.

[abousetta]

Just my five cents.. big size usually comes from two culprits (formats and formulas) that are usually below the obvious horizon. I have an excel sheet that I distribute for use in the office for people to fill. Before anyone touches it and adds information its about 3 mega. The reason is there are a ton of formulas so people don't caclulate things themselves, color schemes to hightlight areas to input data and areas that should be left blank, grouping of columns and rows ( multiple levels) to allow easy movement throught the large worksheets.

Could any of these be your culprits?

abousetta

[Professor22]

Hi Marvin, I have no shapes or pictures but thanks for the pointer about "zipping" the file. I think that [Content_Types].xml is the problem - it is 453KB and contains many hundreds of lines as follows:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Override PartName="/xl/activeX/activeX542.xml" ContentType="application/vnd.ms-office.activeX+xml" />
<Override PartName="/xl/activeX/activeX973.bin" ContentType="application/vnd.ms-office.activeX" />
<Override PartName="/xl/activeX/activeX1225.xml" ContentType="application/vnd.ms-office.activeX+xml" />
<Override PartName="/xl/activeX/activeX1656.bin" ContentType="application/vnd.ms-office.activeX" />
<Override PartName="/xl/activeX/activeX32.bin" ContentType="application/vnd.ms-office.activeX" />
<Override PartName="/xl/activeX/activeX77.xml" ContentType="application/vnd.ms-office.activeX+xml" />
<Override PartName="/xl/activeX/activeX481.bin" ContentType="application/vnd.ms-office.activeX" />
<Override PartName="/xl/activeX/activeX718.bin" ContentType="application/vnd.ms-office.activeX" />
<Override PartName="/xl/activeX/activeX1164.bin" ContentType="application/vnd.ms-office.activeX" />
<Override PartName="/xl/activeX/activeX1581.xml" ContentType="application/vnd.ms-office.activeX+xml" />
<Override PartName="/xl/activeX/activeX226.bin" ContentType="application/vnd.ms-office.activeX" />
<Override PartName="/xl/activeX/activeX643.xml" ContentType="application/vnd.ms-office.activeX+xml" />
<Override PartName="/xl/activeX/activeX1326.xml" ContentType="application/vnd.ms-office.activeX+xml" />
<Override PartName="/xl/activeX/activeX1818.xml" ContentType="application/vnd.ms-office.activeX+xml" />
<Default Extension="xml" ContentType="application/xml" />
<Override PartName="/xl/activeX/activeX151.xml" ContentType="application/vnd.ms-office.activeX+xml" />

... but what does this mean and how do I get rid of it?

Hi Professor22 and welcome to the forum.

There are a few possible resons. I'd first look for a lot of shapes or pictures in your sheets.

Another way to see is to make a copy of your .xlsx file and rename it with an extension of .zip. Then look at the zip file to see which component is the big one.

There is also a tool called PUP that may help at: http://spreadsheetpage.com/index.php/pupv7/home

or http://excelexperts.com/Excel-Tips-W...el-File-So-Big
Or http://www.excelforum.com/excel-gene...-so-large.html

Hope this gives you some help.

[martindwilson]

have you tried running the excess format cleaner add in? i suggest you try the tools first then come back if they dont work, if you have tried them please let us know first!

[MarvinP]

Hi Professor22,

I still think you have a bunch of shapes on your worksheets. Here is how to test.
Go to your VBA editor and find and start the Immediate window. In the Immediate Window type:

Please Login or Register  to view this content.

and press enter. This will display the number of shapes on the current worksheet. Do this for each sheet in your workbook.

Here is some code to delete all shapes in your workbook. Copy this to your VBA editor and compile and run it. Then save your file and see if it doesn't get smaller.

Please Login or Register  to view this content.

I once had a similar problem. I'd added a few controls to a worksheet and didn't really see or need them. After copy and paste the range these controls were in a few times, my workbook size grew and grew. I had forgotten about trying to add these controls and they didn't even show on the sheet. It took me a few days to figure it out.

[Cutter]

Or (to find objects - which may not be visible ie borderless & no fill)

Ctrl+G - click Special button - Objects

will select ALL objects on sheet - then click on Delete

[MarvinP]

I agree with Cutter.

Toss the code and Go To Special.

Why don't I know how to do that?

I just don't have that "Special" box in my brain, yet!!

[Cutter]

Let's put it down to "Why the heck are you up so early?"

[MarvinP]

So I could write code to do what you did in a few keystrokes?

One of these days I'm going learn how shg and Leath do the "For Each" loops. I'm still in 1980 doing For Next stuff.

I'm just trying to learn something new every day and having questions and answers that can be tested, keeps me happy.

Thanks again for a much easier way to find all shapes in a workbook. Now I wonder if this suggestion will make the OP's file smaller.

Professor22 - You need to let us know what the answer was when you find it. Also mark the thread as Solved to finish it off.

[Professor22]

have you tried running the excess format cleaner add in? i suggest you try the tools first then come back if they dont work, if you have tried them please let us know first!

HI Martin, I did run the excess format cleaner - it told me it had cleaned the file but it made no difference to the size.

[Professor22]

So I could write code to do what you did in a few keystrokes?

One of these days I'm going learn how shg and Leath do the "For Each" loops. I'm still in 1980 doing For Next stuff.

I'm just trying to learn something new every day and having questions and answers that can be tested, keeps me happy.

Thanks again for a much easier way to find all shapes in a workbook. Now I wonder if this suggestion will make the OP's file smaller.

Professor22 - You need to let us know what the answer was when you find it. Also mark the thread as Solved to finish it off.

Hi Marvin, not only did I have a runtime error (element not found) when I ran the code but I also got a "no objects found" message when using the solution provided by Cutter.

[romperstomper]

4.5MB is not a big file by any stretch of the imagination. If you press Ctrl+End on the 'blank' sheet, which cell is selected?

[jwright650]

just a thought,
Are there steps to take in which someone could recreate your problem so we have something to tinker with? ie.(website address to go to and copy said data [900r x 8c] and paste it in a workbook, empty the workbook except for the column header row and check file size)

All of this guessing doesn't seem to be working to resolve your problem.

[MarvinP]

Ok - So now we know it isn't a bunch of Shapes that are making your file big.

But I thought you had a bunch of ActiveX = PartName controls in one of your zip files.

How do you select and remove all those Active X controls?

I thought all of them would be found as Shapes on your sheets. Perhaps you have Userforms that have the Active X controls on them? Do you have any Userforms in this workbook. Look at the VBA list of objects.

HEY - Did you try to find Shapes on each worksheet? Did you run the code while each worksheet was the Active one? If I remember correctly the Active Sheet is the only one searched for shapes.

[MarvinP]

Another thought,

If you open a new and blank workbook and save it, how big is it? If you have stuff in your Normal, Default workbook this may be adding size.

See workbook settings on http://office.microsoft.com/en-us/ex...005199391.aspx

[romperstomper]

What does this produce:

Please Login or Register  to view this content.

[Professor22]

Hi Romperstomper. it takes me to cell N1032 which is the last cell of data.

[Professor22]

Ok - So now we know it isn't a bunch of Shapes that are making your file big.

But I thought you had a bunch of ActiveX = PartName controls in one of your zip files.

How do you select and remove all those Active X controls?

I thought all of them would be found as Shapes on your sheets. Perhaps you have Userforms that have the Active X controls on them? Do you have any Userforms in this workbook. Look at the VBA list of objects.

HEY - Did you try to find Shapes on each worksheet? Did you run the code while each worksheet was the Active one? If I remember correctly the Active Sheet is the only one searched for shapes.

Hi Marvin, a new blank workbook opened and saved is only 10KB. I have to go into a meeting for the rest of the day now so will will look at the VBA question tomorrow. Appreciate your help on this.

[Professor22]

What does this produce:

Please Login or Register  to view this content.

Will try this and get back to you Romperstomper

[Professor22]

just a thought,
Are there steps to take in which someone could recreate your problem so we have something to tinker with? ie.(website address to go to and copy said data [900r x 8c] and paste it in a workbook, empty the workbook except for the column header row and check file size)

All of this guessing doesn't seem to be working to resolve your problem.

Hi John, it's a good idea. I will look into this if the other suggestions don't fix the problem.

Thanks to everyone who has posted replies so far. I will be away for a week after tomororow so will reply to other responses after that.

[Klugscheiser]

Hi guys,

I also have some file size problems. The attached file was created (copied from another workbook) with VBA. I already tried all kind of possibilities, like going search for the end range and deleting all rows/columns, searching for shapes, object, external links, but guess what, the file size remains the same. So I’m getting quite desperate. Maybe you guys have any additional ideas?

Some background; I have a major .xlsm file, that generates charts and tables (ranges) for all questions in one questionnaire. After all charts/tables are created I need to copy them to PowerPoint as editable objects. For that reason I copy the created table (range) to a new created workbook and from then on into the PowerPoint presentation. But expect of a small sized PPT I get a PPT with almost 20 MB (normally such a presentation shouldn’t exceed 4 MB). The strange thing is, that I use the same code in another .xlsm file and there it works like it should.

So if anybody has any idea, I would be really great!

Sheet3.xlsx

[martindwilson]

Klugscheiser Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[Klugscheiser]

Sorry, my mistake, I should have read the rules before posting It won't happen again.

[Jon Acampora]

This can be cause by named ranges that are referencing large ranges in other workbooks.

1. Open the Name Manager (press Ctrl+F3 on the keyboard) and look for any unfamiliar range names.
2. Delete the named ranges that are referencing another workbook, or are not needed in your file.
3. Save the file and check the file size.

This happens a lot when files are passed from one person to another. You could be using a really old file that has been copied into several times. Named ranges can be added to a workbook when a sheet is copied into a workbook. You might not even know they are there.

Hope this helps someone

[FDibbins]

Hi Jon. Please do not respond to an OP until they have complied with mods or senior members

Thanks