Excel Performance Problems

Fix Excel XLSM File Freezes or Not Responding When Saving

Bushra Parveen Oct 10, 2026 868 views

Question details

The user needs to resolve an issue where an Excel macro-enabled workbook (.xlsm) takes an excessively long time to save and becomes unresponsive.

How to Fix Excel XLSM File Freezes or Not Responding When Saving

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to save a complex, multi-sheet workbook that consolidates data using hundreds of thousands of INDIRECT formulas.
Observed behavior: Excel lags significantly, freezes, and displays a 'Not Responding' message during the saving process.

Before you start

Before attempting to optimize formulas or run new macros, create a duplicate copy of your .xlsm workbook to prevent accidental data loss during the troubleshooting process.

Solution 1Recommended

Replace Volatile INDIRECT Formulas with Efficient Alternatives

Replacing the volatile INDIRECT function with direct references or non-volatile functions prevents Excel from recalculating the entire workbook during saves.

The INDIRECT function is volatile, meaning Excel recalculates it every time any change is made anywhere in the workbook. Having hundreds of thousands of these formulas will inevitably cause severe performance bottlenecks and freezing during saves.

Locate Volatile Formulas

Open your workbook and identify the summary sheet or columns heavily relying on the INDIRECT function.

Switch to INDEX and MATCH

Select the affected cells. In the formula bar, replace the INDIRECT formula logic with the INDEX and MATCH functions, or use direct sheet references (e.g., ='Sheet1'!A1) which only recalculate when source data changes.

Use Power Query for Consolidation

Instead of using live formulas to pull data from 200+ sheets, go to the Data tab, select 'Get Data', and use Power Query to append and consolidate the sheets efficiently without volatile overhead.

Replace Volatile INDIRECT Formulas with Efficient Alternatives

Performance Boost: Replacing INDIRECT with INDEX/MATCH or Power Query can reduce file save times from minutes to just a few seconds.

Solution 2

Consolidate Data Using a VBA Macro

Use a VBA macro to extract data on demand rather than relying on live formulas that constantly recalculate in the background.

Solution 3

Change Calculation Options to Manual

Temporarily disable automatic calculation to stop the workbook from freezing while you edit or attempt to remove the problematic formulas.

Free Microsoft Office alternative

Experience Faster Spreadsheet Performance with WPS Office

If Microsoft Excel continues to freeze or lag when handling large datasets and complex formulas, try WPS Office. It is a highly optimized, lightweight, and free alternative that effortlessly handles massive workbooks, complex formulas, and VBA macros without draining system resources.

1. Download and Install: Download WPS Office from the official website and follow the easy installation instructions.
2. Open Your XLSM File: Launch WPS Spreadsheet, go to Menu > Open, and select your macro-enabled workbook.
3. Enjoy Smooth Performance: Edit, calculate, and save your complex spreadsheets with optimized speed and zero freezing.

Lightweight architecture ensures faster opening, saving, and calculation timesFully compatible with Microsoft Excel formats, including .xlsx, .xlsm, and .csvSupports advanced formulas, massive datasets, and VBA macrosFree to download and use with a highly familiar user interface

Frequently Asked Questions

Why does the INDIRECT function cause Excel to freeze?

INDIRECT is known as a 'volatile' function. This means Excel forces it to recalculate every time any change is made to the workbook, even if the change is unrelated to the formula. In large quantities, this overwhelms the CPU and causes Excel to freeze.

Can I still use macros if I remove INDIRECT formulas?

Yes. VBA macros do not rely on INDIRECT formulas to function. In fact, replacing heavy sheet formulas with a well-written macro often improves the performance of an .xlsm file significantly.

What are the best non-volatile alternatives to INDIRECT?

Using combinations of INDEX and MATCH, the CHOOSE function, or direct structured references are highly recommended. These functions are non-volatile and only recalculate when their specific source data is altered.

Will turning off automatic calculation fix the saving issue?

Setting the calculation to 'Manual' (Formulas > Calculation Options > Manual) will stop Excel from freezing while you are actively typing or editing. However, the file may still lag when you eventually save, as Excel often forces a recalculation upon saving.

Excel & Data Problems