Got an Excel file that seems to bring your system to it's knees? Try this

You open a specific spreadsheet and everything seems to slow to a crawl.  Opening the file, moving around within the spreadsheet, selecting cells, and pretty much everything else is unbelievably slow.  Your entire system will end up slowing to a crawl.  What doesn't make sense is that the spreadsheet itself does not have that much in it.  If you look at the file size it's also artifically large. 
In all cases I have found that the culprit is a value of some sort in a cell that is  w a y  down into the spreadsheet.  Like line number 1053498 or something ridiculous like that.  To find out if your spreadsheet has this issue, open the file and then type CTRL-END.  Does it put you way the hell down on line 50 million?  Yep, you need to fix it.  I have no idea why this happens but here is how to fix it.
I usually fix this by going to where you think the last cell in the worksheet actually should be, then type CTRL-SHIFT-END.  This selects all of the cells from that point down to where Excel currently thinks the "end" of the worksheet is.  Now you can delete these cells or "clear contents" also works I believe.  Save the sheet with a new file name.
To test to make sure that the fix worked, type CTRL-END and see if it takes you to the lower right of your working area.  If it takes you to the end of the world again (like line 59730219) then it's not fixed.  Try it again.
If you have succeeded then that spreadsheet will now hum along nicely and no longer bog your system to a crawl.  The file size will also probably fall drastically.  The last spreadsheet I did this to went from more than 5 MB to 100K. 


Posted via email from ken5m1th

No comments:

Post a Comment