Speeding Up Opening and Saving a Workbook

by Allen Wyatt
(last updated March 14, 2020)

2

Chris asked if there was a way to "compact" a large workbook in order to speed up its saving, opening, and closing. The short answer is no, there is not a way—unlike database programs (Microsoft Access), Excel doesn't have a function to compact a file.

That being said, there are things you can do to speed things up, particularly if you've been using the workbook for a while. Workbooks tend to get "bloated" over time, just from routine use. Perhaps the biggest culprit is macros, as multiple edits to macros can cause fragmentation of how the macro code is stored in a workbook. You can export, delete, and import macro modules, which will fix much of the problem. You can also use a third-party "cleaner" for macros, such as Rob Bovey's CodeCleaner:

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

You should also press Ctrl+End in your workbook to see where Excel takes you. If it is far beyond the last column and row of your actual worksheet data, then Excel is saving extra data (albeit blank data) with your workbook. Delete the actual columns and rows that are to the right of or below your data, then resave.

You can also do a "Save As" to save your workbook to a new file. Compare the sizes of the old and new files, keeping the one that is smaller. If that doesn't work (and you are using Excel 2000 or later), save the workbook as an HTML file. Then, use Excel to open the HTML file you just saved, then save the HTML file as an XLS file. This "round trip" through the HTML format has been known to greatly reduce file size. (If you do this, make sure you export your macros from the old workbook and import them into the new one. Macros never survive a round-trip HTML experience.)

Check to see if your workbook contains non-Excel objects, such as graphics, sound files, or other embedded objects. These can take quite a bit of file space. If you don't need them, delete them.

If your worksheet has several PivotTables, there are several things you can do to reduce file size. These ideas are recounted in other issues of ExcelTips.

If your workbook contains very large formulas (the ones that run on and on for lines), examine the formulas to see if they can be shortened in any way. Perhaps you could extract common portions of formulas and assign them, as "subformulas," to defined names, and then reference the names in your larger formulas. This especially saves space if the formula is used in many places in the workbook.

Also, using Excel's "track changes" feature can make workbooks larger, particularly if you are keeping a history of changes. Consider turning the feature off if you don't really need it.

If you can find no way to reduce the size of your file, and it is still loading slowly, check to make sure that the actual slowdown isn't attributable to your anti-virus software. If it is checking and rechecking your Excel files, that can add quite a bit of time to any of your file operations—particularly if you are on a network.

Finally, you can always rethink the organization of your workbook. For instance, I had one workbook in which I tried to keep everything historical related to a project. The result was a huge, huge worksheet that took forever to load over the network. The best solution I found was to copy the historical data I no longer needed to a different workbook, and then do my work with the most recent project records. This approach offered a dramatic speed improvement in file operations.

Similar approaches might be taken with other data. For instance, instead of keeping data for all clients in a single workbook, you might put each client's data in its own workbook. The advantage is that the individual workbooks are easier to open and manipulate than the large single workbook. You can then use other features in Excel (including macros, if desired) to extract data from the various workbooks to produce summary reports.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2402) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Deleting All Headers and Footers

Headers and footers add a finishing touch to documents, but sometimes they can be bothersome. You may need to remove them ...

Discover More

Avoiding Rounding Errors in Formula Results

Some formulas just don't give the results you expect. Sometimes this is due to the way that Excel handles rounding. ...

Discover More

Making Sure Num Lock is On

The Num Lock key controls how your numeric keypad behaves and, possibly, a few other behaviors. Here's how to adjust ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Creating Default Formatting for Workbooks and Worksheets

Not satisfied with the way that default workbooks and worksheets look in Excel? You can easily create your own defaults ...

Discover More

Open Workbooks Don't Display

Have you ever opened a workbook, only to have it not display your worksheet data? This can be very disconcerting, but it ...

Discover More

Opening a Workbook to a Specific Worksheet

When you open a workbook, Excel displays the worksheet that was visible when the workbook was last saved. You may want, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 + 8?

2020-09-12 02:47:11

Harish

You should also press Ctrl+End in your workbook to see where Excel takes you. If it is far beyond the last column and row of your actual worksheet data, then Excel is saving extra data (albeit blank data) with your workbook. Delete the actual columns and rows that are to the right of or below your data, then resave.

Wow this worked like a charm. Thanks to Allen, u saved my day :)


2020-03-14 06:39:06

roY tAylor

I used the code cleaner that Allen is recommending and it did the job, the only down side I found was it removed all the REM lines too. For someone who supports lots of projects these REM lines can be a very important to remind you of what you were thinking.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.