Forum Discussion
How to handle massive workbooks that freeze and choke Excel? Let's share tactics
1. Download my free Name Manager add-in to really see what is going on behind the scenes regarding range names. https://jkp-ads.com/excel-name-manager.aspx
Names I always delete:
- Error names (#REF!)
- Names with external references
- Names containing ={ (these are mostly names holding constants of external add-ins like FactSet)
Next I check for duplicated local vs global names. The local (sheet-level) copies are often caused by copying worksheets and shouldn't be there unless on purpose
2. Install my free Rule Manager to clean up duplicated CF rules:
https://jkp-ads.com/excel-rule-manager.aspx
3. Open the Selection pane (Home, Find & Select, Selection pane). It allows you to see if there are many (hidden) objects on your sheet(s)
4. I offer add-ins to time calculations and to clean up files, but those aren't free so I won't mention them here.
There is a problem with this forum: I wrote separate thank-you messages to both participants. These messages were held for review and were not published. What do I need to say to get them published? Moderator: would you be so kind as to thank both of them for their replies on my behalf?
Hi Jan. It is an absolute honor to see you here and be able to exchange a few words with someone who has literally written the book on Excel inner workings and add ins.
I have been taking a close look at your suggestions regarding the Name Manager and the Rule Manager. It is awesome to see how cleanly you tackle that hidden mess of defined names and conditional formatting rules that usually destroy a spreadsheet from the inside.
The eternal dilemma we face in the office is corporate security. Policies make installing external tools an uphill battle most of the time, even when they come from trusted sources like yours. But for a quick autopsy on a dead workbook, they are pure gold. How do you usually handle environments where local IT completely locks down any chance of installing custom add ins?
