Slow Workbook Diagnostics Assistance Request
I am working on a long lived excel workbook that is sluggish at best. Slow to open, slow to change tabs, slow to enter or exit cell.
I've ruled out my normal suspects:
No volatile formulas - only uses SUM and handful of INDEX(MATCH())
No excessive Named ranges - fewer than 20, all print areas
No conditional formatting
No Objects, no charts, no hidden tabs
No excessive* look up to external workbooks
Checked true last cell using Ctrl+End on each tab. Could be tightened a bit but only 3 or 4 extra rows past the actual data.
- 5x Index(match()) to close external workbooks
- There are 125 other external linked formulas going to 20 external workbooks, mostly single cell reference, some SUM. Not ideal but wouldn't expect to see the reduction in performance that I'm seeing here.
- there were over 60k styles (which I had originally thought was the problem). Following removal I saw some performance increase but not nearly as much as I was expecting.
I used the INQUIRE add in to clean Excess cell formatting as well as the "Check Performance" optimization from the "Review" tab. Again, some performance increase but not as much as I was hoping for.
The only thing I can think of is the external links but this particular client has workbooks with far more formulas to far more external workbooks which aren't seeing nearly the same level of performance issues. Unfortunately, their existence is a non negotiable.
I don't know what else to do here; any advice is appreciated.
[link] [comments]
Want to read more?
Check out the full article on the original site