'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Permanently Enable Iterative Calculation in Excel
Question details
The user needs a method to permanently enable iterative calculation whenever Excel starts, preventing the setting from changing based on the workbooks opened.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using a third-party program or relying on a specific workflow that requires iterative calculations to be automatically enabled upon application launch.
- Observed behavior
- Excel calculation settings vary according to the application state and the first workbook opened, causing iterative calculation to turn off unexpectedly.
Ensure that your organization's IT policies permit the use of VBA macros, as permanently saving this configuration requires creating a startup macro.
Use a Startup Macro in PERSONAL.XLSB
Create a VBA macro in your Personal Macro Workbook that automatically enables iterative calculation every time Excel is launched.
Calculation options in Excel are application-level settings, meaning they can change depending on the first workbook you open during a session. By utilizing the PERSONAL.XLSB workbook, which opens silently in the background on startup, you can force the iterative calculation setting to remain active regardless of other files.
Launch Microsoft Excel and press 'ALT + F11' on your keyboard to open the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left, look for 'VBAProject (PERSONAL.XLSB)'. If it does not exist, you will need to record a dummy macro and choose to save it in the 'Personal Macro Workbook' to generate this file.
Double-click on 'ThisWorkbook' under the PERSONAL.XLSB project. In the code window that appears, paste the following code: Private Sub Workbook_Open() Application.Iteration = True End Sub
Save your changes in the VBA editor, close it, and restart Excel. Ensure that your Trust Center settings allow macros to run, so the script can execute properly on startup.
How to Enable Iterative Calculation in WPS Spreadsheet
WPS Spreadsheet natively supports iterative calculations for complex formulas and circular references. You can easily manage and enable this feature directly within the application settings.
- 1. Access the Options Menu: Open WPS Spreadsheet, click on the 'Menu' button in the top-left corner, and select 'Options' from the drop-down list.
- 2. Navigate to Calculation Settings: In the Options dialog box, click on the 'Calculation' tab located in the left-hand navigation pane.
- 3. Enable Iteration: Check the box next to 'Iterative calculation'. You can optionally adjust the 'Maximum iterations' and 'Maximum change' limits based on your calculation requirements.
- 4. Apply Changes: Click 'OK' to save your settings. Your spreadsheet will now correctly process formulas involving circular references.
Frequently Asked Questions
What is iterative calculation used for in Excel?
Iterative calculation is primarily used to allow circular references, where a formula refers back to its own cell either directly or indirectly. This is common in complex financial modeling, engineering calculations, and solving complex mathematical equations.
Why does my iterative calculation setting turn off automatically?
Excel calculation options are application-level settings inherited from the first workbook opened. If you open a workbook that was saved with iterative calculation disabled, Excel adopts that setting for the entire application session until it is manually changed.
Can I enable iterative calculations temporarily without using VBA?
Yes, you can enable it manually for your current session by going to File > Options > Formulas and checking the 'Enable iterative calculation' box. However, you will need to repeat this process if a newly opened workbook overrides the setting.
What happens if I don't enable iterative calculation for a circular reference?
If iterative calculation is disabled and you create a circular reference, Excel will display a 'Circular Reference Warning' prompt. It will not calculate the formula correctly and will typically return a zero or an error state.
