User FAQs

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

2) 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

3) 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.

4) When should you change your calculation option to Manual ?
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.

5) 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.

6) 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.

7) 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.

8) 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 

9) 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.

10) 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 unsorted
*) 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

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


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


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


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


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


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


14) 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

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


16) 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.

17) 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.

18) 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"

19) 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.

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

21) 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.

22) 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.

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

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

24) 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.

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext