Searching for a Solution to a Long-Standing Excel Problem

    I work as an Excel Developer and owe a lot to Ozgrid and stackoverflow, which have become my go-to sites over the years. Never in all that time have I read a post addressing my particular problem. I also just did a couple of searches with the keywords "performance" and "freeze" which got a few hits but nothing directly related to my situation. Specifically (and I'm not exaggerating), 4 to 7 times a day Excel freezes up on me. Sometimes, even thought I can't even select anything on a spreadsheet, I can save my work before closing out of Excel and restarting. Too many times, however, the freeze is accompanied by a message that "Excel has stopped responding". When that happens, the only way I've found to get out of it is to end the task in Task Manager, in which case everything is lost back to my last save.


    This has been going on for literally years. It happens at work with Excel 2010 running on a less-than powerful Lenovo PC, and at home with Excel 2016 running on an HP Envy 750-xt with an Intel Core [email protected] GHz processor and 32 GB of RAM. It's happened on at least my last 2 personal PC's and in other work situations. Since I can't find a trace of the same kind of problem here or on stackoverflow, where you'd figure at least 1 or 2 of the thousands of experienced Excel users would be grousing about it, I'm beginning to think it's either something I'm doing very wrong or I'm carrying some kind of Excel curse. Unfortunately, Microsoft tech support hasn't come up with anything they'll admit to.


    If anyone has any information on this or a similar problem and, even better a potential solution, I'll be eternally grateful if you'd reply. Thanks in advance.

    Re: Searching for a Solution to a Long-Standing Excel Problem


    Most important question, does that happen with a new sheet/file too? Create a new one, add a couple of random formulas and data.



    If the file resides on a server try using a local copy to see if the network factor has an impact. Changes in Offices Trust Center could be causing these issues then.



    Have you tried copying the contents into a new spreadsheet (unformatted)?
    I have a small Excel sheet where I keep track of numbers from a website and unless I clear them from their formatting (via a plain text editor) it will cripple Excel down to not responding for half a minute every time I click something.



    I understand that given the size of your sheet this is probably a painful solution but you could at least try it with a partial data set to see if that has any impact – or the other way round, copy portions of your data with their formatting into a new one and see which portion of your sheet creates those performance issues.

    Re: Searching for a Solution to a Long-Standing Excel Problem


    Also you can try:



    For example Excel can get confused about the actual range of your worksheets.
    Press CTRL + END and see where the selection ends up.
    Best case scenario it will be right underneath the the bottom-most right-most used cell. However, sometimes it will be way off of that with a lot of empty space to your actual data.
    Select all the empty cells, then right-click and select Delete (this is different from pressing the DEL key!). Save your file and test again.
    Do this for all the worksheets of your workbook to clear unnecessary “empty cell” bloat.



    I already mentioned that formatting can get funky. And with more and more formatting it can get more and more problematic. If you were conservative with formatting clearing and resetting it shouldn’t take too long and might help.
    * Definitely create a backup of the entire workbook beforehand.
    For example if you have a header row with a certain formatting select the entire row, then on the Home tab in the editing group click Clear > Clear Formats. Then reset the format.
    Actually, a good place to start might be the non-formatted cells! Probably most of your data entries are in the standard format. Select all of it and clear the formats to make sure it isn’t “made to look like standard format” ~ this will also clear data formatting (e.g. Time, Date) so this is something one has to keep in mind.



    Finally I would clear the Formula error checking.
    On the Formula tab there is a Formula auditing group. Click on Error Checking > Reset Ignored errors. In general, messy formulas and a lot of nested formulas create performance hits. Now, if there is some error in one of the initial formula it will be carried through all the nested ones too creating a lot of background work for Excel

    Re: Searching for a Solution to a Long-Standing Excel Problem


    I don't remember when I've seen such a comprehensive response, and I genuinely appreciate the time you spent and, most of all, some excellent suggestions. In fact, you nailed me on several things that could be contributing to the problem. I'll definitely implement your suggestions, starting with keeping a running log every time the issue reappears.


    As a small gesture toward returning the favor, Deb Dangleish on Contextures posted some code that gives you complete range information for every sheet in a workbook. I hope you enjoy it.


    Once again, thanks so much for your response.




    QUOTE=mchilapur;772758]Also you can try:



    For example Excel can get confused about the actual range of your worksheets.
    Press CTRL + END and see where the selection ends up.
    Best case scenario it will be right underneath the the bottom-most right-most used cell. However, sometimes it will be way off of that with a lot of empty space to your actual data.
    Select all the empty cells, then right-click and select Delete (this is different from pressing the DEL key!). Save your file and test again.
    Do this for all the worksheets of your workbook to clear unnecessary “empty cell” bloat.



    I already mentioned that formatting can get funky. And with more and more formatting it can get more and more problematic. If you were conservative with formatting clearing and resetting it shouldn’t take too long and might help.
    * Definitely create a backup of the entire workbook beforehand.
    For example if you have a header row with a certain formatting select the entire row, then on the Home tab in the editing group click Clear > Clear Formats. Then reset the format.
    Actually, a good place to start might be the non-formatted cells! Probably most of your data entries are in the standard format. Select all of it and clear the formats to make sure it isn’t “made to look like standard format” ~ this will also clear data formatting (e.g. Time, Date) so this is something one has to keep in mind.



    Finally I would clear the Formula error checking.
    On the Formula tab there is a Formula auditing group. Click on Error Checking > Reset Ignored errors. In general, messy formulas and a lot of nested formulas create performance hits. Now, if there is some error in one of the initial formula it will be carried through all the nested ones too creating a lot of background work for Excel[/QUOTE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login