Concept: Automatic and Manual Calculation in Workbook Formulas

It's useful to understand the actions that cause Worksheets to calculate or recalculate the formulas in your workbook.

Calculations depend on the formula type and the action you're doing. Recalculation time includes the time spent running reports, executing formulas, and applying conditional formatting.

This table summarizes whether Worksheets recalculates workbook data or not:

Recalculation Setting	Formula/Workbook Type	Event/Action	Recalculate Formula?
Automatic	Volatile INDIRECT NOW NOWTZ OFFSET RAND RANDBETWEEN TODAY	Change any cell in workbook.	Yes.
Automatic	Non-Volatile	Change any cell that this cell depends on.	Yes.
Automatic	ARRAYAREA	A formula uses ARRAYAREA and the array referenced by ARRAYAREA changes. The root cell (top left cell) of the array referenced by ARRAYAREA changes.	Yes.
Automatic	RANDCONST		No. This non-volatile function is recalculated only when: A scheduled live data refresh runs. You select Data > Recalculate . You select Data > Recalculate All .
Automatic	SELECT (with live data)	Manually refresh live data range. Live data refresh schedule runs.	Yes.
Automatic	SELECT as a generic formula	Change any cell that the formula references.	It depends. If you use the parameter section to reference data, the formula runs when the data changes. If you change data in the SELECT statement portion, the formula doesn't run again.
Automatic	ONCE	Surround any formula with the ONCE function.	No.
Automatic	Copied workbook	First time you open copied workbook.	No. Select Data Recalculate All to update the workbook.
Automatic	Converted Microsoft Excel file	First time you open the uploaded/converted workbook.	No. Select Data Recalculate All to update the workbook.
Manual	Any	Any.	No. Worksheets never recalculates formulas unless you manually initiate a recalculation. This is applicable for all formulas, including volatile formulas. (New formulas are calculated when you first submit them but they're not recalculated.)

The default recalculation setting in workbooks is

Automatic

You might want to prevent automatic recalculation in some situations. Example: If you're making many changes to a workbook with complex formulas, you might want to wait until you finish all your changes before recalculating the formulas.

To specify a calculation option, select

File

Settings

To manually recalculate formulas, use one of these actions:

Data

Recalculate

calculates any formulas in the workbook where the data has changed.

Data

Recalculate All

calculates all formulas in the workbook, whether or not the data has changed since the last calculation.

Recalculate All

also updates external references in consumer workbooks

Notes
:

When you do an action that doesn't cause a recalculation, such as uploading a Microsoft Excel file to Worksheets and opening the converted workbook the first time, select

Data

Recalculate All

to update the workbook.

Live data in a workbook isn't affected when you use the recalculation settings or actions. You can refresh live data manually by selecting

Data

Refresh All Live Data

, by selecting

Refresh Now

in the Live Data Details panel, or by setting up a scheduled refresh using

Data

Schedule Live Data Refresh