If cleaned up workbook size is still huge…

… and the copy/paste process starts working without an error, but is slow.

This comes up from time to time in the workbooks with corrupted styles: memory overallocation that impacts Excel workbook size. Here is a post with an answer:
http://forums.techguy.org/business-applications/983739-solved-too-many-format-error.html#post7848545

Update [5/1/11]: the link above has been reported to “act up”. The full post is still available, you just need to scroll up. Here is recap just in case site owners decide to expire the post:

Check your used range/memory allocation in the workbooks that are still slow: hit END key (don’t hold it) and then hit HOME key on each worksheet. This action would take you to the last cell with allocated memory in the worksheet. If the row or column index is really high and you don’t have any data in the blank cells you have overallocated memory problem. Just delete blank rows and columns and save the file. The save process would reclaim memory back.

This entry was posted in Computers and Internet. Bookmark the permalink.

2 Responses to If cleaned up workbook size is still huge…

  1. Jeff says:

    What is considered a big file? I have a 20Mb xlxs file I was working before I used the tool. After using the tool, the size decreased to 12Mb. I have over a half million vlookup formulas and match formulas. All of the formulas reference to internal locations.

    • XLGeek says:

      If you think your workbook size is still not optimal for the amount of data and formulas that you have you should check memory allocation. Half million rows data range can easily result in 12MB, but it all depends on data cardinality and how effective the compression is.

Leave a Comment or Ask a Question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s