Is there a function that will explain why Excel is using enormous amounts of memory?

Question

I have been using this spreadsheet for many years. Recently it has been taking longer and longer to do simple things like cut & paste. The pics attached has been running for over 5 mins. and the lastest one now also shows that Edges is also using a lot of memory.

What am I doing wrong? I run MS365 on 2 PCs: Surface 3 laptop and Dell OptiPlex desktop and both have the same issue, so I assume it's not the PCs.

Can anyone explain why this is happening? 

Answer 1

Excel does not have a built‑in function that explains exactly why a workbook is using a lot of memory. However, the behavior shown (slow cut & paste, high memory in Task Manager on multiple PCs) is consistent with known Excel performance and memory issues, especially in long‑lived workbooks.

Use these targeted checks and fixes:

1. Check for whole‑column or whole‑row references
   • Formulas like =VLOOKUP(A1,$D:$M,2,FALSE) or =COUNTIF(Sheet2!A:A,Sheet3!A1) reference over a million cells per column in modern Excel.
   • Replace them with exact ranges, for example =VLOOKUP(A1,$D$1:$M$5000,2,FALSE).
   • Also review Defined Names to ensure they don’t point to entire columns/rows.
2. Reduce heavy or complex formulas
   • Array formulas and functions such as LOOKUP, INDIRECT, OFFSET, INDEX, and MATCH can consume a lot of memory, especially when applied to large ranges.
   • Limit their ranges to only the rows/columns actually used.
   • If there are many formulas, consider replacing some with static values where data no longer changes.
3. Look for other workbook elements that grow over time
   • Large numbers of shapes, complex PivotTables, charts with many data points, and extensive conditional formatting can all contribute to “available resources” or “out of memory” behavior.
   • Remove unused shapes/objects and simplify PivotTables or charts where possible.
4. Test whether the issue is workbook‑specific
   • Create a new blank workbook and try similar cut & paste operations.
   • If the new workbook is fast, the issue is likely due to formulas/objects in the long‑used file rather than the PC.
5. Consider Excel bitness and memory limits
   • 32‑bit Excel is limited to 2 GB of virtual address space per instance (even on a 64‑bit OS). Large or complex workbooks can hit this limit and slow down or show “available resources” errors.
   • 64‑bit Excel removes this 2‑GB cap and can use all available RAM; for very large or complex workbooks, using 64‑bit Office can significantly improve stability and performance.
6. Check add‑ins and other applications
   • COM add‑ins share the same 2‑GB space with 32‑bit Excel and can increase memory pressure. Temporarily disable non‑essential add‑ins and retest.
   • Other applications (including browsers like Edge) also consume system memory. If the issue improves after a reboot or closing other apps, overall system memory pressure may be contributing.

These steps do not give a single “reason report,” but they target the most common causes of excessive memory use and slow performance in long‑standing Excel workbooks.

---

References:

• How to clean up an Excel workbook so that it uses less memory
• Memory usage in the 32-bit edition of Excel 2013 and 2016
• How to troubleshoot "available resources" errors in Excel
• Excel performance: Performance and limit improvements
• Tips for improving Excel's performance