When I try to filter an excel table with approx 36000 rows I takes a long while to calculate. When I look at the processor and memory usage, I notice the cpu usage is at 100% and the memory usage around 60%.

The document has multiple sheets and the sheet containing the table has lookups to other sheets.

I already tried this:

  1. ForceFullCalculation set to true
  2. Switch off multi-threaded recalculation

So I already know it's because Excel needs to recalculate all the lookups in the table.

What options are there to have a better working experience?

  • 1
    Go through your formulas: 1. make sure any array type formulas limit the references to only the cells with data, no full column references. 2. that you are avoiding volatile functions (OFFSET,INDIRECT,...). – Scott Craner Jan 19 '17 at 14:22
  • we can't debug this because Microsoft doesn't provide debug symbols for Office products. phone the Microsoft support for help. – magicandre1981 Jan 19 '17 at 16:42
  • Make sure everything actually needs to be recalculated. Often in huge spreadsheets, much of what's there doesn't change for each recalculation. – fixer1234 Jan 19 '17 at 17:37
  • @ScottCraner checked both, not sure if vlookup is a volatile function, but that's the function that consumes all my cpu – davejal Jan 19 '17 at 17:56
  • @fixer1234 Not sure what you imply with your comment, I already tried turning of recalculation – davejal Jan 19 '17 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.