Issue with MS365W

Question

Dear QA Team,

I am reporting an issue in Microsoft Excel (Microsoft 365) where the application becomes slow and sometimes produces incorrect calculation results when working with large workbooks.

The issue occurs when opening files with large datasets, multiple linked sheets, Power Query connections, and complex formulas. After enabling automatic calculation and refreshing data sources, Excel becomes unresponsive and some values appear outdated or inconsistent.

Steps to reproduce the issue:

1. Open a large Excel workbook
2. Enable automatic calculation
3. Refresh Power Query data
4. Edit values in linked sheets
5. Observe performance and results

Actual result: Excel becomes slow, freezes at times, and shows inconsistent calculation outputs. Expected result: Excel should run smoothly and provide accurate and consistent results.

This issue impacts productivity and may lead to incorrect reporting data.

Kindly investigate and provide a resolution.

Yours sincerely, [Your Name]

Answer 1

Hi, SME-EDU

This usually happens when Excel is hitting a mix of memory pressure, background query activity, and add-ins, so the workbook can look “finished” even though parts of it are still catching up.

Here are some suggestions you can try:

First, make sure it’s the 64-bit version of Excel. 64-bit is recommended for large files, complex calculations, and external data connections, and Power Query in 32-bit can be limited to about 1 GB in some cases.

In the query connection settings, it is suggested that you turn off background refresh, so the refresh finishes before you continue editing. That helps avoid the “old values still showing” feeling while the query is still running in the background.

In Power Query Editor, you should stop background preview downloads and use profiling on the first 1000 rows while editing, because those preview options affect performance on large data.

Open Excel once in Safe Mode. If it runs better there, an add-in is likely interfering, because Safe Mode bypasses Excel and COM add-ins.

It is suggested that helper queries do not all load back to separate sheets. You can try keeping only the final output on the grid when possible, because loading query results back to the worksheet can cause Excel to evaluate from scratch again.

Thank you for your patience in reading, I hope this information has been helpful to you. 

---

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. 

Answer 2

The described behavior matches known Excel performance limitations with heavy workloads and complex recalculation.

Key points from the scenario:

• Large workbook with many formulas, links, and Power Query connections
• Automatic calculation enabled
• Refreshing data and editing linked sheets
• Slow performance, freezing, and inconsistent/“outdated” values

Relevant behaviors and constraints:

1. Excel recalculation modes
   • Excel has three modes: Automatic, Automatic Except Data Tables, and Manual.
   • In Automatic mode, Excel recalculates all open workbooks after every change, which can be very slow for large models.
   • For very large workbooks, guidance is to limit automatic recalculation and use Manual or Automatic Except Data Tables so recalculation happens only when explicitly requested.
2. Data tables and multi-threading
   • Excel data tables (What-If Analysis > Data Table) always recalculate single-threaded and do not use multi-threaded calculation.
   • Large data tables that depend on complex formulas can take a long time to recalculate and are handled asynchronously; they may finish after the rest of the workbook, which can appear as temporarily inconsistent or “out-of-sync” values.
   • To avoid repeated heavy recalculation of data tables while editing, the recommended setting is Automatic Except Data Tables and then manually recalculate tables (for example, with F9) when needed.
3. Calculation performance characteristics
   • Poor calculation speed directly impacts productivity and user error rates.
   • As calculation time increases beyond 1–10 seconds, users typically need to switch to manual calculation to maintain usability.
   • The second and subsequent recalculations are often faster because Excel recalculates only changed cells and their dependents and reuses the previous calculation chain.
4. Multi-session / shared environments (if applicable)
   • In multi-session environments (for example, Windows Server RDS or Azure Virtual Desktop), Microsoft 365 Apps limit Excel to two threads for multithreaded recalculation and worker thread pools to preserve resources across sessions.
   • Heavy Excel workloads that require intensive calculation or data analysis can therefore run slowly in these environments.

Recommended mitigation steps:

1. Adjust calculation mode
   • Set calculation to Manual or Automatic Except Data Tables while editing and refreshing data.
   • Recalculate explicitly (F9 or equivalent) at logical checkpoints instead of after every edit.
2. Control data table recalculation
   • If the workbook uses data tables, set calculation to Automatic Except Data Tables so that tables do not recalculate on every change.
   • Recalculate data tables only when final results are needed.
3. Review workbook design
   • Reduce unnecessary complexity where possible (for example, avoid overly large data tables or redundant formulas) to improve recalculation speed and reduce the chance of apparent inconsistencies during partial recalculation.
4. Environment considerations
   • If running in a multi-session environment (for example, Azure Virtual Desktop or Remote Desktop on Windows Server), be aware of the two-thread limit for Excel’s multithreaded recalculation, which can make heavy workloads slower than on a dedicated desktop.

These changes help Excel remain responsive and ensure that final results are consistent and fully recalculated when requested.

---

References:

• Heavy Excel workloads run slowly in multi-session environments
• Excel performance: Improving calculation performance
• Excel Recalculation