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

Скачать книгу

Pressing Enter, Move Selection check box option is checked. If you want Excel to automatically advance the cell cursor in another direction (Right, Up, or Left), select the direction from its drop-down list. If you don’t want Excel to move the cell cursor outside of the active cell upon completion of the entry (the same as clicking the Enter button on the Formula bar), click the After Pressing Enter, Move Selection check box to remove its check mark.

       Playing around with the display options

The display options in the middle of the Advanced tab of the Excel Options dialog box (see Figure 2-7) fall into three categories: general Display options that affect the Excel program; Display Options for This Workbook that affect the current workbook; and Display Options for This Worksheet that affect the active sheet in the workbook.

       Figure 2-7: The various display options in the center of the Advanced tab control what’s shown on the screen.

      Most of the options in these three categories are self-explanatory as they either turn off or on the display of particular screen elements such as the Formula bar, ScreenTips, scroll bars, sheet tabs, column and row headers, page breaks, (cell) gridlines, and the like.

      remember When using these display options to control the display of various Excel screen elements, keep the following things in mind:

      ✔ The Ruler Units drop-down list box automatically uses the Default Units for your version of Microsoft Office (Inches in the U.S. and Centimeters in Europe). These default units (or those you specifically select from the drop-down list: Inches, Centimeters, or Millimeters) are then displayed on both the horizontal and vertical rulers that appear above and to the left of the column and row headings only when you put the Worksheet area display into Page Layout view (Alt+WP).

      ✔ Click the Comments and Indicators option button under the For Cells with Comments, Show heading when you want Excel to display the text boxes with the comments you add to cells at all times in the worksheet. (See Book IV, Chapter 3.)

      ✔ Click the Nothing (Hide Objects) option button under the For Objects, Show heading when you want Excel to hide the display of all graphic objects in the worksheet, including embedded charts, clip art, imported pictures, and all graphics that you generate in the worksheet. (See Book V, Chapters 1 and 2 for details.)

      ✔ Click the Show Page Breaks check box to remove its check mark whenever you need to remove the dotted lines indicating page breaks in Normal (Alt+WN) view after viewing the Worksheet area in either Page Break Preview (Alt+WI) or Page Layout view (Alt+WP).

      ✔ Instead of going to the trouble of clicking the Show Formulas in Cells Instead of Their Calculated Results check box to display formulas in the cells of the worksheet, simply press Ctrl+’ (apostrophe) or click the Show Formulas button on the Formulas tab of the Ribbon. Both the keystroke shortcut and the button are toggles so that you can return the Worksheet area to its normal display showing the calculated results rather than the formulas by pressing the Ctrl+’ shortcut keys again or clicking the Show Formulas button.

      ✔ Instead of going to the trouble of removing the check mark from the Show Gridlines check box whenever you want to remove the column and row lines that define the cells in the Worksheet area, click the Gridlines check box in the Show/Hide group on the View tab or the View check box in the Gridlines column of the Sheet Options group on the Page Layout tab to remove their check marks.

      tip Use the Gridline Color drop-down list button immediately below the Show Gridlines check box to change the color of the Worksheet gridlines (when they’re displayed, of course) by clicking a new color on the color palette that appears when you click its drop-down list button. (I find that navy blue makes the cell boundaries stand out particularly well and gives the screen a hint of the old paper green-sheet look.)

       Caring about the Formulas, Calculating, and General options

At the bottom of the Advanced tab of the Excel Options dialog box (see Figure 2-8), you find a regular mix of options in five sections. The first three sections, Formulas, When Calculating This Workbook, and General, contain a veritable potpourri of options.

image

       Figure 2-8: The options at the bottom of the Advanced tab control various calculation, general, data, and 1-2-3 compatibility settings.

      The settings of most of the options in these three sections won’t need changing. In rare cases, you may find that you have to activate the following options or make modifications to some of their settings:

      ✔ Set Precision as Displayed: Select this check box only when you want to permanently change the calculated values in the worksheet to the number of places currently shown in their cells as the result of the number format applied to them.

      ✔ Use 1904 Date System: Select this check box when you’re dealing with a worksheet created with an earlier Macintosh version of Excel that used 1904 rather than 1900 as date serial number 1.

      ✔ Web Options: Click this command button to display the Web Options dialog box, where you can modify the options that control how your Excel data appears when viewed with a web browser, such as Internet Explorer.

      ✔ Edit Custom Lists: Click this command button to create or edit custom lists with the Fill handle. (See Book II, Chapter 1.)

       Digging the Data options

      The Data section of the Advanced tab of the Excel Options dialog box contains four check box options. These options control the way that Excel 2016 handles huge amounts of data that you can access in Excel through external data queries discussed in Book VI, Chapter 2 or through Excel’s pivot table feature (especially when using the Power Pivot add-in) discussed Book VII, Chapter 2. By default, Excel 2016 disables the undo feature when refreshing data in a pivot table created from external data that has more than 300,000 source rows (also called records) to significantly reduce the data refresh time. To modify the minimum number of source rows at which the undo refresh feature is disabled, enter a new number (representing thousands of records) in the text box containing the default value of 300 under the Disable Undo for Large PivotTable Refresh Operations check box or select the new value with the spinner buttons. To enable the undo feature for all refresh operations in your large pivot tables (regardless of how long the refresh operation takes), simply deselect the Disable Undo for Large PivotTable Refresh Operations check box.

      Excel 2016 also automatically disables the undo feature for Excel data lists that are created from related external database tables (referred to in Excel as a data model) that exceed 64MB in size. To change the minimum size at which the undo feature is disabled, enter a new number (representing megabytes) in the text box containing the default value of 64 under the Disable Undo for Large Data Model Operations check box or select this new value with the spinner buttons. To enable the undo feature for all operations involving data lists created from an external data model (regardless of how long the undo operation takes), simply deselect the Disable Undo for Large Data Model Operations check box.

      tip If you want Excel to automatically assume that any external data used in creating new pivot tables or imported into data lists from external data queries involve a data model so that Excel automatically looks for the fields that are related in the various files you designate, select the Prefer the Excel Data Model

Скачать книгу