Recalculation of formulas

Product: MyOffice Professional, MyOffice Education

Application: Spreadsheet Desktop version

Calculation is the process of calculating formulas and then displaying the values of the results in cells. Formulas are usually recalculated automatically when the values in the cells on which they depend change. This is a standard procedure when opening/closing a file and editing it.

If there are a large number of formulas in a document, their automatic recalculation can make opening and working with the document difficult. In order to speed up work with such documents, MyOffice Spreadsheet provides a mode in which formula recalculation is done upon user request.

Automatic recalculation mode	Manual recalculation mode
•When a user opens and saves a document, only the outdated formula values are recalculated. •When you work with a document, formulas are recalculated if the associated cells have been changed.	•When you open a document and change the associated cells, no formulas are automatically recalculated. •When you work with a document, formulas are recalculated only when you carry out manual operations on the cells that contain them. •Manual recalculation of outdated formula values is available in the entire document, on the sheet, and in selected cells. •When saving a document, recalculation of outdated formula values is performed only if the Before saving checkbox is selected.

Automatic recalculation mode

Manual recalculation mode

•When a user opens and saves a document, only the outdated formula values are recalculated.

•When you work with a document, formulas are recalculated if the associated cells have been changed.

•When you open a document and change the associated cells, no formulas are automatically recalculated.

•When you work with a document, formulas are recalculated only when you carry out manual operations on the cells that contain them.

•Manual recalculation of outdated formula values is available in the entire document, on the sheet, and in selected cells.

•When saving a document, recalculation of outdated formula values is performed only if the Before saving checkbox is selected.

Outdated values in formulas are irrelevant data that were calculated in the past but are no longer relevant. They may occur as a result of data entry errors, errors in formulas and functions, data type mismatches, changes in table structure, errors in macros, or synchronization problems. MyOffice Spreadsheet stores information about the presence of such cells within the file itself.

When you work in the cloud, only automatic formula recalculation mode is available.

When formulas are recalculated, references to data from external documents are not updated. To update them, follow the steps described in the Update data section.

Select formula recalculation mode

By default, the automatic mode of formula recalculation is selected in the document.

To switch the mode, do one of the following:

1.Open the mode selection sub-menu in one of the following ways:

•In the Data tab, click Calculation mode with the image of the current calculation mode. For example, t_data_calculation_mode Calculation mode.

•When working in macOS, select Data > Calculation mode command from the command menu.

2.In the sub-menu that opens, select the desired formula recalculation mode: Calculate automatically / Automatically or Calculate manually / Manually.

The icon of the selected mode is displayed in the Status bar.

Recalculation of formulas in automatic mode

In automatic mode, only outdated formula values are recalculated when opening a document. If you want to force calculate all formulas in the document, press Ctrl+Shift+F9 (Windows, Linux) or shift_grey Shift+ command_grey Cmd+F9 (macOS).

Recalculate formulas manually

You can manually recalculate outdated formula values in the entire document, on an open sheet, or in selected cells.

To recalculate outdated formula values in the entire document, do one of the following:

•On the Data tab, click the arrow to the right of the t_data_calculate_whole_document Calculate data button and select Calculate whole document from the drop-down menu.

•When working in macOS, select Data > Calculation mode > Calculate > Whole document command from the command menu.

•Press F9.

To recalculate outdated formula values on a sheet currently open, do one of the following:

•On the Data tab, click the arrow to the right of the t_data_calculate_whole_document Calculate data button and select t_data_calculate_sheet Calculate sheet from the drop-down menu.

•When working in macOS, select Data > Calculation mode > Calculate > Sheet command from the command menu.

•Right-click the sheet tab and run the Calculate sheet command from the context menu.

•Press Shift+F9.

To recalculate outdated formula values in one or more selected cells, do one of the following:

•On the Data tab, click the arrow to the right of the t_data_calculate_whole_document Calculate data button and select t_data_calculate_selected_cells Calculate selected cells from the drop-down menu.

•When working in macOS, select Data > Calculation mode > Calculate > Selected cells command from the command menu.

•Right-click the selected cells and choose Calculate selected cells command from the context menu.

Recalculate formulas when saving a document

When the user saves the document, recalculation of outdated formula values is done if the Calculate before saving checkbox is selected in the drop-down menu of the t_data_calculate_whole_document Calculate data button.

In automatic recalculation mode, formulas are always recalculated before saving, therefore the t_data_calculate_whole_document Calculate data button is disabled.

In manual recalculation mode, the Calculate before saving checkbox is selected by default. If you want to clear the checkbox, do one of the following:

•On the Data tab, click the arrow to the right of the t_data_calculate_whole_document Calculate data button and clear the Calculate before saving checkbox in the drop-down menu.

•When working in macOS, select Data > Calculation mode > Calculate and clear the Before saving checkbox.

Was this helpful?

Yes