The options on the Formulas tab of the Excel 2016 Options dialog box (File→Options→Formulas or Alt+FTF) are divided into Calculation Options, Working with Formulas, Error Checking, and Error Checking Rules.
The Calculation options enable you to change when formulas in your workbook are recalculated and whether and how a formula that Excel cannot solve on the first try (such as one with a circular reference) is recalculated. Choose from the following items:
Automatic option button (the default) to have Excel recalculate all formulas immediately after you modify any of the values on which their calculation depends.
Automatic Except for Data Tables option button to have Excel automatically recalculate all formulas except for those entered into what-if data tables you create. To update these formulas, you must click the Calculate Now (F9) or the Calculate Sheet (Shift+F9) command button on the Formulas tab of the Ribbon.
Manual option button to switch to total manual recalculation, whereby formulas that need updating are recalculated only when you click the Calculate Now (F9) or the Calculate Sheet (Shift+F9) command button on the Formulas tab of the Ribbon.
Enable Iterative Calculation check box to enable or disable iterative calculations for formulas that Excel finds that it cannot solve on the first try.
Maximum Iterations text box to change the number of times (100 is the default) that Excel recalculates a seemingly insolvable formula when the Enable Iterative Calculation check box contains a check mark by entering a number between 1 and 32767 in the text box or by clicking the spinner buttons.
Maximum Change text box to change the amount by which Excel increments the guess value it applies each time the program recalculates the formula in an attempt to solve it by entering the new increment value in the text box.
The Working with Formulas section contains four check box options that determine a variety of formula-related options:
R1C1 Reference Style check box (unchecked by default) to enable or disable the R1C1 cell reference system whereby both columns and rows are numbered as in R45C2 for cell B45.
Formula AutoComplete check box (checked by default) to disable or re-enable the Formula AutoComplete feature whereby Excel attempts to complete the formula or function you’re manually building in the current cell.
Use Table Names in Formulas check box (checked by default) to disable and reenable the feature whereby Excel automatically applies all range names you’ve created in a table of data to all formulas that refer to their cells.
Use GetPivotData Functions for PivotTable References check box (checked by default) to disable and reenable the GetPivotTable function that Excel uses to extract data from various fields in a data source when placing them in various fields of a pivot table summary report you’re creating.
The remaining options on the Formulas tab of the Excel Options dialog box enable you to control error-checking for formulas. In the Error Checking section, the sole check box, Enable Background Error Checking, which enables error-checking in the background while you’re working in Excel, is checked. In the Error Checking Rules, all of the check boxes are checked, with the exception of the Formulas Referring to Empty Cells check box, which indicates a formula error when a formula refers to a blank cell.
To disable background error checking, click the Enable Background Error Checking check box in the Error Checking section to remove its check mark. To change the color used to indicate formula errors in cells of the worksheet (when background error checking is engaged), click the Indicate Errors Using This Color drop-down button and click a new color square on its drop-down color palette. To remove the color from all cells in the worksheet where formula errors are currently indicated, click the Reset Ignore Errors button. To disable other error-checking rules, click their check boxes to remove the check marks.