Feature Summary
The Calculation Options feature is used to control the calculation behavior of formulas in collaborative sheets, balancing calculation performance and data accuracy when processing large amounts of formula data.
Feature Description
| Feature Dimension | Description |
|---|---|
| Membership Restriction | No membership restriction |
| Version Restriction | No version restriction |
Steps
Find the Calculation Options Feature Entry
Feature Entry
「Formulas」 tab → 「Calculation Options」 button
- Click the top 「Formulas」 tab
- Find the 「Calculation Options」 button in the toolbar
- Click to open the 「Calculation Options」 dialog, which contains two main setting areas: 「Formula Calculation」 and 「Recalculation」
Formula Calculation Settings
Feature Entry
Calculation Options dialog → 「Formula Calculation」 area
Iterative Calculation Parameters:
| Setting Item | Description | Default Value |
|---|---|---|
| Iterative Calculation | Check to enable iterative calculation, allowing circular references in formulas and approximating results through multiple iterations | Unchecked by default |
| Maximum Iterations | Set the maximum number of cycles for iterative calculation | 100 (range 1~32767) |
| Maximum Error | Set the convergence precision for iterative calculation; stops when the difference between two iteration results is less than this value | 0.001 (≥0) |
Precision Control:
| Setting Item | Description |
|---|---|
| Precision as Displayed | When checked, formula calculation results will be truncated according to the cell's display precision before participating in subsequent calculations. For example, if a cell displays 0.1 (actual value 0.1234), 0.1 will be used in calculations |
Steps:
- If you need to enable iterative calculation, check the 「Iterative Calculation」 checkbox
- Adjust the 「Maximum Iterations」 value (using the up/down arrows on the right or by direct input)
- Adjust the 「Maximum Error」 value
- If you want to calculate based on display precision, check 「Precision as Displayed」
- After completing the settings, they need to be used in conjunction with the settings in the 「Recalculation」 area to take effect
Recalculation Mode Switching
Feature Entry
Calculation Options dialog → 「Recalculation」 area
| Setting Item | Description |
|---|---|
| Automatic Recalculation | Selected by default. When the data in a referenced cell changes, all dependent formulas are automatically recalculated |
| Manual Recalculation | When switched, modifying data will not automatically trigger formula updates. Formulas are only recalculated after pressing F9 or clicking the 「Recalculate」 button |
Steps:
- In the 「Recalculation」 area, select the 「Automatic Recalculation」 or 「Manual Recalculation」 radio button
- Click 「OK」 in the lower right corner to save all settings
- After switching to manual mode, a prompt message will appear in the status bar
- In manual mode, click the 「Formulas」 → 「Recalculate」 button, or press F9 to immediately recalculate formulas in all sheets
- Press Shift+F9 to recalculate only the formulas in the currently active sheet
FAQ
Q1: Will switching the calculation mode affect other collaborators?
- Solution: Yes, the calculation option is a document-level setting, and switching it will affect all collaborators. In manual mode, formulas will not update automatically after others modify data until someone presses F9. It is recommended to communicate with the team and confirm before switching.
Question 2: What should I do if the circular reference does not converge after enabling iterative calculation?
- Solution: Increase the "Maximum number of iterations" or raise the "Maximum error" value to accelerate convergence. If the formula itself has a logical error that prevents convergence, check the formula logic. For scenarios that do not require iterative calculation, uncheck "Iterative calculation" to restore the normal circular reference warning prompt.
Question 3: What should I do if automatic recalculation takes too long?
- Solution: Check the amount of data involved in calculations within the Sheets, or perform a calculation diagnosis. You can also split the Sheets to reduce the calculation load before performing automatic recalculation.
Question 4: When should I switch to manual calculation?
- Solution: When the data volume is large and formulas are complex, manual calculation can prevent lag caused by triggering a full recalculation every time a modification is made.
Question 5: Will a new formula be calculated in manual calculation mode?
- Solution: A newly created formula will still calculate a result once, but subsequent data modifications will not trigger automatic updates.
Question 6: What happens when switching back to automatic calculation?
- Solution: All formulas will immediately recalculate once to ensure the data is up to date.
Notes
- The default mode is automatic calculation
- In manual calculation mode, remember to press F9 to ensure data accuracy