Calc Sheet vs Calc Now (F9)

[jaw323]

I have tried looking through MS help and MS help forums and could not find and explaination of the difference between these two functions. If you go to tools --> options -->calculation in excel 2000 or 2003. you have these options to calculate a workbook. I would like to know the difference between them.

The purpose of this is that I am doing QA on a large spreadsheet program (49Megs) built in excel 2000 that will need to be used in excel 2003 or XP or whatever this newer version is called. in the old version we used Calc Now(F9) to calc the entire workbook. When I tried the same thing in 2003, it crashes Excel or freezes it for long periods of time. It only takes 30 seconds or a little more in 2000.

Any help is appriciated.

 

[KenWright]

F9 will recalc all 'New / Changed / Volatile formulas' and their dependent cells.

Calc Sheet is as follows, with a snippet from Charles Williams' excellent site that details all you could want and more on this:-

Recalculate Selected Worksheet(s) (Shift-F9)
Calculate Worksheet usually flags the Workbook as uncalculated in Manual mode.

In Manual Mode:
Recalculates only the uncalculated and volatile cells on the selected worksheet in dependency chain sequence, or if multiple worksheets are selected they are all calculated.
Note that this only gives you "correct" results if all precedent inter-sheet and inter-workbook dependencies have already been fully calculated, and that cells on other worksheets that are dependent on the sheet(s) are not recalculated.
After a sheet calculate the formulae in the sheet the sheet and the workbook are flagged as uncalculated (CALCULATE shows in the statusbar), unless there were no uncalculated or volatile cells.

In Automatic Mode:
Recalculates all uncalculated and volatile cells on the selected worksheet, or if multiple worksheets are selected they are all calculated. If any cells were recalculated then flags the workbook as uncalculated, which triggers an automatic recalculation to recalculate any dependents on other sheets and all volatile cells and their dependents on ALL worksheets, not just the selected sheet(s). If there are no uncalculated dependents on other sheets or volatile cells then nothing happens in the triggered automatic calculation because the workbook was already calculated.

http://www.decisionmodels.com/calcsecretsg.htm

When you open the workbook in XL2003 it will force a full recalc as the workbook was created in an earlier version of Excel. Try hitting CTRL+ALT+F9 in 2000 and you will force a full calc, and this should be representative of what Excel is trying to do the first time you open the book in 2003.

Either way, his site is an invaluable resource for any calculation related questions

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

Google for [COLOR=blue white]"calc now" "calc sheet"[/color]

The first page returned:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214395

From that page:

Recalculate the Active Sheet
To recalculate only the active sheet, do either of the following: • Press SHIFT+F9.
-or-
• Click Options on the Tools menu, and then click the Calculation tab. Click the Calc Sheet button to calculate only the active sheet.

Recalculate All Open Documents
To recalculate all open documents, do either of the following: • Press F9.
-or-
• Click Options on the Tools menu, click the Calculation tab, and then click the Calc Now button.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[BlueHorizon]

Hi,
As I understand it, Calc Now calculates all open worksheets, including data tables and charts. Calc Sheet calculates only the active worksheet and any charts and sheets linked to the worksheet. I found this out by using the question mark in the upper right corner of the dialog box. Click it once. Click the command and a popup box appears with a brief explanation. That question mark tool is available in most dialog boxes for just that purpose.
HTH,

Best,
Blue Horizon

http://www.bluehorizontraining.com