Excel 2016 very slow (Calculating 0%, 4 processors) but same file is OK on other computers

[sshSSH]

I am having a problem with several large files being slow in Excel 2016 (windows 10). It can take an hour to open a file, it says at the bottom right: calculating 0% (4 processors), and then once it's open it takes forever to do something simple in the file like filter. I know there are things you can do like switching to manual calculation, pasting values instead of formulas (this helps somewhat but it's still very slow), or simplifying the formulas, but the same files are not a problem on other computers with similar or worse specs so I don't think I should have to do those things. I have tried to open in safe mode to diagnose but since it's a group license I wasn't able to. I was able to open with selective startup and still had the same problem. Any ideas?

 

[Zot]

Hard way.
Use other computer that can open fast
Create a new workbook. Manually copy each sheet to new workbook.

I think the file has something corrupted in internal structure.

 

[ravi2628]

Just Delete and unformat the Empty Cells(Rows & Columns) after your data and then try if it is not working please close the Excel files and clear the prefetch data then it will work correctly.

 

[Zot]

There are several reasons. Like in @ravi2628 case, many like to select whole column or row just format small number or rows. This causes the saved file huge but contents is not really much ?

 

[ravi2628]

There are several reasons. Like in @ravi2628 case, many like to select whole column or row just format small number or rows. This causes the saved file huge but contents is not really much ?

Hi Zot,

But it worked for me because I was working with a Huge data file and calculations. so I suggested the same

Regards,
Ravi

 

[sshSSH]

Thanks Zot and Rivi2628 so much for your replies! I tried first just doing Rivi2628's suggestion of the clearing the areas outside my data (there were a couple of shaded columns) and deleting the prefetch. That seemed an easier thing to try first since one of my files with this problem has lookups between spreadsheets that might be time consuming to redo (if i copy each sheet into a new file). Doing that helped to make the file open quickly, but then it totally bogged down when I tried to filter the data. So I moved on to trying Zot's suggestion... I copied just the data into a brand new file on the 'good' computer, saved it there and brought it back to the 'problem' computer. I even abandoned my pivot table and made a new one in the new file. It opened fine and did work quickly for a while, doing some filtering and graphing, so I was sort of celebrating things working when... it bogged down (calculating 0%, 4 processors, taking an hour) while I was trying to filter something, so I guess it's not quite fixed unfortunately (but better than it was).

 

[Zot]

My observation is that even if the CPU has 4 ores, Excel is only using one of them. If program stuck in loop, the CPU load is only 25%. I think it is because the Excel in not multitasking apps.

What about the RAM between those computers. If the file too big and not enough RAM, apps will use HDD as temp memory. Check if one is using SSD or HDD. Maybe that's also make the difference.

 

[sshSSH]

The file is about 70,000 Kb in size, though I have a much bigger one with the same issue too. Both computers have 8GB Installed RAM. The 'bad' computer has an Intel i7-7600U CPU@ 2.8GHz while the 'good' computer has an Intel i7-1065G7CPU@1.3GHz.

Sorry I do not know how to check if one is using SSD or HDD (as temp memory). Zot, can you explain how to check that?
I can watch the task manager's processes tab to see what % of the CPU and memory Excel uses when it does a slow task. I just asked Excel to do a complicated data filter and during the 5 minutes it took to do it, it seemed to use about 30-35% of the the CPU and about 570 MB of the memory. This leaves (after what other processes are using) 60% of the CPU and 40% of the total memory unused.

 

[Zot]

The temporary memory is automatically created and managed by Windows and usually is C:\ drive. We just don't bother about it.

The RAM looks like more than enough but why keep everything in such big file ?. Why not make it by month instead of year foe example

I wonder what is the difference between the 10th and the 7th generation CPU