User FAQs

If you have a question, please send it to us.

1) What is Automatic Calculation ?
Excel will perform smart recalculation on all open workbooks whenever anything changes.
When you open a workbook if anything has changed then a smart recalculation is performed.
A full calculation is always performed when you open a workbook last saved in a earlier version of Excel.

2) When should you change the option to Manual Calculation ?
You should switch to Manual calculation when you want to be able to make changes immediately without waiting.
For workbooks that take more than a couple of seconds to recalculate.
If not then there will be a delay whenever anything changes.

3) If the word Calculate appears in the status bar, what does this mean ?
*) Your calculation is set to Manual and a formula in the active workbook needs recalculating.
*) The workbook contains circular references and the iteration option is switched on.
*) You have set the Workbook.ForceFullCalculation property to True.
*) The dependency tree has reached its limit.

4) The second time you calculate a workbook it can be significantly faster than the first time, why is that ?
Excel usually only recalculates cells and dependencies that have changed.
Excel stores and reuses the most recent calculation chain which means it doesn't have to build it again.
Excel caches recently used data.

5) Is there a way to interrupt the recalculation if it is taking too long ?
Yes. Pressing the ESC key should (in most cases) interrupt the calculation.

6) Is the Calculation Setting saved at the workbook level or the Excel session level ?
All these settings are saved at the workbook level but are applied at an application/session level.
The calculation settings are taken from the first workbook you open (in that session) and then ignored in subsequent workbooks.

7) Is there a quick way to update all the formulas on the active worksheet ?
Yes. Pressing (Shift + F9) will perform a Smart Calculation of the active worksheet.
A smart calculation will only update the cells containing formulas that have changed since the last calculation.
There is also a button for this on the Formulas tab, Calculation group.
There is no quick way to perform a Full Calculation of the active worksheet.

8) Is there a quick way to update all the formulas in the selected cells ?
Yes. You can replace all the equal signs in these cells to update the formulas.
Press (Ctrl + H, =, =, Replace All)
This will perform a Full Calculation of all the selected cells.

9) Is there a quick way to see the formulas rather than the values ?
(Ctrl + ') - This is a single left quote, not an apostrophe.

10) Can you describe the Evaluate Formula dialog box ?
This lets you see different parts of a nested formula evaluated in the order the formula is calculated.

11) Is there a quick way to find all the errors on my worksheet ?
Home Tab, Find & Select > Go To Special > Formulas > Errors

12) Where is the command located for displaying the Edit Links dialog box ?
Data Tab, Queries & Connections group, Edit Links
File Tab, Info Panel, Edit Links to Files

13) What changes could you make to speed up workbook recalculation ?
The speed taken to update depends on the number of cell references, the calculation operations and the efficiency of the 'formulas and functions' being used.
Having more rows with fewer calculations will update faster than fewer rows with more complex calculations.
*) Remove any duplicated calculations
*) Remove any array formulas or replace them with user defined functions
*) Change the lookups to be performed on sorted data rather than not sorted
*) Use Excel or VBA built-in functions where possible
*) Reduce the worksheet usedrange
*) Avoid using any volatile functions
*) Replace dynamic named ranges with structured referencing
*) Design formulas to be forward referencing so they do not refer to any cells to the right or below
*) Avoid inter-workbook links
*) Consolidate data to fewer worksheets
*) Move all lookups and reference data to a single worksheet
*) Take out any references or expressions from your array formulas
*) Initiate the calculation from within VBA
*) Run 'time checks' on different worksheets to identify bottlenecks

14) When updating formulas what do the following shortcut keys do ?
(F9) - Smart Recalculation - recalculates all cells in all "open" workbooks.

Application.Calculate 

(Shift + F9) - Smart Recalculation - recalculates all cells in all the "selected" worksheets.

ActiveSheet.Calculate 
Worksheets().Calculate 

(Ctrl + Alt + F9) - Full Calculation - calculates all cells in all open workbooks.

Application.CalculateFull 

(Shift + Ctrl + Alt + F9) - Full Calculation (dependency tree and calculation chain) - calculates all cells in all open workbooks.

Application.ForceFullCalculation 

15) What is a Circular Reference ?
A circular reference will occur when a formula directly or indirectly refers to its own cell.
Excel calculates circular references sheet by sheet without considering dependencies.
If your circular references span multiple worksheets you can get very slow calculation.

16) Can you think of any scenarios when a circular reference is required ?

17) Can you explain what Iterative Calculation means ?
Also known as repetitive calculation.
One of the most common ways of accommodating circular references is to increase the number of iterations.

18) How would you check if a workbook contains any Circular References ?
Switch off the (Excel Options, Formulas)("Enable Iterative Calculation") option.
Excel will provide a warning message if it encounters any circular references.

19) Does Excel have any Multi-Threading capabilities ?
Excel 2007 introduced the concept of multi-threaded calculation (and recalculation) of workbooks
Excel tries to identify parts of the calculation chain that can be recalculated concurrently on different threads.

VBA Questions

20) Is it possible to prevent the calculation from being interrupted ?
Yes. There are two ways this can be achieved:
Using the CalculationInterrupting property.

Application.CalculationInterrupting = xlCalculationInterruptKey.xlNoKey. 

If you are running from VBA using the Application OnTime.

Application.OnTime MacroName 

21) What does the Range.Calculate method do ?
Smart Recalculation of the specified range.

Range("A1:D20").Calculate 

22) What does the Range.CalculateRowMajorOrder method do ?
Smart Recalculation of the specified range ignoring any forward references or within range dependencies.

Range("A1:D20").CalculateRowMajorOrder 

23) Is there a way in code to trigger a recalculation of the active sheet only ?
Togglying the EnableCalculation property and then recalculating.

ActiveSheet.EnableCalculation = False 
ActiveSheet.EnableCalculation = True 
Activesheet.Calculate 

24) Is there a way to check the calculation state ?
Yes. You can use the CalculateState property.

Application.CalculationState 

25) What does the Workbook.ForceFullCalculation property do ?

Workbook.ForceFullCalculation = True 

Returns or sets the workbook to forced calculation mode.
This property will be reset when Excel is restarted or the workbook is closed.
When you want every calculation of the workbook to be a full calculation.
When this property is set to True the calculation time for data tables will increase significantly.

26) When would you use the Workbook.ForceFullCalculation property ?
If a workbook has a large number of complex dependencies that takes ages to load or when a recalculation takes longer than a full calculation.

27) Write code that will trap the F9 events and redirect it to a subroutine.

Application.OnKey "{F9}", "HandleF9" 
Application.OnKey "+{F9}", "HandleShiftF9" 
Application.OnKey "^+{F9}", "HandleCtrlShiftF9" 
Application.OnKey "%^+{F9}", "HandleAltCtrlShiftF9" 

28) What is the Range.Dirty method ?
Designates a range to be recalculated the next time a recalculation is performed.

Range("A1:B10").Dirty = True