Excel 2016 All-in-One For Dummies. Harvey Greg

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. (See Book III, Chapter 1.)

      ✔ 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. (See Book VII, Chapter 2 for details.)

      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.

Changing correction options on the Proofing tab

The options on the Proofing tab (see Figure 2-4) of the Excel Options dialog box (File ⇒ Options ⇒ Proofing or Alt+FTP) are divided into two sections: AutoCorrect Options and When Correcting Spelling in Microsoft Office Programs.

       Figure 2-4: The Proofing tab’s options enable you to change AutoCorrect and spell-checking options.

      Click the AutoCorrect Options button to open the AutoCorrect dialog box for the primary language used in Microsoft Office 2016. This dialog box contains the following four tabs:

      ✔ AutoCorrect with check box options that control what corrections Excel automatically makes, an Exceptions button that enables you to indicate what words or abbreviations are not to be capitalized in the AutoCorrect Exceptions dialog box, and text boxes where you can define custom replacements that Excel makes as you type.

      ✔ AutoFormat As You Type with check box options that control whether to replace Internet addresses and network paths with hyperlinks, and to automatically insert new rows and columns to cell ranges defined as tables and copy formulas in calculated fields to new rows of a data list.

      ✔ Actions with an Enable Additional Actions in the Right-Click Menu check box and Available Actions list box that let you activate a date or financial symbol context menu that appears when you enter certain date and financial text in cells.

      ✔ Math AutoCorrect with Replace and With text boxes that enable you to replace certain text with math symbols that are needed in your worksheets.

      The options in the When Correcting Spelling in Microsoft Office Programs section of the Proofing tab control what types of errors Excel flags as possible misspellings when you use the Spell Check feature. (See Book II, Chapter 3.) It also contains the following drop buttons:

      ✔ Custom Dictionaries, which opens the Custom Dictionaries dialog box, where you can specify a new custom dictionary to use in spell checking the worksheet, define a new dictionary, and edit its word list.

      ✔ French Modes or Spanish Modes, which specify which forms of the respective language to use in proofing spreadsheet text.

      ✔ Dictionary Language, which specifies by language and country which dictionary to use in proofing spreadsheet text.

Changing various save options on the Save tab

The options on the Save tab (see Figure 2-5) of the Excel Options dialog box (File ⇒ Options ⇒ Save or Alt+FTS) are divided into four sections: Save Workbooks, AutoRecover Exceptions for the current workbook (such as Book1), Offline Editing Options for Document Management Server Files, and Preserve Visual Appearance of the Workbook.

       Figure 2-5: The Save tab’s options enable you to change the automatic backup and recover options.

      The default setting for the Saves Files in this Format drop-down list box at the top of the Saves Workbooks section is Excel Workbook (*.xlsx), the XML-based file format first introduced in Excel 2007. If you want Excel 2016 to automatically save your new workbook files in another file format (such as the old Excel 97–2003 Workbook (*.xls) file format, select the file format in this initial drop-down list box.

      The settings in the Save Workbooks section on this tab also include the program’s AutoRecover settings. The AutoRecover feature enables Excel to save copies of your entire Excel workbook at the interval displayed in the Minutes text box (10 by default). You tell Excel where to save these copies in the AutoRecover File Location text box by specifying a drive, a folder, and maybe even a subfolder.

      If your computer should crash or you suddenly lose power, the next time you start Excel the program automatically displays an AutoRecover pane. From this pane, you can open a copy of the workbook file that you were working on when this crash or power loss occurred. If this recovered workbook (saved at the time of the last AutoRecover) contains information that isn’t saved in the original copy (the copy you saved the last time you used the Save command before the crash or power loss), you can then use the recovered copy rather than manually reconstructing and reentering the otherwise lost information.

      You may also use the recovered copy of a workbook should the original copy of the workbook file become corrupted in such a way that Excel can no longer open it. (This happens very rarely, but it does happen.)

       Don’t disable the AutoRecover feature by selecting the Disable AutoRecover for This Workbook Only check box on the Save tab even if you have a battery backup system for your computer that gives you plenty of time to manually save your Excel workbook during any power outage. Disabling AutoRecover in no way protects you from data loss if your workbook file becomes corrupted or you hit the