Excel Performance Problems

Fix Excel Workbook Taking Over 20 Minutes to Close

WPS EditorWPS Editor Oct 9, 2026 870 views

Question details

The user needs to resolve an issue where a complex, macro-enabled Excel workbook takes an excessively long time to exit.

How to Fix an Excel Workbook Taking Over 20 Minutes to Close
Product
Microsoft Excel
Device & OS
not provided
Scenario
Closing a macro-enabled workbook containing more than 20 worksheets and complex dependencies.
Observed behavior
The workbook becomes unresponsive and takes over 20 minutes to close, even when no changes are saved, whereas other workbooks close normally.
Before you start

Before applying VBA macro solutions, verify that you have saved a backup copy of your Excel workbook to prevent any accidental data loss or macro errors.

Solution 1Recommended

Disable Automatic Calculation on Close Using VBA

Use a VBA macro to automatically switch Excel's calculation mode to manual just before the workbook closes, bypassing the calculation freeze.

Excel sometimes recalculates complex formulas and volatile functions during the closing process. As workbooks grow in complexity with dozens of sheets, this can cause massive delays. By forcing Excel into manual calculation mode right before the workbook closes, you can immediately reduce the exit time.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.

2
Access the Workbook Module

In the Project Explorer pane on the left side, locate your active workbook and double-click on 'ThisWorkbook'.

3
Insert the BeforeClose Macro

Paste the following code into the code window: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.Calculation = xlCalculationManual End Sub

4
Save and Test

Save your file as a Macro-Enabled Workbook (.xlsm) and close the VBA editor. Try closing your workbook to see if the delay is eliminated.

Disable Automatic Calculation on Close Using VBA
Restore Calculation on Open: You may want to add a Workbook_Open event macro in the same module (Application.Calculation = xlCalculationAutomatic) to automatically re-enable calculations when you reopen the file next time.
Fast Spreadsheet Processing

Manage Complex Macro-Enabled Workbooks Smoothly with WPS

If your large Excel workbooks continue to freeze or close slowly, WPS Spreadsheet provides a highly optimized, lightweight environment for complex data. It fully supports VBA macros, complex dependencies, and ensures vastly improved loading and closing times.

  1. 1. Download WPS Office: Download and install WPS Office for free from the official website.
  2. 2. Open Your Complex Workbook: Launch WPS Spreadsheet and load your heavy, macro-enabled .xlsm workbook.
  3. 3. Manage VBA Macros: Navigate to the Developer tab to access the VBA editor, where your existing code will run seamlessly.
  4. 4. Experience Faster Processing: Save and close your files without the massive delays common in unoptimized spreadsheet software.
Optimized performance engine prevents freezing on large datasetsFully compatible with Microsoft Excel .xlsx and .xlsm formatsBuilt-in VBA macro support for automated tasksLightweight application footprint reduces system resource strain
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel calculate formulas when closing a file even without saving?

Excel may trigger recalculations on close if the workbook contains volatile functions (like TODAY, NOW, or OFFSET) or highly complex inter-sheet dependencies. Excel attempts to resolve the workbook's state before shutting down the application instance, which causes the massive delay.

Will setting calculation to manual break my formulas?

Setting the calculation to manual will not break your formulas, but it will prevent them from updating automatically when you change cell values. You will need to press F9 to calculate manually, or switch back to Automatic mode to see updated formula results.

How do I turn automatic calculation back on when opening the file?

You can add a macro to automate this. In the VBA editor, double-click 'ThisWorkbook' and add the following code: 'Private Sub Workbook_Open() Application.Calculation = xlCalculationAutomatic End Sub'. This ensures calculations run normally while you actively work.