Utility to "clean up" or "defrag" large Excel file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a tool/utility that I can run on a very large Excel file to
essentially "clean up" the file? This file has existed for a while - lots of
things added and deleted....I is there such a utility?

Thanks!
 
Sabrina

Generally file size is increased due to Excel thinking the used range on sheets
is much larger than what it really is.

This is caused by adding rows and columns then clearing them. Excel still
retains these cells as "used".

See Debra Dalgleish's site for methods of resetting the used range on sheets.

http://www.contextures.on.ca/xlfaqApp.html#Unused

Also if you have a lot of VBA code in modules you can clean them up using Rob
Bovey's codecleaner add-in

http://www.appspro.com/Utilities/CodeCleaner.htm


Gord Dibben MS Excel MVP
 
Sabrina said:
Is there a tool/utility that I can run on a very large Excel file to
essentially "clean up" the file? This file has existed for a while - lots of
things added and deleted....I is there such a utility?

Thanks!
Click to expand...

------------------

The only sort of clean-up that I'm aware makes a meaningful difference is if
your file has accumulated a bunch of empty data rows for which Excel still
stores formatting information. It can sometimes double the size of your stored
file. Hit Ctrl-End on a sheet and if the cursor goes to a cell well beyond the
end of your actual data then you have an opportunity to get rid of some meta data.

Look at Debra's website for a solution (and a lot of other good stuff!)

http://www.contextures.com/xlfaqApp.html#Unused

If you have a bunch of disused macros then I suppose you can delete them too
though that's never made much improvement in my particular cases.

Good luck...

Bill
 
Bill said:
------------------

The only sort of clean-up that I'm aware makes a meaningful difference is if
your file has accumulated a bunch of empty data rows for which Excel still
stores formatting information. It can sometimes double the size of your stored
file. Hit Ctrl-End on a sheet and if the cursor goes to a cell well beyond the
end of your actual data then you have an opportunity to get rid of some meta data.

Look at Debra's website for a solution (and a lot of other good stuff!)

http://www.contextures.com/xlfaqApp.html#Unused

If you have a bunch of disused macros then I suppose you can delete them too
though that's never made much improvement in my particular cases.

Good luck...

Bill
Click to expand...

----------------

Note that you may want to also check the current thread "Huge Files Don't Know
Why" on this board. Somebody there had a few hundred unused named ranges that
also took up space. Dave Peterson pointed to an automated solution there.

Bill
 
Back
Top