High CPU Usage in MS Excel

Posted on 2007-10-01
Last Modified: 2013-12-10
I have having problems with a couple of Excel 2003 spreadsheets and the calculation features.  I am using a Dell Latitude 1 GHz laptop.  I am experiencing high CPU usages in Excel when I try to use the auto calculation feature in Excel.  The computer hangs for about 10 minutes or so.  Is there any way that I can prevent this from happening.
Question by:JAOsmond
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    You may simply have a lot of calculations. Keep in mind that conditional formatting and VBA code tied to worksheet change events can also consume cycles. How big is the workbook? How many formulas are there? Any VBA code? User defined functions? Do you have other workbooks open at the same time that may have a lot of formulas?


    Author Comment

    The workbook is about 32MB with no VBA code.  There are a lot of formulas in the workbook, so it normally takes a minute or two to calculate, but for some reason today the workbook over doubled in size and would take almost all available CPU cycles when it would try and calculate.  We let it run several times for about 5 minutes and then killed the process.  Once we set the formulas to manually update, the workbook appeared to go back to its original size of 32MB.  We are not sure what caused the change today.
    LVL 81

    Accepted Solution

    Very likely you hit the dependency limit. In order to make calculations more efficient Excel keeps track of formula dependencies. These dependencies allow Excel to know which formulas are impacted and therefore need to be recalculated when any cell value is changed. By not calculating formulas that are not impacted by a change Excel can perform faster calculations.

    In Excel versions prior to 2007 there is a limit of 65,536 dependencies allowed before Excel no longer tracks dependencies and starts calculating all formulas whenever any cell is changed. The exact number of dependencies in any workbook is difficult to determine but it can be estimated by counting the number of formulas and estimating the average number of unique cell references there are in each formula.

    Below is a macro to get the count of functions in the active workbook.

    [Begin Code Segment]

    Public Sub CountFunctionsInWorkbook()

       Dim Worksheet As Worksheet
       Dim Count As Long
       For Each Worksheet In Worksheets
          On Error Resume Next
          Count = Count + Worksheet.Cells.SpecialCells(xlCellTypeFormulas).Count
          On Error GoTo 0
       Next Worksheet
       MsgBox "There are " & Count & " formulas in the active workbook."

    End Sub

    [End Code Segment]

    When it is suspected that the dependency limit has been exceeded there are a few things that can be done. The easiest is to turn off automatic calculations and calculate on demand by pressing F9 or by using a macro. Another solution is to redesign the workbook formulas to try and eliminate as many unnecessary formulas as possible.

    Why the workbook has a dramatic change in size just with a change in calculation modes remains a mystery to me.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Great sound, comfort and fit, excellent build quality, versatility, compatibility. These are just some of the many reasons for choosing a headset from Sennheiser.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now