Questions about calculation delays occurring in Excel

Posted on 2011-10-23
Medium Priority
Last Modified: 2012-05-12

This is a follow-up to a previous thread on a similar topic located here:

          Which factors result in the most "sluggishness" in Excel?

1) After selecting Manual under Calculation Options in the Formulas tab (in Excel), why would any type of delay* still remain?  Aside from calculations, what other processes exist that could slow things down?

*Delay refers to a delay relative to a "control" spreadsheet which contains the same data placed there by Copy/PasteValue.

2) Something I have looked at in the past and never understood is the amount of the CPU capacity being devoted to a process occurring in Excel or some other application.  In the task manager, under the Performance tab is a chart depicting CPU Usage (and another chart showing Physical Memory Usage).  The CPU usage is given as a percentage and even during periods when a spreadsheet is completely unavailable due to some type of calculation process, etc., the CPU usage percent seems to always be <20%.  Why doesn't it devote everything it's got to the task at hand -- knowing of course that it can always be diverted elsewhere if needed -- rather than holding >80% back?  What is that >80% doing?

3) Does the number and size of other tabs in the same workbook have any effect on the time it takes to complete various processes in a spreadsheet?

Question by:Steve_Brady
LVL 26

Assisted Solution

redmondb earned 400 total points
ID: 37014985
Hi, Steve_Brady.

How many processors do you have? Couple of things...
(1) One of the Advanced Options can limit the number of processors that Excel will use. (Definitely in 2010 and I think also in 2007.)
(2) Some parts of Excel are limited to a single processor (e.g. macros).

LVL 39

Expert Comment

by:Aaron Tomosky
ID: 37015091
Along the same lines: use the per CPU usage chart. Probably one core is pegged at 100%

Author Comment

ID: 37015114
Thanks for the responses.  Here's my specs:

        Processor:  AMD Phenom(tm) II X6 1090T Processor  3.20 GHz
        Memory:  16.0 GB
        Operating System:  Windows 7 Ultimate (64-bit)
        Drive C:  Crucial RealSSD C300 CTFDDAC128MAG-1G1 2.5-inch SATA Solid State Drive
        Drive D:  HL-DT-ST BD-RE WH12LS30 ATA Device
        Drive E:  WD 500GB SATA/600 7200RPM 16MB Hard Drive
        Drive F:  Hitachi Deskstar 7K1000.C HDS721010CLA332 - hard drive - 1 TB - SATA-300
        Video:  ATI Radeon HD 5700 Series
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 17

Accepted Solution

andrewssd3 earned 1600 total points
ID: 37015118
One factor which does not appear to be affected by manual calculation being set on is conditional formatting - it is effectively volatile, and will cause a lag if applied to a large range.
LVL 26

Expert Comment

ID: 37015142
Thanks, Steve. Does the "X6" mean six processors? If so, that would make sense of your "<20%" - approx. 16% for excel  plus a bit over for everything else. (BTW, when I said "processors" I probably meant "CPU's". Oops.]

Please see attached and let us know what your "Formulas" settings are.
Excel Multi Thread SettingsThanks,

Author Comment

ID: 37127812

Thanks for the responses.

One factor which does not appear to be affected by manual calculation being set on is conditional formatting - it is effectively volatile, and will cause a lag if applied to a large range.

In continuing to check different processes just through trial and error and based on Andrew's comment, I discovered that by far and away the process causing the biggest slowdown was conditional formatting which I did indeed have "applied to a large range."  Once that was removed, with everything else being the same, the processing speed across the board in that spreadsheet returned to normal -- even with calculation set to automatic.

Does the "X6" mean six processors?

Yes, and my Formulas settings are the same as yours except there is a 6 in place of the 4 and a 6 in the Manual box.

Thanks for the FYI's in your first comment Brian.

Author Closing Comment

ID: 37127815
LVL 26

Expert Comment

ID: 37128214
Glad you're sorted Steve, and thanks for giving us your findings.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question