Excel formulas

Display formula text

Switch between formula text mode and formula result mode

ctrl+` (gravis) switches between displaying formula-text and formula-result.

If in show formula text mode, the resulting value of a specific formula can be shown by pressing F2 on the cell with the formula.

See also the DisplayFormulas property of the WorksheetView object.

Worksheet function formulaText

The worksheet function formulaText(ref) returns the formula in the cell ref. If there is no formula, it evaluates to #NA.

Evaluation modes

Excel evaluates formulas in one of two modes:

Implicit Intersection Evaluation (IIE), or
Array Evaluation (AE)

These modes differed how a function that expects a single value was passed a multi celled range (for example C42:C90).

With IIE, the function chose the value of the cell on the same row or column as the formula (referred to as implicit intersection)
With AE, the function is called with each cell's corresponding value and an array (not a single value) is returend. (This behaviour is also referred to as lifting)

Before Excel intruduced dynamic arrays (DA),

IIE was the default mode for cell arrays
AE was used everywhere else (such as conditional formatting, data validation, CSE array formulas etc.)

With the introduction of dynamic arrays, AE has become the default evaluation mode.

In Excel's object model, an AE formula is read/written with range.formula2. For backward compatiblity, a formula can be forced to behave in IIE mode (implicit intersection, no spilling) with range.formula2.

Operators

Range operator

The range operator is the colon. It produces a range with the cells between the addresses on its left and right side.

TODO: application.range seems to correspond to the colon operator.

Intersect operator

The intersect operator is a space. It evaluates to the range that is common to both ranges on its left and right side.

Thus, It is possible to write formulas such as the following which calculates the sum of the number in F10:F12

=sum(F9:F12 F10:F15)

An intersection that returns an empty set is shown with the #NULL! cell error.

See also the set operators in the application object.

Union operator

The union operator is the comma. It evaluates to the range that is present on any of the ranges on its left or right side.

Recalculation

Excel recalculates formulas either automatically, manually or automatically except for data tables.

This mode of recalculation can be changed in File -> Options -> Formulas -> Calculation options.

All currently open workbooks and worksheets share the same recalculation mode.

If the recalculation mode was not manually changed, then it determined by the recalculation mode of the first opened Worksheet.

Triggering recalculation

Formulas can be forced to be recalculated with Keyboard shortcuts involving F9:

Shortcut	Description	VBA equivalent
`F9`	calculates all worksheets in all open workbooks
`shift+F9`	calculates the active worksheet	`activeSheet.calculate` (?)
`ctrl+alt+F9`	forces the recalculation in all worksheets of open workbooks, even if Excel thinks they have not changed	`application.calculateFullRebuild` (?)
`ctr+alt+shift+F9`	calculates dependent cells

VBA functions

Additional VBA functions (that might be related to these keyboard shortcuts) are:

activeWorkbook.calculate
application.calculateFull
application.volatile
range.calculate
range.dirty marks the given range to be recalculated

The corresponding VBA method is calculate (on a Worksheet object).

See also the calculation property of the application object.

Worksheet functions

The worksheet function info("recalc") evaluates to the current mode of recalculation.