Excel workbooks growing way too large for no apparent reason...?

MacBlimp

New Member
Joined
May 28, 2008
Messages
6
Hello,

a colleague of mine is experiencing a weird problem with a number of his workbooks in excel 2007. His workbooks grow a lot when performing seemingly innocent actions. An example from yesterday:

Adding a few hyperlinks to other sheets in the workbook caused the document to grow from 1 072k to an overwhelming 38Mb. No other operations than adding the links were performed between saves.

The document is in excel 97-03 format and he's working in excel 2007.
For some reason, deleting all rows and columns outside the used range of the worksheets (but not neccessarily the worksheet where the canges were made) brings the filesize back to normal for some reason.

If anyone can shed some light at what's happening we'd be very grateful.

Thanks in advance :)
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to MrExcel.

Is the whole worksheet formatted with for example, Borders, Colors, etc. rather then the used range ? Are there any type of formulae in the workbook ? Does it contain a pivot table or tables ? Does it have external links to more than one file or template ? Is the file saved on a network ? Is the file shared if it is on a network ? What kind of data do you have in the workbook ?
 
Upvote 0
- The worksheet is not formatted beyond the used range.
- There are a few formulas around to calculate some percentages on some of the sheets. Not neccessarily on the sheets that seem to cause the problems, though.
- The workbook contains several pivottables, these are however removed at a later stage in editing process, and the size-problems persist. Also, the problem also seems to occur on sheets with or without pivot-tables, not "favoring" either.
- No external links. Just to other sheets in the same workbook
- File is saved on a network server.
- The workbook mostly contain various tables of sales data (foundation for a market analysis), one or two of these reach a few thousand lines. Most are a lot smaller than that though (30-60 lines approx.). There are a few diagrams accompanying some of the tables. Also a few pages used mostly for text (approx 4 printed pages combined).
 
Upvote 0
As per my understanding, the problem seems to persist because of the Pivot tables irrespective of whether you remove them or not at a later stage. Whenever you create a Pivot in Excel, it would consider a base file. The existence of this base file with the Pivot is the main cause of the file oversizing. File saved on a network is an added possibility for the latency and oversizing.

Also, the problem also seems to occur on sheets with or without pivot-tables, not "favoring" either
Also I guess, that you are experiencing this issue with a single file and not multiple sheets. We are not considering any sheets here and we are talking of whole files or workbooks.

Did you check the file size on a different computer ? DOes this happen with all the workbooks or just this one ? What kind of formulae is being used in your workbook ?
 
Upvote 0
The file(s) have ben tested on several different computers in the network, and the filesize remains constant. Also, the problem is not restricted to this file alone, but is encountered in several different files, but not all. It seems to occur primarily when working with files in a excel 97-03 format, so compatibility might be an issue (?). The forumlas in use are simple maths like =F1/H1 (to return a percentage).

The confusing part(s) is the randomness of the occurence, both in where it strikes, and how severely. The example from the opening post where adding a few internal links increased the file size nearly 400 times is one of the more extreme situations, but files growing 20-30 times is pretty usual. Also, as mentioned in the first post, trimming the sheets (deleting rows and colums beyond the used are for each sheet) seems to get the size back to normal, but this is a rather tedious process and shouldn't be neccessary. It also seems to manifest itself at different sheets from time to time. Sometimes trimming sheet 3 might reduce the size by 5 Mb, while at other times doing the same does nothing (but trimming shhet 5 might have the same effect).

At some point we ran a macro which saved each sheet in an affected workbook into separate files, just to see if we could track which sheets were affected by the respective filesizes. Most of the files were at expected size, but some were a lot larger (several Mbs). However, running the same macro at different, but similarily built workbooks provided different results (other sheets bloated, others not).

I'm not really sure if this makes much sense, but we're rather confused as to what is causing the problem.

:confused:
 
Upvote 0
Dude, we too are victims for we regularly experience these ill functionality in Excel many a times and we could not do anything but to create a new workbook with the same format and data. Try it, you may resolve the issue that you are experiencing at the moment.
 
Upvote 0
Guess we'll have to try that. Thanks a lot for the taking time to reply and for your help. I really appreciate it :)
 
Upvote 0
Hi Erik,

Using Office 2003:

I have been reading up on the growth issue concerning Excel.
I have a workbook that has been in use and continually developed for about 10 yrs. I am trying to bring the size down by re-writing old code, deleting unused lines and rows etc. I have copied the code to note pad and deleted the modules, then created new modules and replaced the code.

The problem I have is the things I can't see. For instance, I can delete the code for my userforms but not the userforms themselves. I have the same problem with the sheets. I have merged cells, condition formatting, validation cells, the works.

I do not want to 'copy' the worksheets because I don't want the baggage which it caries in the background.

What I have been attempting to do is to create a workbook that can look at an existing workbook and reproduce every aspect into a new version from scratch. I want to do this cell by cell using values and properties without copying anything. (It would be pretty straight forward to copy it. That would be too easy;)

I currently have much sheet functions handled. The follow areas still have me stumped: Validation & merged cells.

I have not attempted to reproduce the code but I think I can figure that out. Any pointers would be welcome.

I do not have a clue how to reproduce the userform controls although I am sure it is doable as well.

I know that this may seem like a lot of effort but I believe there is a real need for this. I have seen odd things from time to time. like the arrow keys sending the cursor the wrong way. It would be really handy to just start fresh once a project is tweaked just the way you want it or to refresh one every once in a while.

Please feel fee to copy this post to anybody you know that could point me in the right direction.

Thanks in advance.

Mark
 
Upvote 0
I had this problem for close to 2 years.

Have not had it since I:

1.) developed ONE new "clean" worksheet with the formating (including conditional) I needed and used it to develop the other pages needed

2.) converted that worksheet into the new xlsx format.

3.) put a paste special - values only button on my taskbar.

4.) stopped using the "normal" paste button in Excel.

My workbook CONSISTENTLY stays at 365kb.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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
Back
Top