Formula calculation, performance, and error handling options

Calculation options

Workbook Calculation     Specifies how you want Microsoft Office Excel to calculate workbooks.

• Automatic     Calculates all dependent formulas every time that you make a change to a value, formula, or name. This is the default calculation setting.

• Automatic except for data tables     Calculates all dependent formulas except data tables.

• Manual     Calculates open workbooks only when you click Calc Now on the Calculation tab. When you click Manually, Excel automatically selects the Always calculate before saving workbook check box. If saving a workbook takes a long time, clearing Always calculate before saving workbook may improve the save time.

  • Recalculate workbook before saving    Recalculates data before the workbook is saved.

Enable iterative calculation     When selected, this option allows iterative formulas (also known as circular references) to be calculated. Unless you specify otherwise, Excel stops after 100 iterations or when all values change by less than 0.001. You can change this iteration limit by typing the number that you want in the Maximum Iterations box, the Maximum Change box, or both boxes. When the Enable iterative calculation check box is cleared, circular references cannot be calculated, and an error message is displayed.

• Maximum Iterations     Type the numbers in the Maximum iterations and Maximum change boxes to change the maximum number of iterations that are used and the maximum amount of change below which iteration stops.

• Maximum Change     Type the numbers in the Maximum Iterations and Maximum Change boxes to change the maximum number of iterations that are used and the maximum amount of change below which iteration stops.

Working with formulas

R1C1 reference style     Changes the reference style of row and column headings and cell references from A1 style to R1C1 style. In A1 style, columns are labeled alphabetically, and rows are labeled numerically. In R1C1 reference style, both columns and rows are labeled numerically.

Formula AutoComplete     Selected by default, this option lets you use Formula AutoComplete to easily create and edit formulas and to minimize typing and syntax errors. When you type an = (equal sign) to start a formula, followed by the beginning letters or a display trigger, Formula AutoComplete provides a dynamic drop-down list of valid functions, names, and text strings that match the letters or trigger so that you can simply insert an item in the drop-down list into the formula. Clear the Formula AutoComplete check box if you don't want to use Formula AutoComplete.

Use table names in formulas     Selected by default, this option makes it easier and more intuitive to work with table data when you use formulas that reference a table, either portions of a table or the entire table. Clear the Use table names in formulas check box if you don't want to use table names in formulas.

Use GetPivotTable functions for PivotTable references     Determines the type of cell reference that is created for a PivotTable cell when you use semi-selection in a formula outside of the PivotTable. Select the check box to create a GETPIVOTTABLE function cell reference, such as GETPIVOTDATA("Sales",$A$4). Clear the check box to create an A1 reference style cell reference, such as A4.

Error Checking

Enable background error checking     Select to have Excel check cells to for errors at idle. If a cell is found to have an error, the cell is flagged with an indicator in the upper left corner of the cell.

Indicate errors using this color     Sets the color that Excel uses to indicate errors. If you click Automatic, the color is set to the default color of green.

Reset Ignored Errors     Click to flag errors in the spreadsheet and to find them when checking for errors even though they have already been found and ignored.

Excel checking rules

Cells containing formulas that result in an error     Select to have Excel treat cells that contain formulas that result in an error as an error and to display a warning.

Inconsistent calculated column formula in tables     Select to have Excel treat cells that contain formulas or values that are inconsistent with the column formula or tables as an error and to display a warning.

Cells containing years represented as 2 digits     Select to have Excel treat formulas that contain text-formatted cells with years represented as 2 digits as an error and to display a warning when checking for errors.

Numbers formatted as text or preceded by an apostrophe     Select to have Excel treat numbers formatted as text or preceded by an apostrophe as an error and to display a warning.

Formulas inconsistent with other formulas in the region     Select to have Excel treat a formula in a region of your worksheet that differs from the other formulas in the same region as an error and to display a warning.

Formulas which omit cells in a region     Select to have Excel treat formulas that omit certain cells in a region as an error and to display a warning.

Unlocked cells containing formulas      Select to have Excel treat an unlocked cell that contains a formula as an error and to display a warning when checking for errors.

Formulas referring to empty cells     Select to have Excel treat formulas that refer to empty cells as an error and to display a warning.

Data entered in a table is invalid     Select to have Excel treat cells that contain values that are inconsistent with the column data type for tables that are connected to data in a SharePoint list as an error and to display a warning.