Calculating and recalculating formulas in browser-based workbooks (2024)

SharePoint Server 2010 More...Less

You can use formulas in a workbook in the browser exactly as you can use them in Excel. Just as in Excel, the author of the formula determines the way formulas are calculated or recalculated. This article describes different options for calculation that a workbook author can select..

Typically, a workbook recalculates formulas automatically when a value in a cell changes that the formula depends on. For example, suppose that you use a formula to add all the individual values in a column. If you insert rows in the workbook, and therefore, add cells to the range of cells in the column, the workbook adjusts the formula to include the new cells automatically, and recalculates the total.

However, a workbook author can use a different option so that the workbook does not calculate formulas automatically. When an author creates a formula in Excel, the author can select different calculation options. For example, the author might require that all formulas have to be re-calculated manually when a user clicks a command. After the author selects a calculation option and saves the workbook on a site, the published workbook keeps the calculation option that the author specified in the Excel workbook.

The following table provides a brief description of the different calculation options.

Calculation option	Description
Automatic Calculation	Formulas are recalculated automatically every time that a formula changes, and every time that a cell changes that a formula references. This is the default option.
Manual Calculation	Formulas are never re-calculated automatically. A user must select the Calculate Workbook option to recalculate any formulas.
Automatic except Tables	Formulas and referenced cells that are contained in an Excel Data Table as part of What-If analysis are not recalculated. Other formulas are recalculated automatically every time that a formula changes, and every time that a cell changes that a formula references.
Recalculate Before Save	Formulas are recalculated automatically every time that the workbook is saved. Important: Excel Services does not support this option because you cannot save a workbook.

If the workbook author configured a workbook for manual calculation, you can follow these steps to calculate the formulas in a workbook in the browser.

Open the workbook.
On the workbook toolbar, click the Data menu, and then click Calculate Workbook.

or, you can use the keyboard shortcut F9 to calculate the formulas in a workbook.

Notes:If a workbook in the browser contains a formula with a circular reference that cannot be resolved, it does not display a warning message about the circular reference. Instead, it calculates the values that you would get if you cancel the operation on the Excel client. In effect, the workbook automatically cancels the circular reference. This prevents decrease in performance that otherwise might result from trying to calculate a circular reference.