How to handle massive workbooks that freeze and choke Excel? Let's share tactics

Hello Albert_Vile11a​,

One thing that helps me is diagnosing the bottleneck before changing the workbook.

I will start with Microsoft’s drill-down approach:

1. Time the workbook calculation, then narrow it down to the slow worksheet and formula blocks.
2. Check volatile functions such as TODAY, OFFSET and INDIRECT. Microsoft notes that these can increase recalculation time.
3. Check oversized ranges. Ctrl+End can reveal a used range that extends far beyond the actual data.
4. Check conditional formatting. Large numbers of conditional-format rules can slow calculation.
5. Check repeated calculations, large lookup ranges, defined names and external workbook links.

Microsoft docs:

Excel performance: Improving calculation performance
https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance

Excel performance: Tips for optimizing performance obstructions
https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions

The key question for me is: does the freeze happen during calculation, or when Excel is displaying or updating the sheet? That usually determines where I investigate first.