Excelling at failing Excel

Discussion in 'General Software' started by novicegeek, Aug 9, 2019.

  1. novicegeek

    novicegeek Member

    Messages:
    382
    First off, I’m an Excel idiot. I use Word exclusively, and only use Excel when it’s someone else’s document and I don’t have a choice. Now would be one of those times. I do want to learn, but right now, I’m an idiot.

    At work, there’s an Excel spreadsheet that freezes when trying to sort alphabetically. It hogs the memory so much that we can’t do anything with it. Granted, the computers at work are crap (Intel Pentium G3220 with 4 GB of RAM), but when I put this spreadsheet on my computer at home, it was hogging 7 GBs of RAM, and taking a few minutes to complete the task.

    The file size is 1.47 MB, not big. To my knowledge, and according to the author, there are no formulas, macros, or calculations on it. It’s really just a list of names divided by work area (should have used Word, right). Other spreadsheets appear to work fine on computers at work, and my computer at home.

    I was told the spreadsheet was created on 2010; the computers at work are running 2019. I’m running 365. There are three separate workbooks (pages)within the spreadsheet. We did recently change from Windows 7 Enterprise to Windows 10, if that makes any difference at all.

    Thus far, I have cleared the rules from the entire spreadsheet (under conditional formatting) and unmerged all the cells, because there was a message about that when I tried to sort. I event tried to save it in an .xls format. Nothing has helped.

    Would some kind soul step forward with a possible solution to this memory hogging spreadsheet? I would greatly appreciate it, as would many other people. Thanks.
     
  2. Trizoy

    Trizoy VIP Member

    Messages:
    4,704
    Sometimes spreadsheets just stop working.. I have found if you save in newer formats, then roll back to an older format (if required) will allow to work. With <2mb of size, and nor formulas it may have vba code running in the background.
    For example are there buttons that you can click? Or does it refresh data automatically when you open it?

    Also, if it truly has only data, copy it out into a new spreadsheet.

    Let me know, I work with Excel a lot.
     
  3. novicegeek

    novicegeek Member

    Messages:
    382
    Alright Trizoy, getting a little ahead of me here... lol, as I said, Excel idiot. All I know is that it has buttons in the ribbon that I can click. I just opens when I click it - not sure about the refreshing data part. What I do know is I went to the end of the spreadsheet (CTRL + End) and tried to delete the 12 columns that were excess, and it stopped responding (used about 976 MB of memory when I finally killed it via the task manager). I'll show you a snip, though it probably won't offer much info than what I already said.

    Exactly how to I find if I have a vba code running in the background? I've looked up a little on this subject, but still quite ignorant.

    By the way, I appreciate your response. I really wish I knew Excel better, but in the meantime, I really appreciate your expertise in this area. It may save a lot of frustration.

    Thanks.
     

    Attached Files:

  4. beers

    beers Moderator Staff Member

    Messages:
    8,381
    I'd do this, don't worry about removing crap from the previous one just copy the data to a new sheet.
     
    Darren likes this.
  5. novicegeek

    novicegeek Member

    Messages:
    382
    Well, I tried to copy and paste, all options, but it's color coded, and it either didn't copy the colors, or (with the first paste option), locked up and I had to utilize the task manager to shut both down. But it was worth a try. Thanks.
     
  6. novicegeek

    novicegeek Member

    Messages:
    382
    Update: the darn thing works perfectly in safe mode; however, there are no add-ins active, so I have no clue as to why it's behaving this way.
     
  7. johnb35

    johnb35 Administrator Staff Member

    Messages:
    41,839
    You most likely have something going on in your computer that is causing it, can't say what though. You can send the file to me and I can try taking a look at it if you want.
     
  8. Darren

    Darren Moderator Staff Member

    Messages:
    12,130
    If this is a company file I would not do that.
     
  9. novicegeek

    novicegeek Member

    Messages:
    382
    I thought it might be our low end computers at work, but I think this has something to do with the document itself. It's been tried on two different computers at work, and on my computer at home (with a better CPU and much more RAM). It's still bogging down. At home, I opened it in safe mode, hit F5 for the "Go to..." dialog box, and selected Special..., and then clicked the Objects button, and then walked off to let my computer chew on it for a while. About 1/2 hour later, I finally shut it down via the task manager, as it was still not responding when I tried to do anything with it.

    And John, I appreciate your generous offer. Not that the spreadsheet has any classified info, and I know you're a standup guy, I do work for an agency might get antsy about things like that. If it were a personal file, I wouldn't have any hesitation.

    And Darren, appreciate the input. In my zeal for answers, I would have even thought about it, for the aforementioned reasons.

    Thanks again, guys!
     
  10. AlienMenace

    AlienMenace Well-Known Member

    Messages:
    1,052
    I am no expert, but maybe if you download Excel to your cell phone or if you have a tablet and try it in there to see if it works. Or even maybe download LibreOffice and use the Calc program to see if it locks it up or runs. Just a thought.
     
    Darren likes this.
  11. Darren

    Darren Moderator Staff Member

    Messages:
    12,130
    I took a few too many business classes to not point that out. I trust both of you would have best intentions but more just for your own protection I wouldn't mess with it.

    Calc is a good idea actually. I'd make sure to make a duplicate first and open that so it doesn't mess with anything on the original.
     
  12. novicegeek

    novicegeek Member

    Messages:
    382
    Okay. That was interesting. I opened the spreadsheet though OpenOffice and it says that the file contains links to other files. I could find no links when I opened it in Excel. By the way, should I update the links or no?

    Thanks.
     
  13. novicegeek

    novicegeek Member

    Messages:
    382
    Well, I selected no on the links, and the results were much worse. I never got to the sort button. I just highlighted a column, and it started not responding. Then it kept highlighting on its own. at this writing, I'm up to cell 808. I'm going to kill it through the task manager. I've got to get to work.
     
  14. Darren

    Darren Moderator Staff Member

    Messages:
    12,130
    Obviously hind sight is 20/20 but I've seen more than a few companies that put WAY too much content into one Excel file, especially business critical information such as customer data. Then when it all grenades they're screwed.

    Don't put all your eggs in one basket. Or.... an Excel sheet. :)

    Wish I had more suggestions. I'd just go about how to rebuild it effectively into multiple documents, even if it is manually.
     
  15. novicegeek

    novicegeek Member

    Messages:
    382
    Well, I appreciate your suggestions. But the confusing part is, it's not a big spreadsheet; 1.47 MB (three printed pages, or worksheets, in the whole file). I think it has something to do with how the file developed, and what was done in it, but I'm not sure.

    But, thank you. I'll keep working on it.
     
  16. AlienMenace

    AlienMenace Well-Known Member

    Messages:
    1,052
    Well you tried it without updating the links, did you try updating the links?
     
  17. novicegeek

    novicegeek Member

    Messages:
    382
    Well, as you suggested, I tried it without updating the links. It seems without the links (because I tried this again) did better. The first time out of the gate without the links, I was trying to highlight an area, and because the program was running so slow, it highlighted much more than I thought it would. So, without the links (the second try) I could sort, but I did get the "not responding" message on and off. With the links, I had to shut the program down via the task manager because it froze up on me.

    I hope this helps.

    Thanks again.
     
  18. AlienMenace

    AlienMenace Well-Known Member

    Messages:
    1,052
    I think Johnb35 might be right about something. It sounds a little squirrelly. Have you done a virus/malware scan on the computer and the "File (spreadsheet)? Just saying.
     
  19. novicegeek

    novicegeek Member

    Messages:
    382
    I just scanned the file. It comes back clean. This spreadsheet has been on three different computers, two at work, and mine at home. Being government property, the work computers are locked down pretty tight, and I don't have any viruses on mine. And in case you were wondering, we do have an IT guy where I work; however, he's too busy to work on this issue. Besides, I get the feeling he's about as well-versed with Excel as I am. I'm determined to fix this issue, but as I'm somewhat ignorant of Excel and its way (though I'm quickly learning), I appreciate all of your input. Thanks again.
     
  20. johnb35

    johnb35 Administrator Staff Member

    Messages:
    41,839
    About the only thing I can think of to do would be to copy parts to a new sheet until it starts crashing again. Then you'll know what's causing it.
     
    AlienMenace likes this.

Share This Page