A recent post at Stack Overflow reminded me that I have not posted about the pros and cons of Excel’s Workbook.ForceFullCalculation property.

## So what does Workbook.ForceFullCalculation do?

The simple answer is that it switches off Excel’s smart recalculation algorithm, so that every calculation recalculates every formula in all open workbooks.

## Why is ForceFullCalculation Bad?

**ForceFullCalculation slows down calculation** because every formula is treated as though it is volatile and gets calculated, rather than only calculating the formulas that depend either on something that has changed since the last calculation or on a volatile function.

## When is ForceFullCalculation Good?

When ForceFullCalculation is switched on Excel does not need to build and maintain the dependency trees. The dependency trees are created each time you open a workbook, and are maintained every time you make a change to a workbook.

So if

- Your workbook takes a long time to open
- Deleting or changing formulas takes a long time

**then switching on ForceFullCalculation could speed things up a lot.**

Also the dependency trees can take up a lot of memory so eliminating them can be good.

**So its a trade-off: faster workbook opening and editing, and less memory footprint versus slower calculation.**

(And you almost certainly need to be using Manual Calculation mode.)

## So how do you switch it on?

There are several ways of setting ForceFullCalculation:

- Click Alt-F11 to go to the Visual Basic Editor, select the workbook in the Project Explorer, Press F4 to see the workbook properties and change ForceFullCalculation to true.
- Add this VBA statement to a VBA sub and execute it.

Workbooks(“MyBook.xlsx”).ForceFullCalculation=True - If you have FastExcel V3 installed then you can set it from the FastExcel Workbook Calculation Options page.

## The Quirks of ForceFullCalculation

There are a few more things you should know about this property:

- Although it is a Workbook Property it works at Application level and so affects all open workbooks.
- It does get saved with the workbook and, if True, re-establishes itself when the workbook is reopened.
- It makes “Calculate” appear in the StatusBar, and you cannot make it go away with any of the F9 calculate commands.
- Once you have turned it on in an Excel session turning it off again does not restore Smart Recalculation: you have to close and restart Excel.

**Trying ForceFullCalculation out**

Here are the Jimwinz’s instructions from Stack Overflow for creating a demonstration of the problem:

- Create a new file (save and exit other Excel files!)
- To create the data to count, in A2 enter =RANDBETWEEN(0,10000) if you have a slow CPU or RANDBETWEEN(0,20000) if you have a faster CPU
- Copy A2 down to row A10k or A20k.
**Copy and paste values for column A**- Switch to Manual Calculation
- In B2, enter =COUNTIF($A$1:A2,A2), copy down and press F9 to calculate
- Once sheet is fully calculated, select all the CountIf cells in column B and use the Clear All function. This is the stage where Excel seems to freeze for a long time.
- Now click Undo to get the formulas back.
- Switch on ForceFullCalculation using one of the methods shown above. The statusbar should show Calculate and pressing F9 does not make it go away. Now Clear All is virtually instantaneous.

# So what is your experience with this nifty workbook property?

You should add Doug Jenkins’ Newton-Excel-Bach to your blogroll.

Regards

Alfred Vachris

Done

Using your tips on the high-performance performance counters, we also use this to SPEED UP calculation runs. What we determined, was that even with calculation mode set to manual, excel still tries to re-build the dependency tree when inputs change, and also tries to scan the tree to mark the appropriate cells as dirty.

We found that a 0.2sec “calculate” phase was being preceded by a 1.8sec dependency-tree update phase as inputs were toggled to the correct values for that permutation. By forcing the full calculation, we remove the dependency-tree update phase, and *only* double our calculation time , to 0.4secs.

Thanks Matt: that’s a great real-life example.

I discovered this by trial and error about 3 years ago, I was having a nightmare for months with some very large workbooks using Excel tables – even with 64 bit 16GB Ram – every time I made a change or even just entering values – Excel would freeze for a few seconds and sometimes for literally 10 minutes, was driving me nuts, when I found ForceFullCalculation switch in project explorer, I thought to myself, well it can’t make things any worse, so gave it a try, the difference was like night and day, transformed for the better.

At the time I had read hundreds of blog posts trying to find a solution – literally nobody mentioned this option to try! Maybe Microsoft should add a smart algorithm, to turn off the smart algorithm ! if it detects maintaining the dependency tree is taking so long.

Gary,

Thanks for your comment. I have had a small comment about ForceFullCalculation on my website for several years: but its hard to find: see

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

Pingback: Speeding Up Calculations in Excel - HummingbirdTraining.com.au