Excel is using 100% of CPU. Where might be the trouble?

[Habanero Time]

This morning, Excel is pegging out at 100% of CPU usage. Things as simple as adding a row are taking more than a minute to execute.

One other strange thing is every time I save the workbook as XLSX I'm warned that I will lose the macros. From what I see, there are no macros. (Yes. This spreadsheet was sent to me to work on. Someone else built it.)

Might these issues be related?

Using Excel 2011 for Mac
Mac OS X 10.6.8

[Richard Buttrey]

Check the last used cell (End Home) on each sheet. You may find a formula or value has been copied down to row 1048576.

Regards

[Habanero Time]

I'm home now and tried the file on my PC. The CPU isn't pegging out at 100% but when it opens, there are numerous windows that say that the "File Was Able To Open By Repairing Or Removing Unreadable Content."

Still, it's taking a painfully long time to do simple Copy/Paste

If I even scroll a little, the screen freezes and on the bottom of the window it's showing "Calculating: _%" and slowly counts up to 100%

[Richard Buttrey]

Have you checked for the last used cell on each sheet as I suggested?

[Habanero Time]

Yes. Cell AD244 is the last cell and it's what would be expected.

I checked another page.

The final cell is AA32624 but the final visible cell is U13. I can't see that there's a formula are anything going on in AA32624

[Richard Buttrey]

Select rows 14 to 32264 and delete them. Save and close the workbook and then re-open.

Check all pages in a similar fashion.

Is that any better?

[Habanero Time]

I just deleted those rows and I could tell because whomever created this document had no grid lines until row 32264.
Gridlines now start at row 14 and the sheet is still showing AA32264 as the last cell.

[Habanero Time]

A second attempt to delete the rows was successful.

I noticed one other thing. Above the columns is something I haven't seen before. On other spreadsheets I've seen this along the side by the rows for subtotals but never seen it above the columns before. Here is a screenshot:

http://dragoncookies.com/wp-content/...creenExcel.png

[OnErrorGoto0]

That is the Grouping symbol - you can group rows or columns or both.
If there is no code and no modules in the workbook, you should not get an error about macros being removed when saving as xlsx. Are you sure there is no code behind the worksheets? It might well explain the slowness?

[jeffreybrown]

One other thought, have you checked the size of the file lately? Sometimes files can grow into hugh unwieldy files.

To reset the last cell I have always found this useful

[Richard Buttrey]

After you deleted the rows and then saved the file you should find that the last used cell is on row 13. If it's still showing row 32264 then you haven't successfully deleted the rows.

Select all the rows, by that I mean select all the row numbers 14 to 32264 and then delete. Immediately save the file and then check for the last used cell.

Regards

[Habanero Time]

Thanks for hanging in here with me. I've deleted the rows completely.

On my PC, when I open this file, I says that it Repaired or Recovered ... in order to open.
I click on the link provides afterward and it says:

Removed records: Protection from /xl/ worksheets/sheet3. xlm part

[Richard Buttrey]

So has that fixed the problem?

When you do an End Home on any sheet to find the last used cell, are you now seeing something that seems sensible rather than a row in the 100's of 1000's?

[Habanero Time]

The problem continues.

All of the final cells do make sense.
File size of 307KB

[OnErrorGoto0]

Are you sure there is no code behind the worksheets?

[Habanero Time]

I've looked for code and don't see any. But there's the thing that happens on the PC:
"Excel was able to open the file by repairing or removing the unreadable content." And then it gives this log:

Removed records: Protection from /xl/ worksheets/sheet3. xlm part


On the Mac, however, the file opens but it just drags

[OnErrorGoto0]

It appears that Excel thinks there is code somewhere, based on your first post. Would it be possible for you to post a copy of the file with no data in it?

[Habanero Time]

I'm going to post the file. I removed about 3000 rows of data and am saving under a different name. It's going to take at least 10 minutes before the spreadsheet closes. That's one thing I noticed: the sheet doesn't crash or hang indefinitely. It slowly does whatever it needs to do.

[Habanero Time]

VendorSheet-Repair.xlsx Here is the file

[jeffreybrown]

I can't even get the spreadsheet to open. Actually I should say after about 5 minutes I lost hope.

Bascially the spreadsheet open and stuck processing at 19% and was going nowhere fast.

What is in this spreadsheet? With the processor working this hard my guess would be some labor intensive formulas.

At a certain point I would have to ask myself if it is time to start all over from scratch.

Sorry, but again the spreadsheet was not cooperating for me but maybe somebody else will have some luck...

[Habanero Time]

jeffrybrown
Thanks for trying. The spreadsheet has a lot of SUMIFS, and LOOKUP functions on one of the pages. It also has a lot of conditional formatting.
So, starting from scratch is a daunting notion.

It sounds like you didn't get that issue of "Excel was able to open after repairing or replacing ..."

[bryanbak3]

Try to copy and paste values of all worksheets if you don't need the formulas anymore. Seeing as it says it was previously saved as an xlsm that means it had macros in it most likely. You can see if there is any formulas by clicking on the formulas tab and selecting the show formulas button.

[bryanbak3]

Also if it is referencing other spreadsheets for its lookup functions, you need to probably have those sheets open as well or it will cause a horrible slow down and it sounds like this is the case seeing as jeff couldn't even open it.

[jeffreybrown]

Right off the bat when I open the file I get: "Excel found unreadable content in 'VendorSheet-Repair.xlsx'. Do you want to recover...

I have to go clean up the lliving room which my grandson destroyed so I'll try it again

[Richard Buttrey]

I had great difficulties opening the file and it only completed opening after the message about only able to open after repairing or replacing ...
Even then it was painfully slow calculating, taking about 6 minutes to complete. And every time I came back to the Excel Window after going off to a web browser window to do something else, it started calculating from scratch again.

I noticed that the last cell on the last (apparently empty sheet apart from row 1) was down around row 1400 which may or may not be relevant, but I never got the opportunity to delete the rows to check it out.


Are the conditional formats you mention all the same or lots of different ones. You also mentioned lots of Lookup formulas - which by their nature are very heavy on processing. What ranges are they searching. Have you tried temporarily limiting the lookup range to a few rows to see if that makes a difference?

Sadly I suspect you may need to consider a rebuild and check each stage as you go along.

Regards

Finally after a couple of these ocurrencies, with the processors working overtime and the fan whirring like a demented helicopter, it crashed my laptop completely.

As Bryanbak3

[jeffreybrown]

Sadly I faced the same problems as Richard. The living room wasn't as dirty as I thought and after returning in about 15 minutes the sheet was opened. I opened up the VBE so I could reset the last cell, and...another 5 - 10 minute wait. Everytime I touch something in the sheet the processor starts all over and it is just a perpetual loop.

Time to rebuild seems the only viable option at this point.

[Habanero Time]

Good lord! Crashed the laptop?

Thanks to everyone with this. Looks like I'm going to go ahead and start over--slowly--in case this starts up again.

Yes. This spreadsheet is loaded with LOOKUPS like this:
IF(ISERROR(LOOKUP(2,1/((Sheet0!H:H=$K$28)*(Sheet0!I:I=E32)),Sheet0!L:L)),0,LOOKUP(2,1/((Sheet0!H:H=$K$28)*(Sheet0!I:I=E32)),Sheet0!L:L))

[jeffreybrown]

IF(ISERROR(LOOKUP(2,1/((Sheet0!H:H=$K$28)*(Sheet0!I:I=E32)),Sheet0!L:L)),0,LOOKUP(2,1/((Sheet0!H:H=$K$28)*(Sheet0!I:I=E32)),Sheet0!L:L))

My guess is when you start rebuilding, post a small sample workbook with this formula and somebody might be able to optimize the efficiency of what you are trying to accomplish.

I'm not a formula expert by any stretch of the imagination, but two comments. Why use ISERROR when 2010 has the IFERROR function which would cut down on half the formula and maybe you shouldn't reference entire columns. Some formulas with ignore all 1,000,000+ rows and some formulas won't

This site here is a good read if you have the time about how to optimize spreadsheets http://www.decisionmodels.com/index.htm

And here you can even test your formulas for speed http://msdn.microsoft.com/en-us/library/aa730921.aspx

[vlady]

Hello everyone

just opened his file
http://www.excelforum.com/attachment...3&d=1326233069

after repairing for about less a minute it opened normally ..

Here's the catch ..

i turned of the autoupdate in formulas before opening the file..
coz i noticed it hanged on calculations.


there it goes but xml files are deleted on recovery

[Habanero Time]

My guess is when you start rebuilding, post a small sample workbook with this formula and somebody might be able to optimize the efficiency of what you are trying to accomplish.

I'm not a formula expert by any stretch of the imagination, but two comments. Why use ISERROR when 2010 has the IFERROR function which would cut down on half the formula and maybe you shouldn't reference entire columns. Some formulas with ignore all 1,000,000+ rows and some formulas won't

This site here is a good read if you have the time about how to optimize spreadsheets http://www.decisionmodels.com/index.htm

And here you can even test your formulas for speed http://msdn.microsoft.com/en-us/library/aa730921.aspx

This is what happens with multiple people working on a spreadsheet.
I am aware of and like SUMIFS, IFERROR and intended to go tighten that stuff up after I got past this initial hurdle.
This spreadsheet has all sorts of challenges. the piece that was subcontracted to me was this one that's taken 2 days.

[vlady]

@ Habanero
the IF(ISERROR(LOOKUP(...............),0,LOOKUP(2,1/((.......................)) hundreds of them i think,, is the culprit here..i just deleted all of this formulas and it is normal again..
jeffrey is right, a named ranged maybe!!????

[OnErrorGoto0]

See if this version improves the speed. I have added 3 named ranges, changed some formulas to use those ranges, removed the OFFSET functions and restricted the lookups to being used in one column only (new formulas in column A) and then the others use the row number returned in INDEX formulas. I also adjusted the summary table at the top to use SUMIF for the months.

[Habanero Time]

See if this version improves the speed. I have added 3 named ranges, changed some formulas to use those ranges, removed the OFFSET functions and restricted the lookups to being used in one column only (new formulas in column A) and then the others use the row number returned in INDEX formulas. I also adjusted the summary table at the top to use SUMIF for the months.

This is huge help (and a good lesson). Much appreciated. I've loaded the data back in and it's looking good, and I'm understanding what you did. I'm going to go over the math and see how it compares to the original one and its data.

Everyone, y'all have been great!

[tomanton]

Its an old tread, however it seems still attracts loads of people. Today I found in my s/s what caused such an issue.
The problem was with formulas that had links to external sheets residing on a C:\ disk.

To avoid the s/s going into calculation straight after opening it I opened any other s/s and then the problematic one, then changed manually the formulas with references to the s/s in C:\ disk as trying to update links automatically resulted in some sort of a problem.
And voila