Excel 2007 dramatically increased file size

Topic

New Reply

Good Morning, I have a user that has a very involved Excel 2007 spreadsheet with lots of internal links, etc. This morning the file size increased from 2 mg to 45 mg and is taking forever to load, move betwen sheets, etc. From Googling I figured out it might be because some worksheet increased in size. A suggestion ws to do a CTRL + End on every worksheet. We are doing that, and I did find one that appears to have grown very large.

My question, how do you bring a worksheet back to it’s “real” size if there’s nothing in the cells? Do you have to delete them? Any other suggestions for the reasons why the file has increased in size are welcome. Thanks much, Judy

Reply | Quote

Replies

Select the first blank row below the last row of data, then Ctrl+Shift+End and delete the entire rows. Repeat for columns, then save the file and see if the size has shrunk.

Reply | Quote

Thanks, Rory. That likely would have worked. However, we’d found the answer late Friday afternoon, and I hadn’t had time to post it here. There is a Microsoft Add-In that when run cleanses out excess formatting on Excel spreadsheets.. We downloaded it and it worked great. In fact we’ve already shared it with our heavy Excel users just in case they need it in the future. Here’s the link:
http://www.microsoft.com/downloads/en/confirmation.aspx?FamilyId=ECFD076C-B873-48CC-B842-DA999C848C82&displaylang=en

I’d recommend that everyone put this in their arsenal of quick fixes. With this fix we didn’t even need to know the worksheet that had the problem, and that made it a timesaver because this spreadsheet had at least 24 worksheets! Thanks much, all.

Reply | Quote

Good to know – thanks. They seem to have kept that one pretty quiet, as I don;’t think I’ve seen it mentioned before.

Reply | Quote

Good find, thank you.

I wish there were something similar to remove duplicate formatting – to avoid the dreaded “you have too many formats” in Excel 2003 !

Reply | Quote

I just want to make sure I understand what’s going on here.

If I select a column and format it a certain way (e.g., some form of date) but I only enter, say, 100 rows, the resulting file size is bigger than if I had only formatted those 100 rows.

The MS add-in will unformat (do the equivalent of a “Clear All”) rows 101 thru whatever (depending on version), resulting in a smaller file.

Now if I come back to that file later and add an entry in row 101 for that col, it will be formated as General so I’ll have to extend the formats.

Thanks.

Fred

Reply | Quote

That is my understanding. In our case something, that the user was unaware of doing. Cause some kind of format in the entire worksheet, that is all the way to the last possible row and column. This was a dramatic increase in size and hence an immediate impact on response time. Likely if you only increased on column there would unlikely be much of an impact. Maybe I’ll try it just for fun. Thanks for your post and reinteration of the problem.

Reply | Quote

For what it is worth, the Microsoft xlsclean.exe download that was recommended only does a partial clean. It removes all content from the unused rows at the bottom of a sheet, but does not clear the columns to the right of the data.
I took a peak at the code and for those interested the “clear” line for the columns was omitted…
‘—
Set ur = wksWks.Rows(r + 1 & “:” & wksWks.Rows.Count)
ur.Clear
‘Reset row height which can also cause the lastcell to be innacurate
ur.EntireRow.RowHeight = wksWks.StandardHeight

Set ur = wksWks.Range(wksWks.Cells(1, c + 1), wksWks.Cells(1, 256)).EntireColumn
‘Reset column width which can also cause the lastcell to be innacurate
ur.EntireColumn.ColumnWidth = wksWks.StandardWidth
‘—

Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel is worth a look)

Reply | Quote

Ahhhhh, bless you for sharing that Judy! I had the same thing happen with one of my files – I saved a new copy of the file to start a new month’s worth of data, and -removed- a whole bunch of data, and yet my file size got 7 times bigger! Downloaded the add-in and it’s back down to a size I would expect. Relief! ^_^

Reply | Quote