Excel using 100% CPU

GulfIT
GulfIT used Ask the Experts™
on
Hi,

I have a File Server which has excel speadsheets which our Accounts department user on a daily basis. The Speadsheets are also linked to other files and some have around 8000 rows.

The users are complaining that Excel users 100% CPU when opening such files and filtering the speadsheet.

Kindly help out how to sort this out so that CPU usage is at normal.

Thanks,
Asif
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Does the 100% usage start as soon as the Excel spreadsheet is opened? If not, when does it start?
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

have a look at volatile formulas, formulas that use whole columns instead of designated ranges, and other optimising techniques as described in detail in Charles Williams's article here:
http://msdn.microsoft.com/en-us/library/aa730921(v=office.12).aspx

You may have some work on your hands.

Efficient Excel performance requires planning if the solution needs to perform in a scaled scenario.

cheers, teylyn

Author

Commented:
Sometimes when opening (for Linked Files) and after opening for the files(While Filtering data).
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Without code to look at it's difficult to suggest solutions but your CPU usage sounds like it's being caused by file I/O. What do you do exactly when you open a file?

Commented:
The cells in a spreadsheet are not always stored the way that makes large spreadsheets easy to manipulate.

If cells across one row, traversing many columns, are stored at relatively adjacent addresses, they can be read and written much faster than if cells stored this way are read by traversing down a column, taking a cell from successive rows in the same column. If the cells are store "row major" and the access is "column major" there may be excessive disk swapping during manipulation of the data.

This used to be a serious problem on old mainframe computers doing statistical analysis of data stored in matrix format. A simple loop in Fortran (yes, it's old) like this

   For Row = 1 to Nr
     For Column = 1 to Nc
          do something with the cell at (Row, Column)
     Next Column
  Next Row

would take a lot of time but by interchanging the inner and outer loop variables, the access time was lowered substantially.

I agree with MartinLiss on the File I/O issue, or the use of virtual memory (hard disk space) when the program needs to grab data from the spread sheet, and is running low on RAM, it swaps out pieces of the array, another time killer.

Large arrays and how they are stored has little or no impact when the values can be accessed in RAM (the key in RANDOM access memory) but can be a killer on serially accessed memory, like hard disks.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'd guess however that anything you do with an array will be faster than file I/O, so depending on what is being done with the files, reading them into an array may save time.

Author

Commented:
How do i change to an array ? and i have monitored the memory utilization, its low.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please describe (with some detail) and/or show the code for what you do with the files.
I'm not sure if this will solve your problem, but I'm almost 100% sure it's all those links that are causing you problems.  

If you would open up all the sheets it references before opening up the one that pegs you at 100% then I bet you'll find that it runs w/o pegging Excel.

-SA
Commented:
Quick Answer: Turn Off Auto-Recalc (see below) then look into using Access for these large tables (spreadsheets).

A "ROW" in excel is a "Record" in a database. You may have a spreadsheet that has grown too large to be a Spreadsheet and belongs in a database where each "ROW" is a record, and each column is a FIELD. You can import an XLS sheet into Access for example, answer a few questions, and turn the XLS rows into records. But read on for an experiment first.

My comments about the paging/swapping during calculations was to point out that the way the spreadsheet "recalculates" (which it does almost all the time when an entry is made) may be obviated by the database approach, and only when the dataset is updated would it be recalculated.

But you can stop Excel from doing this auto-recalc all the time. Here's a suggestion

Try turning off automatic calculation to see if this helps:

Open the Tools menu and click Options.
In the Options dialog box, click the Calculations tab.
Then, click on the radio button labeled Manual, and click OK to close the dialog box.

To manually recalculate, push F9

When you finish a large worksheet, don’t forget to turn automatic calculation back on or notify users that F9 has to be pushed to update the spreadsheet when entries are finished.

See if that stops the 100% usage, or lowers it.

I just checked Open Office spreadsheets. It also defaults to "Auto Calculate" under Tools, Cell Contents, Autocalculate (it's checked) for anyone with very large spreadsheets in the Open Office suite.

Spreadsheets are great for small projects, but when you get into large datasets, you'll no doubt end up turning to a database solution, importing the work  you've done so far, but building a database solution. Small projects don't always scale up very well in excel.

Jeff

PS: When you finish up with large Excel sheets (Tables) take a look at putting these into a real database system.

Here's a good start:
http://office.microsoft.com/en-us/access-help/import-export-and-link-data-between-access-and-excel-HP001095095.aspx?CTT=1

Commented:
GulfIT -- Did you find any of the suggestions useful? No response so far, but hope you tried the auto-recalculation suggestion, or are looking at a database solution that imports the spreadsheet.

Thanks for some response.

Author

Commented:
The manual or F9 method is not a option i guess i will have to look at a database based solution.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please describe (with some detail) and/or show the code for what you do with the files.
You still have never done that and maybe if you did we could help.

Commented:
GulfID - so you cannot alter the recalculation from auto to manual. If that can't be changed, then the bigger your spreadsheet becomes, the more recalculation time will be involved, and it could end up taking minutes between changes.

No spreadsheet that has 8000 rows needs to be "auto calculated" on every change. Especially if it is also linked to other spreadsheets that also recalculate on every change, which is the default.

Opening a sheet as a table in a database may be the best solution to get around this unnecessary and continual recalculation.

There may be an alternative "recalc" to using F9

Jeff

Author

Commented:
Good Advice

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial