Excel issues, crashing, etc

Hi there,

I'm not sure what to do at this point, but at my wits end with excel! It crashes frequently (typically, once daily or more) where it forces me to end the program.

Along with that, it's SLOW. I work with large spreadsheets, and even the one I'm using now - it's about 18,000 rows with 45 columns - and that's one of the smaller ones. It's much worse on my 30,000 row spreadsheets. I can't pare any of it down as I need to review all data provided and make decisions based on the information. It's for work - they're all employee records. I've made sure to copy/paste special to remove all formulas. The only formatting is standard stuff like the top line is in bold, and there are colors used so I know what I've reviewed. But it's crazy slow. Every time I click I filter on any column, it takes, literally 2-3 minutes to give me the filter options, then another 1-3 minutes for it to actually filter what I select. Same for when I need to unfilter the column - about 1-3 minutes for that, as well. If I just had to do this a few times, not so bad, but I'm reviewing so much data that I need to review it in pieces so the filtering has got to be a lot faster.


I also get this message frequently...
Excel cannot complete this task with available resources. Choose less data or close other applications.

Sometimes this happens when I only have 1 or 2 sheets open, and then I can't even open a new worksheet. I have to save everything, close all of excel, and then go back into it, and it'll let me open what I need.

Anyone have any ideas?


Laptop:
Dell Latitude e6540
Intel Core i7-4800MQ CPU @ 2.70GHz
Installed memory: 16.0 GB
System type: 64-bit
Running Windows 7 Enterprise
Running Excel 2010

Thank you!

 

If you open Excel and work with small files, is it still slow? Obviously, you are dealing with a lot of data. Still, with 16GB of RAM and the CPU you have, I'd think Excel would perform better with very large files. You mentioned steps to keep unnecessary formulas out of the file and that should be helpful. If Excel is automatically recalculating all formulas any time you make a change in the data, I can see how it would be sluggish with such as large file. If any of your columns that are in number format but could actually be treated as text, reformatting the cells to text will signal Excel that it does not need to include those cells when recalculating. And, you could turn off automatic recalculation; this might be a huge help. A reference on how to do that: https://support.office.com/en-us/ar...recision-73a772d5-6224-407c-8029-60a47488b6aa If you do that, you just need to remember to manually recalculate when needed.

Does your version of Office have Access, the database? It would really be a better choice for what you're doing. Database works different from spreadsheet in the way it handles data. Database does not need to load all the records into memory when you open file; it retrieves data as needed. A variety of reports and queries can be used to generate reports and query results. And, each new record entered in immediately and automatically saved.

 

Thank you for replying so fast! I can't say that I really have any small files that I use to review/sort, etc. Usually with the smaller ones, I look at them as a whole.

Right now, I have nothing calculating on the spreadsheet, but there are 2 columns with dates on there. I can switch them to text to see if that helps. I talked to our IT team here, and they don't know what to tell me. The problem is with all our deadlines, I can't take this long to get one sheet done cause I can't get things updated in a timely manner for the rest of our team. I thought upgrading to 16GB RAM would help (I had 8 GB before), but there's very minimal improvement - almost unnoticeable.

With this 18K sheet, I do have 2 other sheets open that I transfer information on to, so I don't know if that could cause the 18K one to slow.


I have Access, but honestly don't know how to use it. I'll have to take a tutorial on it so I can try using that instead.

 

Run the Microsoft Office Diagnostics tool to ensure there is nothing wrong with Excel.
Try saving the workbook as an Excel Binary Workbook.

 

Hi all,

I apologize for my delayed response. I was waiting for my next round of using a large spreadsheet. I have one that's about 59K rows and 30 columns. It's even slower than the previous one I mentioned - like it's painful. I click a filter, and it's literally, at minimum 3 mins to pull up the filter. I click what I want, another 3 mins for it to debate it, and then a few minutes for it to actually show the data.

I do run on a shared drive network for the files, but I've started to try saving them to desktop in hopes to make it faster. That hasn't worked so far. I've just saved it as an Excel Binary WB, but that's not helping, either.

I disabled the symantec AV, so far, no improvement, but I'll keep an eye out. I didn't think about the AV trying to go through the info and making it go nutty.

When I just clicked on the filter, I pulled up Resource Monitor and show a spike in CPU and Network with some spiking under Disk. I'm honestly not too familiar with reading this info. Actually, Disk just went crazy with spiking after a few seconds. It seems to alternate between Network & Disk for crazy spiking until it finally lets me select from the filter.


I haven't run Microsoft Office Diagnostics tool. Where can I find that?

 

Here you go.
Start Menu > All Programs > Microsoft Office > Microsoft Office Tools > Microsoft Office Diagnostics.

 

That's normal for many Office tasks, the original/current file must be backed up in the page file before each edit is made, rinse and repeat for each edit; if Windows is set automatically manage the page file size, any increase in the size of the page file will mean a little more waiting.

Basically, it's down to the disk speed I/O, if you have an SSD, wait times would be small -> a notebook 5,400rpm drive would be very slow. Setting a fixed page file size of, say, 8GB, might slightly reduce some of the waits - but it could trigger an Application crash, should the program need more than is available.

 

Thanks, Eldon. I checked and don't have it. Maybe our work doesn't give us access to run Diagnostic. :/

satrow - so, if I'm understanding what you're saying, it's normal for excel to move this slow with large amounts of data like this? I know the laptop doesn't have an SSD. I am running 32 bit version of excel. Would making it 64 bit help at all? I don't know what is the disk speed I/O. Was that something I listed before? And is there any way to increase it so excel runs better?
I'd hate to set a fixed page file and have things crash. With what I do, it's pretty much dependent on me accessing a lot of stuff all at once.

 

Yes, it's normal and by design, using the x64 version of Excel if available might increase the internal computation speeds slightly but won't have any effect on the main bottleneck, disk I/O, due to the data being backed up by page file (it locks the program calling for the backup to page file until it's completed).

Disk I/O speed increases with rotational speed (5,400 is slower than 7.200) and expected usage for a specific drive (drives for Enterprise or Servers may have increased disk I/O manufactured in), it's not something that can be tweaked easily, other than reducing other running software/Services to allow Excel to have a little more headroom to work in.

8GB page file should be plenty for Excel x32; there's a quick script method to check current page file usage here, #34, wmic pagefile (I use the txt version), you can use this to test your current before, during and then after usage, it might be useful to you: http://www.sysnative.com/forums/win...rumentation-wmi-windows-10-8-1-8-7-vista.html