Balooning file size in Excel

[Mike-Baines]

Hi, I'm a new poster and apologise if there is a thread already covering this.


I have scowererd the internet in search of an easy solution to my problem but have failed in my search thus far.


I have a large spreadsheet, which at random times without any discernable reason increases in size by many mega bites. Its taken quite a while to build and thus I don’t really wish to re-build it.


The latest example is when I was deleting a few entries in cells no longer used and re-formatting a few cells also (No more than a couple of hundred cells in a spreadsheet containing over 80 tabs). Upon saving the file size increased from 48MB to 67MB. Is there anyway of finding a solution to this as the spreadsheet is practically the same yet 19MB larger? This has happened before as the file was much smaller than 48MB a few day ago.

 

[SirJB7]

Hi, Mike-Baines!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


This is the general procedure. But in your case I think that without giving a look to the file it'd be impractical or impossible to try to give you any advice. I faced two cases like this in the last few days, here's the link to one of them:

http://chandoo.org/forums/topic/how-to-compress-excel-file#post-58214


Regards!

 

[snadeemshaikh]

Hi Mike,


Now that you have gone through all the procedures mentioned by Ninja & in case your problem is still not solved. Here is an easy solution.


The problem you are facing is very common for me & the reason you have described what you did before the size was increased made it very easy for me to provide a solution for you.


Your statement

The latest example is when I was deleting a few entries in cells no longer used and re-formatting a few cells also

Well you deleted few entries & then performed "REFORMATTING". Now I don't know which version of Excel you are using but I used to face this problem post Excel 2007 versions only.


Cause of the problem: When we apply formatting to an excel sheet, sometimes entire sheet of excel applies the formatting i.e. post Excel 2007 version having 1048576 rows & 16384 Columns.. Now the formatting applied by you is saved on 17179869184 cells & thus size gets increased. Sometimes even I have not applied formatting to entire sheet still some of the formatting does get copied to maximum cells on the sheet.(I don't know whether this is an Excel bug or a bug on me.. I work fast on excel & thus don't remember much of the steps I have done while running my fingers on keyboard.


Solution: After Googling for few mins I could not find any solution, so tried on my own. You have to go to each sheet of your spreadsheet & delete blank cells (so that the formatting applied will be deleted too. For example: You go to Sheet1 - Range A1. Go to the last row where your data ends & post that just select all Columns by pressing (shift+Space) & then to the end of the sheet(Ctrl + Shift + Down arrow) - Right Click from your Mouse then Delete. Repeat the same for Columns too. My problem gets solved 100% of the time by doing this (I usually have 4-5 sheets i.e. 600 kb of data which goes to 12-15 MB & after applying above steps they come back to 600 kb of data.)


Your Case: Now as you have told there are 80 tabs, manually trying the above solution would not help. You can create a Simple macro that will do that for you in minutes. Also your size have not increased much so formatting would have been copied to 40-50% of some of the worksheets.