Bloated Excel 2010 File Size

[HOOS77]

I have multiple Excel files created in Excel 2007 (all cloned from the same master file) which are used by our managers that unexpectedlyl increase in size when saved in Excel 2010 (as .xlsx files in both cases). The amount of data is insignificant and does not vary that much between the various files. Prior to saving in Excel 2010, the files were about 1.1 to 1.3 mb; afterwards, they ballooned to 11 - 12 mb. There are no pivot tables, no macros and no excessive formatting.

I opened the master copy (blank, no data) in Excel 2010 and immediately saved without making any changes and the file increased from 1.11 mb to 12.4 mb !! Files of this size take much longer to open and save which is a major inconvenience to our managers. Interestingly, when I saved as a binary file, the file size dropped to 1.55 mb.

Have searched in vain on Microsoft and other sites for relevant information and found nothing helpful. There has to be a reason for this, but I'm stumped at this point. Any help would be greatly appreciated!

Thanks.

 

[RoryA]

Most of the time simply referring to the Usedrange property of the sheet in code will reset it.

 

[SomeKindaGreekGod]

Reading through this and several other threads I have a similar issue, or perhaps the same issue much worse: I have a client who has several workbooks (with macros) exhibiting this behavior. The problem we're running into is when we right-click Delete the extra cells... nothing ever happens. The screen flashes, one would assume something has happened. However, hitting ctrl+end still brings me to cell H1048576. I've come across a couple posts that state you must immediately "Save" the file after performing this; makes no difference. File size is still 2.5MB whereas the "un-affected" version is 30kb.

Macros can open up a whole new world of hurt, but without more info on what the macros do, I would check to see if there are sheet change events that may be affecting your clearing of the cells, or your macros may be running through all the rows in the sheet instead of just the rows in your range.

You've probably tried this already, but try selecting all the extra rows and then deleting, and likewise with the columns.

I would also try the zip file route I mentioned earlier to make sure you're looking at the right sheet if you have multiple sheets. That was my main issue since I had many sheets and previously wasn't able to find the problem ones.

 

[scottdrenn]

Macros can open up a whole new world of hurt, but without more info on what the macros do, I would check to see if there are sheet change events that may be affecting your clearing of the cells, or your macros may be running through all the rows in the sheet instead of just the rows in your range.

You've probably tried this already, but try selecting all the extra rows and then deleting, and likewise with the columns.

I would also try the zip file route I mentioned earlier to make sure you're looking at the right sheet if you have multiple sheets. That was my main issue since I had many sheets and previously wasn't able to find the problem ones.

Thanks, SomeKinda -- I have tried deleting the cells, the workbook just never "accepts the change." The .zip file worked to the point that it validated it was sheet 2 (the sheet that LUC is H1048576) that's bloated and causing the issues.

Looks like I get to spend the week talking to the vendor$. If I get a "solution" I will try and remember to report back to the community.

...wish me luck!
scottdrenn

 

[CaterinaAnna]

Mine quite often behave like this if a user (ie me, usually ) copies and pastes an entire column instead of an appropriate range. It's not so bad now I'm usually transferring from .xlsx to .xlsx, but it did happen every time when it was from .xls to .xlsx
ASAP utilities delete unused empty rows/columns was very useful, but I couldn't use it on on a 64 bit machine, so thank you to those who posted other get-rounds.

 

[SomeKindaGreekGod]

Thanks, SomeKinda -- I have tried deleting the cells, the workbook just never "accepts the change." The .zip file worked to the point that it validated it was sheet 2 (the sheet that LUC is H1048576) that's bloated and causing the issues.

scottdrenn

Scott, the last thing I have is to make sure you're looking at the right sheet. Because Sheet 2 in the zip file doesn't necessarily correspond to the sheet with a code name of "Sheet2." It took me a while to find this out, but I had to go into the xml to find what the sheet's code name was that had the problem.

Good luck!

 

[jcurtis95]

What I was experiencing was that somehow Excel thought that my sheets have data in all the rows. I selected all empty cells and deleted those rows.
Easiest way to do this is to click the column A at the first row without real data. Then press SHIFT + CTRL+END from the keyboard. You should now have a large selection of cells. Then click the selection with the second button of the mouse (right click) and select Delete from the pop-up menu. From the Delete window select Entire row. Now save your workbook and file size should have dropped if you had the same problem as I was having.
If the file size is still too big, repeat this procedure to all your sheets. Hope this helps!

I hope this 'thank you' to this older post gets to you...I just want you to know that I have been trying to remove "Bloat' from my Excel fles for the past [4]days. I awoke this morning and started over finally findng this site and your post and it worked. My Excel records had bloated to 59.2MB; after applying your method, it is now 228KB! Thank you!!

 

[Xaiopt]

I noticed the scroll bar remains very small after deleting the bloated rows and columns. The trick was to press the save button.

How to fix scrollbar in excel to get rid of extra empty rows? | Excel Help HQ - Helping you Solve Excel Problems

 

[Cris911]

I am having these issues repeatedly.
1. Opened the .csv file (NO formatting, no formulas, no objects, no VBA, no MACROS)
2. Saved to local desktop folder 1,545 KB
3. Opened File, Saved as Excel Workbook 1,734 KB (4,946 rows)
a. Column 1 remove all single quotes (replace all)
b. Format column to Custom ‘00000000’
4. Copy and Paste Special - Values from a separate workbook 179,332 rows (existing records no formatting, no formulas, etc.)
5. Save and Close – 35,216 KB

So TRY AGAIN:
6. Opened the attached file
7. Copy all data only (not the whole spreadsheet)
8. File > New Blank Workbook
9. With cursor in A1, Paste
10. Save as Book1.xlsx – 33,632 KB

So TRY the fixes:
11. Reopen Book1.xlsx
12. Selected all columns to the right of the data DELETED, then CLEAR All
13. Selected all rows to the bottom of the data DELETED, then CLEAR All
14. Save and Close –33,632 KB

Done all the obvious fixes, removed columns, rows, and cleared all formatting to the right and below the data
Used CLEAR Formats, Comments, and Hyperlinks
As mentioned there are no formulas, no objects, I have not renamed the file, etc.
No macros, no VBA of any kind.
No special setup of Excel
No add-ins
Brand new computer - 3rd time
NOT working off a shared drive
Tried copying all the data off one sheet, and paste special values to another tab (sheet), save the file – did not change anything

Sent the file to another user in the office (email), filesize reduced only when the user copied the data and pasted to a new file and saved.

Frustrated beyond belief.

 

[StealthSkater]

ActiveSheet.UsedRange does work as long as the workbook is open. But if I close and re-open it, the file bloat reappears. It's like the ActiveSheet.UsedRange command is forgotten as soon as you close the .xlsx book.

This is a poor man's cure. But it works.

I save the .xlsx workbook as an .xls . Then I close it. Then I open it and save it as an .xlsx . Then close.

When you open it again, the file bloat should be gone.

 

[PCRIDE]

The other thing to try which I didnt see mentioned, SAVE AS with a NEW FILE Name. Using the same file name, and Save As other formats for some reason still retains the data, whether its hidden or not.

The XLSX format is much different than Excel 2007, 2010 is when Microsoft starting using XML, remember they were sued by someone on patent infringement and later released Office SP1 which removed the patented logic using XML.

My guess its the version because it actually creates a new format when you convert.

Other notable mentions

If you open the ORIGINAL 2007 file with 2010 version, I presume it doesn't lag and works just as it did using 2007?
This appears to only happen when you save with 2010 version (it adds the XML stuff) - This is what is probably inflating the size
If nothing changed on the file or formats, then the cuprt is Excel 2010.

Try saving as a different name in the 2010 - This has worked for me many times.