How to improve performance of my Excel sheet ?

Annapurna, Aruna (Cognizant) 0 Reputation points
2026-02-07T06:14:19.76+00:00

I have an excel sheet with 8 to 10 Tabs of which only two tabs have <3000 rows. But almost 15-20 colummns use functions such as XLOOKUP, SUBTOTAL and IF. There are no other math functions being used. Number of threads is set at 10. But still it takes alsmot 3 minutes to calculate the sheet with EVERY EDIT.

How do we improve the performance.

Microsoft 365 and Office | Excel | For business | iOS
0 comments

3 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-02-07T06:14:30.1466667+00:00

    To improve the performance of your Excel sheet, consider the following strategies:

    1. Optimize Functions: Since you are using functions like XLOOKUP, SUBTOTAL, and IF, ensure that they are used efficiently. For example, if you can replace multiple XLOOKUP calls with a single lookup that retrieves multiple results, this can reduce calculation time.
    2. Use SUBTOTAL Wisely: The SUBTOTAL function is beneficial for filtered lists as it ignores hidden rows, which can help in reducing calculation time when dealing with large datasets.
    3. Limit Volatile Functions: If you are using functions that recalculate every time there is an edit (like INDIRECT, OFFSET, etc.), try to limit their use as they can significantly slow down performance.
    4. Reduce the Use of Array Formulas: If you are using array formulas, consider if they can be replaced with simpler formulas. Array formulas can lead to slower calculations, especially in larger datasets.
    5. Use INDEX and MATCH Instead of VLOOKUP: If applicable, using INDEX and MATCH can be faster than VLOOKUP, especially when dealing with large datasets.
    6. Check for Unused Cells: Sometimes, Excel calculates cells that are not in use. Make sure to clear any formatting or data from unused cells to reduce the calculation load.
    7. Structured Table References: If you frequently add data, consider using structured table references, which automatically adjust as data is added or removed, making your formulas more efficient.
    8. Use Manual Calculation Mode: If the sheet is very large and you are making multiple edits, consider switching to manual calculation mode. This way, you can control when the calculations occur, reducing the time spent recalculating after each edit.
    9. Optimize Memory Usage: Ensure your computer has enough RAM available for Excel to function efficiently. The 64-bit version of Excel can handle larger datasets better than the 32-bit version.

    By implementing these strategies, you should see an improvement in the performance of your Excel sheet.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Liora D 18,915 Reputation points Microsoft External Staff Moderator
    2026-02-07T07:58:19.8233333+00:00

    Dear @Annapurna, Aruna (Cognizant)

    Welcome to Microsoft Q&A. 

    Thank you for your question, based on your description, the file size itself isn’t unusually large (3,000 rows is normally fine). The slow performance usually comes from how Excel is forced to recalculate. In many workbooks, Excel becomes slow not because of the number of rows, but because formulas are referencing very wide ranges, or because the file is stored in a location that triggers continuous background processing.

    There are a few practical ways to improve performance:

    1. Narrow the lookup ranges instead of using entire columns

    If your XLOOKUP uses something like: 

    =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B)

    then Excel must scan all 1,048,576 rows of that column even if your real data is only 3,000 rows. Reducing the lookup range to the actual data (example: A2:A3000) usually improves speed immediately. This is one of the most common causes of slow recalculation.

    1. Check if the file is opened from OneDrive / SharePoint with AutoSave enabled

    When AutoSave is on, Excel has to do three things at the same time: calculate formulas, save each version in the cloud and sync changes back to OneDrive / SharePoint

    Even a well‑structured file can slow down significantly in this situation.

    A simple test is to save a local copy (File > Save As > This Device) and see if performance improves.

    If the local copy is fast, then the issue is related to cloud sync, not formulas.

    User's image

    1. Temporarily switch to Manual Calculation while working

    This does not change your formulas, it only tells Excel to wait until you press F9 before recalculating.

    Steps: Formulas tab > Calculation Options > Manual

    User's image

    This helps especially when you are editing many cells in a row and don’t need Excel to recalc every time.

    1. Look for formulas that depend on many other sheets

    Sometimes XLOOKUP or IF formulas refer to multiple tabs.

    When you edit any tab, Excel must recalculate all dependent tabs.

    If possible, adding a “helper column” (something pre‑calculated once) can reduce the dependency chain.

    1. If you can, let me know how the file is being used

    Are you opening the workbook in Excel for Windows, Mac, or iPad/iOS?

    (Excel on iPad has more limited calculation resources.)

    Is the file stored in OneDrive / SharePoint, or locally on your device?

    Could you share one example of your XLOOKUP formula?

    Do your formulas reference whole columns?

    These small details make a very big difference in how Excel behaves.

    Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.  

    I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.  

    Looking forward to hearing back from you with any updates or additional details. 

    Warm regards, 

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

    Was this answer helpful?

  3. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2026-02-08T00:10:27.2666667+00:00

    Hi,

    Speed of execution is also dependent on the complexity of calculations. Only on studying your file deeply can improvements be made.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.