Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Speeding Up Opening and Saving a Workbook

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.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

CAROL MARTIN    14 Oct 2016, 08:51
Good Morning!
I have a ginormous bank reconciliation that takes forever to open and would kill to have it looked at as to the cause. Anyway I can email it to you and ask you to assess?
Thank you in advance,
Carol Martin
Imran Bhatti    04 Oct 2015, 03:58
This paragraph from the tip solved my problem.
"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."

Best Regards
Imran Bhatti
Marshall    28 Nov 2013, 13:26
Excel spreadsheet takes along to save.
From Microsoft support
http://support.microsoft.com/kb/313937

This problem may occur if any one of the following conditions is true:

    When the computer is connected to one or more mapped network shares that are nonexistent or that are currently offline.
    When one or more mapped drives are persistent, and the drive is in a domain that is not trusted.
    When the mapped drive is located on a slow or a down-level computer. A down-level computer is when the operating system of the computer has an earlier version of Microsoft Windows than the computer that you are using).
    When a mapped drive is connected across a Wide Area Network (WAN).
    When a drive is an inaccessible removable drive.

Back to the top | Give Feedback
Collapse imageResolution
To resolve this issue, you must disconnect all network drives that are have any one of the conditions that are stated in the "Cause" section. To do this, follow these steps.

Note Because there are several versions of Microsoft Windows, the following steps may be different on your computer. If they are, see the product documentation to complete these steps.

    Right-click My Computer, and then click Disconnect Network Drive.
    In the Disconnect Network Drive dialog box, click the letter of the drive that you want to disconnect, and then click OK.
    Repeat steps 1 and 2 until all offline network drives are disconnected. If you are not sure about a particular drive, follow these steps:
        Click Start, and then click Run.
        In the Open box, type the following, and then press ENTER\\computer name\share name

        where computer name is the name of the server that is sharing the resource, and share name is the name of the shared resource that you want to use.

        If the resource is not available, you receive an error message that is similar to the following:
        The network name cannot be found.
    Alternatively, you can try to view the contents of the drive in the Windows Explorer.

Back to the top | Give Feedback
Collapse imageWorkaround
To work around this behavior, use one of the following methods.
Method 1: Do not use persistent connections
When you connect a mapped drive, click to clear the Reconnect at logon check box. By clearing this check box, the mapped drive will not be connected the next time that you log on to the computer. See the "More Information" section for more information about how to map a network drive.
Method 2: Use a user logon script
If you can, use a logon script to connect a user to the appropriate servers every time that the user logs on. Make sure that the script maps the drive in a non persistent state. If the drive is not available as the logon script runs, the drive is not mapped. This behavior prevents the issue.
Method 3: Use server mirroring
If the connection is over a Wide Area Network (WAN), consider implementing server mirroring. Server mirroring duplicates a distant server locally. Then, map your drive to the local, duplicate server. Doing this can reduce the wait time by connecting to a local server.
Method 4: Use a shortcut to the network location
Use a shortcut on the Microsoft Windows desktop or in My Network Places to connect to the network location that you want.

For more information about how to create a shortcut to a network location, click the following article number to view the article in the Microsoft Knowledge Base:
308416 How to create a shortcut to a network location in Windows XP
Back to the top | Give Feedback
Collapse imageMore information
Each location in a list is checked to make sure that it is available and that you have access permissions when you perform both the following procedures in Microsoft Office programs:

    You try to open or to save a file.
    You access the Look in or the Save In list.

If any mapped drive is in a state that is documented in the "Cause" section, it takes some time for the condition to be detected and resolved by the Office program. To avoid this behavior, you should disconnect that mapped drive. You can look for the availability of the drive at any time in Windows Explorer. If the drive is available, map the network drive again later.

To map a drive to a resource that is online again, follow these steps:

    Right-click My Computer, and then click Map Network Drive.
    In the Drive box, click the drive letter that you want to use for the network drive.
    In the Folder box, type the following\\computer name\share name

    where computer name is the name of the server that is sharing the resource, and share name is the name of the shared resource that you want to use.
    To make the mapped drive non persistent, click to clear the Reconnect at logon check box.
    Click Finish.

If you are trying to connect to resources on other networks, contact the network administrator. Network drives that no longer exist, that are no longer shared, or inaccessible removable drives should be permanently disconnected to avoid affecting the performance of Office products.
Back to the top | Give Feedback
Collapse imageReferences
For more information about how to map network resources, click the following article number to view the article in the Microsoft Knowledge Base:
145843 How to connect to a remote server using dial-up networking

For more information about problems with mapped network resources, click the following article number to view the article in the Microsoft Knowledge Base:
321126 The "Look In" and "Save As" boxes in common dialog boxes are slow
Anthony Cundari    12 Jan 2013, 11:45
I have many files that run macros. When I notice that Excel is running slower than I expect it to, I close the file and then close Excel. Open Excel and the file and it is back to executing normally.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.