# Formulas

### Calculation options

**Automatic** - (Application Setting) Calculates all dependent formulas every time you make a change to a value, formula or name. Any workbook that contains formulas will be automatically calculated when it is opened. This is the default calculation setting. __more__**Automatic except for data tables** - Calculates all dependent formulas except those in data tables. Data tables will only be recalculated when one of its values are changed. You can calculate the formulas in data tables by pressing the "Calc Now" button.**Manual** - Calculates only when requested by the user. __more__**Recalculate workbook before saving** - Calculates all dependent formulas in the worksheet before it is saved.**Enable iteration calculation** - This can be used to restrict the iteration when using circular references. __more__**Maximum iterations** - This is the maximum number of iterations used before iteration will stop. The default is 100.**Maximum change** - The largest number that the change in value, must be less than, before iteration will stop. The smaller the number the more accurate the result. The default is 0.001.

### 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. The default is not ticked.**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. The default is ticked.**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. The default is not ticked.**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 __GETPIVOTDATA__ function cell reference, such as GETPIVOTDATA("Sales",$A$4). Clear the check box to create an A1 reference style cell reference, such as A4. The default is ticked.**Suggest formula variations that are supported by older versions of Excel** - (Added in 365). The default is ticked. __more__

### 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. The default is ticked.**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** - You can use the __Error Checking__ dialog box or the Error Checking Smart Tag to ignore individual errors that might have been flagged. This button allows you to resets ALL the errors in ALL the worksheets in the active workbook. If the ignored errors are reset, you will be able to see ALL the formula errors.

### Error 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. The default is ticked. __more__**Inconsistent calculated column formula in tables** - This option refers to __Calculated Columns__. 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. The default is ticked. __more__**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. The default is ticked. __more__**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. The default is ticked. __more__**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. The default is ticked. __more__**Cells containing data types that couldn't refresh** - (Added in 365). Display error indicators for cells with data types that could not refresh data from external sources. The default is ticked.**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. The default is ticked. __more__**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. The default is ticked. __more__**Formulas referring to empty cells** - Select to have Excel treat formulas that refer to empty cells as an error and to display a warning. The default is not ticked. __more__**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. The default is ticked. __more__**Misleading number formats** - (Added in 365). Select to have Excel display an error when you link to a cell that has a different number format to the one applied to the destination cell. The default is ticked. __more__

© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext