[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4921
  • Last Modified:

High CPU Usage in MS Excel

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.
0
JAOsmond
Asked:
JAOsmond
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
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?

Kevin
0
 
JAOsmondAuthor Commented:
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.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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.

Kevin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now