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

setting over and over again in most of the workbooks you create. In such a situation, it’s high time to get into the Excel Options dialog box and modify that setting so that you won’t waste any more time tinkering with the same setting in future workbooks.

Changing some of the more universal settings on the General tab

The General tab (shown in Figure 2-2) is the first tab in the Excel Options dialog box. This tab is automatically selected whenever you first open this dialog box by choosing File ⇒ Options or by pressing Alt+FT.

       Figure 2-2: The General tab’s options enable you to change many universal Excel settings.

      The options on the General tab are arranged into four groups: User Interface Options, When Creating New Workbooks, Personalize Your Copy of Microsoft Office, and Start Up Options.

      The User Interface Options group contains the following check boxes and buttons:

      ✔ Show Mini Toolbar on Selection: Disables or reenables the display of the mini-toolbar, which contains essential formatting buttons from the Home tab, above a cell selection or other object’s shortcut menu when you right-click it.

      ✔ Show Quick Analysis Options on Selection: Disables or reenables the appearance of the new Quick Access toolbar in the lower-right corner of a cell selection. The Quick Analysis toolbar contains options for applying formatting to the selection as well as creating new charts and pivot tables using its data.

      ✔ Enable Live Preview: Disables or reenables the Live Preview feature whereby Excel previews the data in the current cell selection using the font or style you highlight in a drop-down list or gallery before you actually apply the formatting.

      ✔ ScreenTip Style: Changes the way ScreenTips (that display information about the command buttons you highlight with the mouse) are displayed onscreen. Select Don’t Show Feature Descriptions in ScreenTips from the ScreenTip Style drop-down list to display a minimum amount of description in the ScreenTip and eliminate all links to online help, or select Don’t Show ScreenTips to completely remove the display of ScreenTips from the screen (potentially confusing if you add macros to the toolbar that all use the same icon).

      The options in the When Creating New Workbooks section of the Popular tab of the Excel Options dialog box include only these four combo and text boxes:

      ✔ Use This as the Default Font: Select a new default font to use in all cells of new worksheets by entering the font name in the combo box or selecting its name by clicking it in the drop-down list (Body Font, which is actually Microsoft’s Calibri font).

      ✔ Font Size: Select a new default size to use in all cells of new worksheets (11 points is the default size) by entering the value in the box, or select this new point value by clicking it in the drop-down list.

      ✔ Default View for New Sheets: Select either Page Break Preview (displaying page breaks that you can adjust) or Page Layout (displaying page breaks, rulers, and margins) as the default view (rather than Normal) for all new worksheets.

      ✔ Include This Many Sheets: Increase or decrease the default number of worksheets in each new workbook (1 being the default) by entering a number between 2 and 225, or select this new number by clicking the spinner buttons.

      The Personalize Your Copy of Microsoft Office section contains the following three options:

      ✔ User Name: This text box enables you to change the user name that’s used as the default author for new workbooks created with Excel 2016.

      ✔ Office Background: This drop-down list enables you to change the faint, background pattern that appears on the right side of the Excel title bar above the Ribbon. By default, the Clouds pattern appears as the background. To change this background pattern, select its name from the Office Background button’s drop-down list. To display no pattern in this area of the title bar, you select the None option from this drop-down list.

      ✔ Office Theme: This drop-down list enables you to select between three different tint options – Colorful, Light Gray, and White – that are applied to the borders of the Excel screen, creating a kind of background color for the Ribbon tabs, column letter and row number indicators on the worksheet frame, and the status bar.

       Remember that when the Always Use These Values Regardless of Sign In to Office check box in the Personalize Your Copy of Microsoft Office section is selected (as it is, by default), the user name, background pattern, and theme selected, respectively, for the User Name, Office Background, and Office Theme options are applied to all the Office 2016 application programs that you use, such as Word 2016, PowerPoint 2016, and so on.

      The final section, Start Up Options, contains the following three options:

      ✔ Choose the Extensions You Want Excel to Open by Default: The Default Programs button, when clicked, opens a Set Associations for Program dialog box that enables you to select all the types of application files that you want associated with Excel 2016. Once associated with Excel, double-clicking any file carrying its extension automatically launches Excel 2016 for viewing and editing.

      ✔ Tell Me if Microsoft Excel Isn’t the Default Program for Viewing and Editing Spreadsheets: This check box determines whether or not you’re informed should another Spreadsheet program or viewer on your computer other than Excel 2016 be associated with opening Excel workbook files.

      ✔ Show the Start Screen When This Application Starts: This check box determines whether or not the Start screen (described in detail in Book I, Chapter 1) appears when you launch Excel 2016.

       If you deselect the Show the Start Screen When This Application Starts check box, whenever you launch Excel 2016, the program immediately opens a new, blank workbook file in the worksheet view, skipping entirely the Excel Backstage view. Excel 2016 then works just like Excel 2010 and 2007 on startup.

Changing common calculation options on the Formulas tab

The options on the Formulas tab (see Figure 2-3) of the Excel Options dialog box (File ⇒ Options ⇒ Formulas or Alt+FTF) are divided into Calculation Options, Working with Formulas, Error Checking, and Error Checking Rules.

       Figure 2-3: The Formulas tab’s options enable you to change how formulas in the spreadsheet are recalculated.

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