Calculate and recalculate data in Excel Services

Calculate and recalculate data in Excel Services

There are different ways to calculate formulas in a Microsoft Office Excel 2007 workbook. These calculation settings are defined when the workbook author creates and updates an Excel workbook. When you publish a workbook to a document library, the current Excel calculation setting is supported and retained in the workbook by Excel Services.

What do you want to do?


Learn more about calculation settings

The following table lists the different calculation settings and describes how they work in Excel and Excel Services.

Calculation Setting Microsoft Office Excel 2007 Excel Services
Manual Calculation    

Worksheet Calculation    
A user must calculate the entire workbook or worksheet by pressing a key on the keyboard or by clicking a button or command. A user must calculate the entire workbook by clicking a command.
Recalculate Before Save     The workbook is automatically recalculated before it is saved. The workbook is automatically recalculated before it is saved.
Automatic Calculation     The workbook is automatically calculated every time a formula or cells that a formula references changes. The workbook is automatically calculated every time a formula or cells that a formula references changes.
Automatic except Tables     The workbook is automatically calculated every time a formula or the cells that a formula references changes, except for any formulas and referenced cells that have been created by an Excel Data Table (part of the What-if analysis suite of commands). The workbook is automatically calculated every time a formula or cells that a formula references changes, except for any formulas and referenced cells that have been created by an Excel Data Table (part of the What-if analysis suite of commands).

For more information on calculation and recalculation, see Excel Help.

 Note    Excel Services supports loading a workbook that contains circular references, and detects circular references when it loads or recalculates a workbook. If Excel Services cannot resolve a circular reference, it does not display a warning message that there is a circular reference. The values that are calculated are the same as the values that you would get if you cancel the operation on the Excel client. In effect, Excel Services automatically cancels the circular reference to prevent the calculation from degrading the server performance.

Top of Page Top of Page

Calculate or recalculate a workbook

  1. If necessary, display the Web Part Page that contains the Excel Web Access Web Part, or display the workbook in Web Browser view.
  2. On the Microsoft Office Excel Web Access toolbar, click the Update menu, and then click Calculate Workbook.

ShowIssue: The Update menu does not appear on the toolbar.

An administrator can customize Microsoft Office Excel Web Access so that the Update menu is hidden on the Excel Web Access toolbar: the Type of Toolbar property might be set to None or Navigation Only, or the Calculate Workbook property is cleared. For more information, see Excel Web Access Web Part custom properties.

 Tip   If you are the workbook author, consider dedicating a cell in a workbook that has a formula with the current date and time so that you can quickly see in the workbook when it was last calculated on the server. For example:

="Last calculated at: "& TEXT(NOW(),"m/d/yyyy h:mm")

Top of Page Top of Page

 
 
Applies to:
SharePoint Server 2007