Excel Performance Problems

How to Improve Slow Excel Workbook and Macro Performance

Maira Mehtab Sep 22, 2026 873 views

Question details

The user wants to improve the performance of an Excel workbook that runs slowly during both macro execution and standard operations like editing or copying formulas.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Working with complex workbooks containing large data ranges, numerous formulas, or macros.
Observed behavior: Significant delays occur during basic workbook operations, such as entering dates or deleting formulas, and macro execution takes longer than expected.

Before you start

Before making changes to your formulas or VBA code, save a backup copy of your workbook to prevent any accidental data loss during the optimization process.

Solution 1Recommended

Set Calculation Mode to Manual During Macros

Prevent the spreadsheet application from recalculating the entire workbook during macro execution to drastically reduce processing time.

Disable Automatic Calculation

In your VBA macro code, add 'Application.Calculation = xlCalculationManual' at the very beginning of the script.

Disable Screen Updating

Right below the calculation line, add 'Application.ScreenUpdating = False' to stop the screen from flickering and refreshing while the macro runs.

Restore Settings

At the end of your macro code, before 'End Sub', restore automatic calculation by adding 'Application.Calculation = xlCalculationAutomatic' and 'Application.ScreenUpdating = True'.

Performance Boost: Disabling these settings temporarily allows the macro to run in the background without wasting resources on visual updates or intermediate formula calculations.

Solution 2

Minimize the Use of Volatile Functions

Identify and replace volatile functions that trigger recalculations on every single workbook change.

Optimize with WPS Office

Manage Large Workbooks and Macros Efficiently in WPS Spreadsheet

WPS Spreadsheet offers a lightweight, highly compatible environment for handling complex workbooks. You can easily adjust calculation settings to improve performance when working with large data sets or executing macros natively.

1. Open Workbook in WPS: Launch WPS Spreadsheet and open your large .xlsx or .xlsm file.
2. Access Calculation Options: Navigate to the 'Formulas' tab on the top ribbon.
3. Enable Manual Calculation: Click on 'Calculation Options' and select 'Manual' to stop automatic recalculations while you edit data or run macros.
4. Execute Operations Smoothly: Run your macros or perform bulk data edits without lag. You can press F9 at any time to calculate the workbook manually.
5. Restore Automatic Mode: Once your intensive tasks are finished, switch the 'Calculation Options' back to 'Automatic'.

Lightweight engine for faster file opening and processingFully compatible with Microsoft Excel (.xlsx and .xlsm) formatsRobust support for VBA macros and advanced automationFree and easy-to-use alternative with a familiar interface

microsoft office alternative - wps office

Frequently Asked Questions

What is a volatile function in a spreadsheet?

A volatile function is a formula that recalculates every time any change is made to the workbook, not just when its direct precedent cells change. Examples include TODAY(), NOW(), OFFSET(), and INDIRECT(). Excessive use of these functions heavily slows down workbook performance.

Why does my workbook lag when copying and pasting?

Lag during copying and pasting is often caused by the application recalculating complex formulas, updating conditional formatting rules across massive ranges, or processing thousands of hidden objects. Switching the calculation mode to Manual before pasting can resolve this issue.

How do I find out what is slowing down my workbook?

You can identify performance bottlenecks by reviewing the overall file size, checking for excessive or overlapping conditional formatting, looking for volatile functions, and testing if a macro runs faster with 'ScreenUpdating' disabled.

Does saving the workbook as a binary file (.xlsb) improve performance?

Yes, saving a large file in the Binary Workbook (.xlsb) format can drastically reduce file size and improve opening and saving speeds, although it won't fundamentally fix inefficient formulas or poorly written macros.

Excel & Data Problems