Questions about calculation delays occurring in Excel

Posted on 2011-10-23
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

    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 38

    Expert Comment

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

    Author Comment

    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
    LVL 17

    Accepted Solution

    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

    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


    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

    LVL 26

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now